0
315
ViewsApps Script: Get user input from HTML form and save them as global variables
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]);
}
2 answers
Answer question0
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
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
}
0
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);
}