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

user_13151877 0
Viewed 792

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]);
}
google-apps-script javascript
2 Answers

You can use Properties Service of Apps Script.

This service allows scripts to store strings as key-value pairs scoped to one script, one user of a script, or one document in which an editor add-on is used.

In your case, there are 2 options you can choose. Script Properties and User Properties.

The difference between the two is the content of Script Properties are shared to all users while User Properties is only available to the current user of a script, add-on, or web app.

Here I created an example of how to use Properties.

function setProperties(lastName = "Test Last Name", email = "Test Email", phone = "Test Phone"){
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperties({'lastName': lastName, 'email': email, 'phone':phone})  
}

function readProperties(){
  var scriptProperties = PropertiesService.getScriptProperties();
  Logger.log(scriptProperties.getProperties());
}

Here I run the readProperties() function first and the result is

enter image description here

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

enter image description here

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

enter image description here

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
}
user_146060520

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);
}
user_72150910
Related