LinkedIn
Twitter
This script will add a new row to a table, and display a custom confirmation page to the user.
The confirmation page is dynamic HTML, fed from the “Message” sheet.
When the URL is viewed, this also adds a new row to the “Response” sheet.
Once the script is published as a webapp, dynamic links can be generated in AppSheet and used in Actions, Workflows, Email Templates, etc.
(https://script.google.com/macros/s/{SCRIPT_ID}/exec?Key=2&Email=TEST@GMAIL.COM&Status=CONFIRMED)
(https://script.google.com/macros/s/{SCRIPT_ID}/exec?Key=3&Email=TEST@GMAIL.COM&Status=CANCELLED)
NOTES ON SECURITY
There is no secure login.
- The script writes values to the sheet based on the URL parameters in the link.
- Anyone could build new links with another user’s email address and respond as them.
- Refreshing the page submits a new response.
SCRIPT
Summary
function doGet(e) {
var p = e.parameter;
var key =p.Key;
var email = p.Email;
var status = p.Status
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = 'Message';
var sh = ss.getSheetByName(sheetName);
var lastCol = sh.getLastColumn();
var range = sh.getRange(key, 2, 1, lastCol).getValues();
var h1 = '<!DOCTYPE html><html><body><h1>' + range[0][0] + '</h1>';
var h2 = '<h2>' + range[0][1] + '</h2>';
var h3 = '<h3>' + email + '</h3>';
var body = range[0][2] + '<p><p>';
var footer = range[0][3] + '</body></html>';
var msg = h1 + h2 + h3 + body + footer;
var response = ss.getSheetByName('Response');
var lastRow = response.getLastRow();
var newRow = [];
var d = new Date();
newRow.push([[d],[email],[status]]);
response.getRange(lastRow+1, 1,1,3).setValues(newRow);
return HtmlService.createHtmlOutput(msg);
}
This is a work in progress that I wanted to share to get ideas from the community. I’m going to work on a button for the confirmation page, so that the user has to click to add a row instead of every view/refresh adding a row.
It could also use some validation on each input, and only write valid requests to the sheet.
Other ideas?
That’s cool @GreenFlux
I was using GAS for this as explained by @LeventK
But in my opinion it takes to long to open up the page. 2-3 seconds to me.
Integromat seems a little bit faster to me.
