Apps Script: Get user input from HTML form and save them as global variables-CodePudding

The server side script below receives user input from an HTML form and adds these user data/input to the last available row of my Google Sheet. It´s been working pretty fine. But now I want to store some elements of the array that is in this script as global variables, so that I can re-use them later on in other server side functions bound to the same Google Sheet. I am specifically interested in the values inside lastName, email and phone. Any idea how this can be done?

Thank you so much in advance for your hints and help.

function AddUserInputToSheet(gender, firstName, lastName, age, email, phone) {
  var url = 'SHEET_URL';
  var ss = SpreadsheetApp.openByUrl(url);
  var webAppSheet = ss.getSheetByName("SHEET_NAME");

  webAppSheet.appendRow([gender, firstName, lastName, age, email, phone]);
}

CodePudding user response：

You can use

Then I run the 'setProperties()' and rerun the readProperties() function again:

I reload the script page and ran the readProperties() function:

To add it in your script, you can set the properties in AddUserInputToSheet() and call it anywhere in your script.

Example:

function AddUserInputToSheet(gender, firstName, lastName, age, email, phone) {
  var url = 'SHEET_URL';
  var ss = SpreadsheetApp.openByUrl(url);
  var webAppSheet = ss.getSheetByName("SHEET_NAME");
  webAppSheet.appendRow([gender, firstName, lastName, age, email, phone]);
  
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperties({'lastName': lastName, 'email': email, 'phone':phone})
}

function someFunction(){
  var scriptProperties = PropertiesService.getScriptProperties();
  var data = scriptProperties.getProperties();
  var lastName = data["lastName"];
  var email = data["email"];
  var phone = data["phone"];
  //some operations here
}

CodePudding user response：

Here's an example:

function myfunk1() {
  PropertiesService.getScriptProperties().setProperty('Global1',JSON.stringify(SpreadsheetApp.getActive().getSheetByName('Sheet0').getDataRange().getDisplayValues()));
}

function myfunk2() {
  const vs = JSON.parse(PropertiesService.getScriptProperties().getProperty('Global1'))
  SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange(1,1,vs.length,vs[0].length).setValues(vs);
}