Google Script – Edit Form Submission update

I just finished an update of an earlier Google Script that I wrote.  The earlier script did two things: First, it added the link to the re-edit URL on the spreadsheet itself so that whoever is managing the sheet can have a record of it.  Second, it automatically emailed the link back to the person submitting the form. The updated version of this script, shown below, adds a bit of functionality. In addition to sending the person submitting the form a link, it also sends them the name and message that they submitted. This allows the person submitting to make sure that everything is fine in their submission. I’ve pasted the code below so you can copy directly from this post.  To use the script, set up a Form and a Response sheet using Google Drive.  Then go to the Response sheet and click Tools > Script Editor. Create a new Spreadsheet Script and delete all of the code that appears.  Paste the code below into that window.  Finally, set up an automatic trigger as described in the comments at the top of the code.  If you have any questions, send me an email and I’ll try to help!

/* Script updated on 2 August 2015 by Paul Swanson (@teachertechpaul)


TO PEOPLE USING THIS SCRIPT:
This is an updated version of an earlier script designed to record 
the re-edit URL of a form submission and also to email that link back 
to the person who filled out the form. In this update, the email message 
also includes a name field and the comment submitted by the form user.
 
 To use this script, you
 need to do a couple of things:
 
 1 - Edit the 'COLUMN_NUMBER_OF_EMAIL', COLUMN_NUMBER_OF_NAME, 
and COLUMN_NUMBER_OF_MESSAGE variables to match the columns on your 
response sheet that contains the values of those submitting responses
 
 2 - Edit the emailSubject and emailBody variables to match your needs
 
 3 - Click Resources, Current Project Triggers. Click the link:
 'No triggers set up. Click here to add one now.'
 
 Set your trigger with the following properties:
 - Run: 'editFormSubmission'
 - Events: 'From spreadsheet' and 'On form submit'
 
 That's it! If you like this script, email me at paul@teacherpaul.org

*/
COLUMN_NUMBER_OF_TIMESTAMP = 1;
COLUMN_NUMBER_OF_EMAIL = 2;
COLUMN_NUMBER_OF_NAME = 3;
COLUMN_NUMBER_OF_MESSAGE = 4;

function editFormSubmission(e) {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName('Form Responses 1');
 
 /* First we get the Edit URL of the form submission where timestamps match */
 var formResponses = FormApp.openByUrl(ss.getFormUrl()).getResponses();
 var lastResponse = formResponses[compareTimeStamps(e, formResponses)];
 var formEditUrl = lastResponse.getEditResponseUrl();
 
 /* Next we set the formEditUrl as a link on the Spreadsheet itself */
 var responseRow = e.range.getRow();
 var editUrlCol = getEditUrlCol(sheet, "Form Edit URL");
 var formEditCell = '=HYPERLINK("' + formEditUrl + '","Edit Link")';
 sheet.getRange(responseRow, editUrlCol).setValue(formEditCell);
 
 /* Then we create variables to send in our email */
 var emailAddress = sheet.getRange(responseRow, COLUMN_NUMBER_OF_EMAIL).getValue(); 
 var submittedName = sheet.getRange(responseRow, COLUMN_NUMBER_OF_NAME).getValue();
 var submittedMessage = sheet.getRange(responseRow, COLUMN_NUMBER_OF_MESSAGE).getValue();
 
 var emailSubject = "Link to Re-Edit your Submission";
 var emailBody = "Thank you " + submittedName + "," + "<br><br>
Please confirm the following information. If you would like to edit it, please click " + '<a href="' + formEditUrl + '">this link.</a>' + "<br><br>Student Name: " + submittedName + "<br>Comment: " + submittedMessage + "<br><br>Regards";
 
 /* Finally we log the information and email it to the recipient */
 Logger.log("Send email to " + emailAddress + " with subject: " + emailSubject); 
 Logger.log("Email body: " + emailBody);
 MailApp.sendEmail({
 to: emailAddress,
 subject: emailSubject,
 htmlBody: emailBody
 });
}

/* The following function converts the form submission timestamp into milliseconds, then compares it to the timestamps on the spreadsheet.
This ensures that the correct response is selected for the Edit URL */
function compareTimeStamps(e, formResponses) {
 
 // variable checktime is used to compare the timestamp on the spreadsheet.
 var checkTime = new Date(e.values[COLUMN_NUMBER_OF_TIMESTAMP-1]).getTime();
 
 // variable timeDifference is initialized as the number of millis between the first form timestamp and checktime
 var timeDifference = Math.abs(new Date(formResponses[0].getTimestamp()).getTime() - checkTime);
 var closestResponseNumber = 0;
 
 for (var responseNumber = 0; responseNumber < formResponses.length; responseNumber++) {
 var formResponse = formResponses[responseNumber];
 var newTimeDifference = Math.abs(new Date(formResponse.getTimestamp()).getTime() - checkTime);
 if (newTimeDifference < timeDifference ) {
 timeDifference = newTimeDifference;
 closestResponseNumber = responseNumber;
 Logger.log("Closer time match found: response " + responseNumber + " at " + timeDifference + " millis"); 
 } 
 }
 return closestResponseNumber;
}
 
 /* The following function either finds and returns the column number 
that is headed "Form Edit URL", or it creates a column titled this 
 */
 function getEditUrlCol(sheet, headerStr){
   for (var col=1; col<= sheet.getLastColumn(); col++) {
     if (sheet.getRange(1, col).getValue() == headerStr) {
       return col;
     }
   }
 
   // If this section runs, it means header is not established
   sheet.getRange(1, sheet.getLastColumn() + 1).setValue("Form Edit URL");
   return (sheet.getLastColumn());
 
 }