Send email when Google Form submission is updated

Question

I have read plenty of posts about notifying someone via email when a form has been submitted and including the form data in the email but what I would like to do is identify an update to already existing information and have that put in an email and sent. I imagine that this would be done by identifying the latest timestamp and grabbing the corresponding entry. However, my extremely limited programming skills mean that I have been unable to work out the answer.

Answer 1

You can do this using Google App Script, open Spreadsheet where your form responses are being stored. Go to Tools -> Script Editor -> Script editor will be opened, copy the below function.

Create a trigger to call this function on form submission Resources -> Current project's triggers -> Select 'from spreadsheet' and 'On form submit' and create a trigger.

function sendEmailNoti(e) {
   var timeStamp = e.values[0];
   var userEmail = e.values[1];
   var products = e.values[2];

   var subject = "New Subscription Request";
   var email = "[email protected]";
   var message = "User email : " + userEmail + "<BR>Products opted : " + products + "<BR>Timestamp : " + timeStamp;


   MailApp.sendEmail({
     to: email,
     subject: subject,
     htmlBody: message
   });
}

Answer 2

It just happens I have such a function in my bag of tricks!

This function assumes that you've got a trigger function formSubmitted(e) for form submission that utilizes the event object it receives. As you surmised, it identifies the most recent update. It then creates a simulated form submission event, and invokes the form submission trigger function.

Set onChange() up to trigger on spreadsheet changes.

/**
 * Sometimes, Google Apps Script sucks. This is one of those times.
 * If a user updates previously submitted form data by using the "edit" link,
 * that data will be updated in the Form Responses sheet. Unfortunately,
 * that will NOT generate a spreadsheet form submission event. (See explanation:
 * https://code.google.com/p/google-apps-script-issues/issues/detail?id=2969#c12)
 *
 * Observation: a form edit shows up as a change, with type of "OTHER", and source
 * of a spreadsheet with activeSheet="Form Responses" and activeRange="A1". We
 * will use that information to limit when we force our normal form-submission
 * handler to run. Unfortunately, the event doesn't tell us which row changed,
 * but that should be the one with the most recent timeStamp. 
 */
function onChange( e ) {
  if (e.changeType !== "OTHER") return;
  if (e.source.getActiveSheet().getName().indexOf("Form Responses") === -1) return;
  if (e.source.getActiveRange().getA1Notation() !== "A1") return;

  // Find latest form submission
  var sheet = e.source.getActiveSheet();
  var timeStamps = sheet.getRange("A2:A").getValues();

  // Determine most recent date
  var max = Math.max.apply(null,transpose(timeStamps)[0]);
  var properties = PropertiesService.getDocumentProperties();
  var lastMax = properties.getProperty("maxDate");
  if (lastMax && max.toString() === lastMax) return; // Avoid changes triggered by updates from onChange function
  Logger.log("onChange() thinks there's been a form edit. e="+JSON.stringify(e));
  properties.setProperty("maxDate", max.toString());
  var maxDate=new Date(max);

  // Then find the sheet row that contains it
  for (var row=0,found=false;row<timeStamps.length&&!found;row++) {
    if (timeStamps[row][0] && timeStamps[row][0].getTime() === maxDate.getTime()) {
      found = true;
      break;
    }
  }

  if (!found) {
    // We're blind... recalc everything
    // TODO
  }
  else {
    // Trigger the submission event for this row
    row += 2; // adjust for offset into sheet rows
    var dataRange= sheet.getDataRange();
    var data = dataRange.getValues();
    var headers = data[0];
    var response = data[row];
    var respRange = dataRange.offset(row-1, 0, 1, dataRange.getLastColumn());

    // Build event object with range, values and namedValues filled in
    var values = respRange.getValues()[0];
    var namedValues = {};
    for (var h in headers) { namedValues[headers[h]] = [values[h]]; } 
    var e = { range: respRange,
              values: values,
              namedValues: namedValues
            };
     formSubmitted(e);        // Simulate event
  }
}

Answer 3

I approached this problem slightly differently. Instead of creating a new trigger - modified the form submit trigger itself slightly to pick values for each key from the spreadsheet instead of from the form responses.

The revised code looks like this

// Include a copy of all responses
for ( var keys in columns ) {
  var key = columns[keys];
  message += key + ' :: '+ sheet.getRange(resultUrls.length+1, (data[0].indexOf(key)+1)).getValues() + "<br />";
}