APPS SCRIPT: Custom Confirmation Page + Add-Row When User Clicks URL

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.
Screen Shot 2020-09-18 at 2.09.14 PM


When the URL is viewed, this also adds a new row to the “Response” sheet.

Screen Shot 2020-09-18 at 2.11.12 PM


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)

Screen Shot 2020-09-18 at 2.13.54 PM


:warning: NOTES ON SECURITY :warning:

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?

4 Likes

Wow cool idea! I will definetly play around with this! Thank you!

1 Like

Nice! Another tool on the belt to help expand the AppSheet capabilities.

Since you asked for other ideas…

I’m wondering why the need to go outside of the AppSheet platform for this use case?

My thoughts are that you could use a modified Detail view to serve as the Confirmation Message. When an appointment is entered, you can navigate to the Confirmation Message Detail View using the “Finish View” property on the Form.

For notifications/reminders, Push notifications are ideal. You can even include a link to the appointment so the user can go straight to the appointment to make any changes.

It’s useful when the ‘user’ clicking the URL isn’t an AppSheet user. You could email a confirmation link to a ‘Contact’ stored in your app and collect a response without that contact needing an AppSheet account.

Imagine if you had thousands of Contacts and only wanted to collect a single response to verify their email, or subscribe/unsubscribe from a mailing list. Why bother having thousands of users download and install AppSheet to submit a single response? You could use a Google Form with a prefilled link, but you wouldn’t get dynamic confirmation pages based on the response.

Yes, I’d probably use this method as well if the link was being emailed to an active AppSheet user. I guess I forgot to explain that part in the original post.

But in my use-case, 'user 1’ is creating/sending the link as an AppSheet user, and ‘user 2’ clicking the link could be anyone-- without an AppSheet account. The confirmation page is a message to user2, so a detail view is not an option.

4 Likes

Got it! Makes sense.

1 Like

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.

2 Likes