Html Question posted in
Some basic documentation can be found here.

Html – How to pass values from input in form in Google Sheets sidebar to Google Sheets cell using TextFinder()

I’ve been puzzling over this problem for hours now.

Here is what I am trying to achieve. The end goal is to be able to modify the name of a job using a sidebar in Google Sheets. The sidebar can be opened with the custom menu. Within the sidebar I have a dropdown field which pulls all the job names from a specific range. In the field below, the user can type the updated name.

Then, using textFinder() function, I was hoping to find the original name as selected in the dropdown field and replace it with the new name.

Here is my code:

code.gs

function onOpen() {
  var ui = SpreadsheetApp.getUi();
    ui.createMenu('Manager Menu')
      .addItem('Rename Job', 'showNewJobSidebar')
      .addToUi();
  SpreadsheetApp.getUi();
}

function showNewJobSidebar() {
  
   var html = HtmlService
      .createTemplateFromFile('sidebar');

  // Add the dropdown lists to the template

   html.dropdown = SpreadsheetApp.getActiveSheet().getRange("C6:C").getValues().filter(String);

   html = html.evaluate()
      .setTitle('Change Job Position Name')

  SpreadsheetApp.getUi().showSidebar(html);
}

function updateJobName(form) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let value1 = form.Current_Job;
  let value2 = form.New_Job;
  sheet.getRange("C6:C").createTextFinder(value1).replaceAllWith(value2);
}

html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link
    rel="stylesheet"
    href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"
    />
    <style>
      .container {
          margin: 5px 5px 5px 5px;
      }

      .input-Dropdown {

        width: 50%; 
        text-align: left; 
        float: left;
        padding: 5px 5px 5px 5px;
      }

      .input-Text {

        width: 50%; 
        text-align: left; 
        float: left;
        padding: 5px 5px 5px 5px;
      }

      .custom-heading {
        font-weight: bold;
      }

    </style>
    <script>
     function submitForm() {
       google.script.run.updateJobName(document.getElementById("jobForm"));
     }
   </script>  
  </head>
  <body>
    <div class="container">
      
    <h1 class="custom-heading"><center>Rename Job</center></h1><br><br>
    
    <form id ="jobForm " onkeydown="return event.key != 'Enter'">

    <!-- Create input fields to accept values from the user -->
    Current Job Name:<br><br>
    <select class="input-Dropdown" id="Current_Job"/>
    <? for (let i in dropdown) { ?>
    <option value="<?=dropdown[i]?>"><?=dropdown[i]?></option>
    <? } ?>
    </select>
    <br>
    <br>
    <br>

    New Job Name:<br><br>
    <input class="input-Text" type="text" id="New_Job"/>
    <br>
    <br>
    <br>

    <button class="action" onclick="submitForm();">Save</button>
    <button onclick="google.script.host.close();">Close</button>
    </form>
    </div> 
  </body> 
</html>

The menu and the sidebar function. Here is a screenshot of the sheet with the sidebar:

Screenshot

The dropdown is successfully created from the C6:C in the active sheet. When I click "Save", however, although the button changes to indicate that the button has been pressed, the spreadsheet does not update changing the original job name to the updated job name. Can someone point out what I am doing wrong with this?

Here is the error I am getting:

Screenshot2

Tags:

2

Answers


  1. 0

    You don’t need to search for text. You just need the row number associated to the text in column one.

    gs:

    function getCol1() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1).getValues().map((r, i) => [r[0], i + 2]);
  return vs;
}
 
function changeMyData1(obj) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  sh.getRange(obj.row,obj.col).setValue((obj.val))
}
function launchsidebar() {
  let ui = SpreadsheetApp.getUi();
  ui.showSidebar(HtmlService.createHtmlOutputFromFile('ah4'));
}

    html:

    <!doctype html>
<html lang="en">

<head>
  <title>Title</title>
</head>

<body>
  <select id="sel1"></select>
  <input type="text" id="txt1" > Change to:<br>
  <input type="button" value="Edit" onClick="changeData()">
 <script>
  window.onload = () => {
    google.script.run
    .withSuccessHandler( (vs) => {
      updateSelect(vs);
    })
    .getCol1();
  }

  function updateSelect(vA){
    var id='sel1';
    vA.unshift(["Select",""])
    var select = document.getElementById(id);
    select.options.length = 0;
    for(var i=0;i<vA.length;i++) { 
      select.options[i]=new Option(vA[i][0],vA[i][1]);//second value is the row number and it gets assigned to the option value 
      } 
    } 
    
    function changeData() { 
      let r = document.getElementById("sel1").value; 
      google.script.run.changeMyData1({row:r,col:1,val:document.getElementById("txt1").value}); 
    } 
    
    </script>
</body>

</html>
    function changeMyData1(obj) {
      const ss = SpreadsheetApp.getActive();
      const sh = ss.getSheetByName("Sheet0");
      sh.getRange(obj.row,obj.col).setValue((obj.val))
    }
    function launchsidebar() {
      let ui = SpreadsheetApp.getUi();
      ui.showSidebar(HtmlService.createHtmlOutputFromFile('ah4'));
    }

    Sidebar:

    enter image description here

    Login or Signup to reply.
  2. 0

    Suggestion:

    I’ve made some modifications from your existing code. Since the error that you’re getting is about the Current_job returning null, I added a few lines of code in your submitForm() function to get the value input from your form:

       var current_job = document.getElementById("Current_Job").value;
   var new_job = document.getElementById("New_Job").value;

    and then I passed current_job and new_job as parameters in your updateJobName function:

    function updateJobName(current_job, new_job) {
  let value1 = current_job;
  let value2 = new_job;
sheet.getRange("C6:C").createTextFinder(value1).replaceAllWith(value2);
}

    You can give this a try:

    code.gs

    // Global variables
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

// onOpen "Manager Menu" menu
function onOpen() {
  var ui = SpreadsheetApp.getUi()
    .createMenu('Manager Menu')
    .addItem('Rename Job', 'showNewJobSidebar')
    .addToUi();
}

// used to serve the HTML on the sidebar
function showNewJobSidebar() {
  var html = HtmlService.createTemplateFromFile('sidebar');

  // Add the dropdown lists to the template
  html.dropdown = SpreadsheetApp.getActiveSheet().getRange("C6:C").getValues().filter(String);
  html = html.evaluate().setTitle('Change Job Position Name')
  SpreadsheetApp.getUi().showSidebar(html);
}

function updateJobName(current_job, new_job) {
  let value1 = current_job;
  let value2 = new_job;

  sheet.getRange("C6:C").createTextFinder(value1).replaceAllWith(value2);
}

    html

    <!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css" />
  <style>
    .container {
      margin: 5px 5px 5px 5px;
    }

    .input-Dropdown {

      width: 50%;
      text-align: left;
      float: left;
      padding: 5px 5px 5px 5px;
    }

    .input-Text {

      width: 50%;
      text-align: left;
      float: left;
      padding: 5px 5px 5px 5px;
    }

    .custom-heading {
      font-weight: bold;
    }
  </style>
</head>

<body>
  <div class="container">

    <h1 class="custom-heading">
      <center>Rename Job</center>
    </h1><br><br>

    <form id="jobForm " onkeydown="return event.key != 'Enter'">

      <!-- Create input fields to accept values from the user -->
      Current Job Name:<br><br>
      <select class="input-Dropdown" id="Current_Job"/>
      <? for (let i in dropdown) { ?>
      <option value="<?=dropdown[i]?>">
        <?=dropdown[i]?>
      </option>
      <? } ?>
      </select>
      <br>
      <br>
      <br>

    New Job Name:<br><br>
      <input class="input-Text" type="text" id="New_Job" value="" />
      <br>
      <br>
      <br>

      <button class="action" onclick="submitForm();">Save</button>
      <button onclick="google.script.host.close();">Close</button>
    </form>
  </div>

  <script>
    function submitForm() {
       var current_job = document.getElementById("Current_Job").value;
       var new_job = document.getElementById("New_Job").value;

       //Asynchronous 
        google.script.run.withSuccessHandler(data => {
          google.script.host.close();
        }).withFailureHandler(er => {
          alert(er);
        }).updateJobName(current_job, new_job); // <-- updateJobName function exist on Code.gs, parameters are passed to that function
    }
  </script>

</body>

</html>

    Here’s the output: I changed the Writing to Texting

    enter image description here

    References:

    Feel free to let me know if this works for you!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search