I have two working trigger functions in Google Script that fire when a form response spreadsheet gets a new submission. One inserts the "edit your submission" url into the spreadsheet. The other looks up the response's email and sends them a confirmation.
What I'm having a hard time understanding is how to populate the url first, and then send an email containing that url.
(Google Script is different to debug than js in the browser :\ )
Setup triggers
function Initialize() {
var triggers = ScriptApp.getScriptTriggers();
for (var i in triggers) {
ScriptApp.deleteTrigger(triggers[i]);
}
ScriptApp.newTrigger("SendConfirmationMail")
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onFormSubmit()
.create();
assignEditUrls();
}
On form submit, searches for column titled "Clients Email", and sends a formatted email to them.
function SendConfirmationMail(e) {
try {
var ss, cc, sendername, subject, columns;
var message, value, textbody, sender;
var url;
// This is your email address and you will be in the CC
cc = Session.getActiveUser().getEmail();
// This will show up as the sender's name
sendername = "XXXX";
// Optional but change the following variable
// to have a custom subject for Google Docs emails
subject = "Form Complete: Mobile App - Client Questionnaire";
// This is the body of the auto-reply
message = "Confirmation text here.<br><br>Thanks!<br><br>";
ss = SpreadsheetApp.getActiveSheet();
columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
// This is the submitter's email address
sender = e.namedValues["Clients Email"].toString();
// Only include form values that are not blank
for ( var keys in columns ) {
var key = columns[keys];
if ( e.namedValues[key] ) {
message += key + ' :: '+ e.namedValues[key] + "<br />";
}
}
textbody = message.replace("<br>", "\n\n");
GmailApp.sendEmail(sender, subject, textbody,
{cc: cc, name: sendername, htmlBody: message});
} catch (e) {
Logger.log(e.toString());
}
}
Separate function. Looks up form and applies the edit URL to the 26th column.
function assignEditUrls() {
var form = FormApp.openById('10BVYipGhDa_AthabHE-xxxxxx-hg');
//enter form ID here
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
//Change the sheet name as appropriate
var data = sheet.getDataRange().getValues();
var urlCol = 26; // column number where URL's should be populated; A = 1, B = 2 etc
var responses = form.getResponses();
var timestamps = [], urls = [], resultUrls = [];
for (var i = 0; i < responses.length; i++) {
timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
urls.push(responses[i].getEditResponseUrl());
}
for (var j = 1; j < data.length; j++) {
resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
}
sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);
}