Apps Script: Edit active row values with user input from html-form/google sidebar

Question

The script below gets exactly the values I am interested in from the active row of my Google Sheet. These values that I am interested in are:

firstName
LastName
emailAddress
schoolName
nativeLanguage

I collect these values to display them in an html-form with a client-side javascript. And so far, there is no problem at all to do these 2 things (so, collect values of the active row and display them in the html-form to the user).

Where I am stuck and needs help with is this:

I want the user to be able to change/replace these values in the html-form, where he sees them. And as soon as the user is done making these changes in the html-form and click on the SEND-button at the bottom of the html-form, I want these new data/input from the user to replace the initial values of the active row in my Google sheet.

Any help or hint would be great, as this other post here basically deals with the first part of my issue that´s already been solved (display cell values in sidebar).

Here is the server-side script collecting the values of the active row, and it´s been working just fine. All I would like to do now is build something on top of this, so that incoming input from the html-form can replace the values of the active/currently selected row.

function getValuesOfActiveRow() {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = s.getSheetByName('SHEET_NAME');
  var rowIndex = sheet.getCurrentCell().getRow();
  var targetRowValues = sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()).getValues()[0];

  // extract relevant values from active/selected row
  var relevantData = [[]]; // array of array
  relevantData.push(targetRowValues);
  var firstName = relevantData[1][1];
  var lastName = relevantData[1][2];
  var emailAddress = relevantData[1][0];
  var schoolName = relevantData[1][4];
  var nativeLanguage = relevantData[1][5];

  return [firstName, lastName, emailAddress, schoolName, nativeLanguage];
}

Research [forms](https://developers.google.com/apps-script/guides/html/communication?hl=en#forms) and `google.script.run` — TheMaster, Dec 11 '21 at 21:15

Cooper · Accepted Answer · 2021-12-11T23:23:38.607

Edit active row values with user input from html-form/google sidebar

GS:

function getValuesOfActiveRow() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Sheet0');
  let vs = sh.getRange(sh.getActiveRange().getRow(),1,1,sh.getLastColumn()).getValues().map(r => [r[1], r[2], r[0], r[3], r[4]]);
  return vs[0];
}

function saveValuesActiveRow(form) {
  Logger.log(form);
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Sheet0');
  sh.getRange(sh.getCurrentCell().getRow(), 1, 1, sh.getLastColumn()).setValues([[form.email,form.first,form.last,form.school,form.lang]])
}

function launchSidebarDialog() {
  let t = HtmlService.createTemplateFromFile('ah1');
  t.row = getValuesOfActiveRow();
  SpreadsheetApp.getUi().showSidebar(t.evaluate());
}

html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
  <form>
    <input type="text" name="first" value="<?= row[0] ?>"/> first
    <br /><input type="text" name="last" value="<?= row[1] ?>" /> last
    <br /><input type="text" name="email" value="<?= row[2] ?>"/> email
    <br /><input type="text" name="school" value="<?= row[3] ?>" /> school
    <br /><input type="text" name="lang" value="<?= row[4] ?>" /> lang
    <br /><input type="button" value="SEND" onClick="send(this.parentNode);" />
  </form>
  <script>
    function send(form) {
      console.log(form)
      google.script.run
      .saveValuesActiveRow(form);
    }
    console.log("my code")
  </script>
  </body>
</html>

emailAddress	firstName	lastName	schoolName	nativeLanguage
email1	first1	last1	school1	native1
email2	first2	last2	school2	native2
email3	first3	last3	school3	native3
email4	first4	last4	school4	native4
email5	first5	last5	school5	native5

You can goto to Google Apps Script Reference and using the search box find any function that you don't understand. If it's a pure JavaScript function then go here

– Not only did your solution put me on the right track, but it also gave me enough material to learn and improve my pretty basic Apps Script knowledge. Thank you so much!!! — Bob Hardball, Dec 12 '21 at 13:06

Apps Script: Edit active row values with user input from html-form/google sidebar

1 Answers1

Edit active row values with user input from html-form/google sidebar