html validation google-sheets google-apps-script

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 properly. Just as a visual, here is a screenshot of a sample sheet with the sidebar active:

As you can see, the dropdown is successfully created from 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:

TypeError: Cannot read properties of null (reading 'Current_Job') at updateJobName(Rename Job:27:21)

Solution

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

References:

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