Google Script – Edit Form Submission

Just finished another Google Script and I wanted to share it with you all.  There is an option when setting up a Google Form so that the submitter can go back and edit the responses.  However, if the submitter does not bookmark the URL immediately, it’s impossible to go back later and find it.

This script solves that problem by doing two things: First, it adds 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 emails the link back to the person submitting the form.

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!

***Note*** This is an older version of the script that I’ve left up because it was getting lots of traffic. For a newer version, take a look at my post here.

/* Script created on 27 Sept 2014 by Paul Swanson (@teachertechpaul)


TO PEOPLE USING THIS SCRIPT:
 The purpose of this script is 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. To use this script, you
 need to do a couple of things:
 
 1 - Edit the 'COLUMN_NUMBER_OF_EMAIL' variable to match the column on your response sheet
 that contains the email address of the submitter
 
 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_EMAIL = 2;

function editFormSubmission(e) {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName('Form Responses 1');
 
 /* This section gets the Edit URL of the most recent submission so that it can
 be sent via email back to the user. It also matches the time stamp of the form
 reponse with that of the spreadsheet to insert the Edit Url in the sheet itself.
 */
 
 var formUrl = ss.getFormUrl();
 var form = FormApp.openByUrl(formUrl);
 var formResponses = form.getResponses();
 var lastResponse = formResponses[formResponses.length - 1];
 var formEditUrl = lastResponse.getEditResponseUrl();
 var formEditCell = '=HYPERLINK("' + formEditUrl + '","Edit Link")';
 var timeStamp = lastResponse.getTimestamp().valueOf();
 var editUrlCol = getEditUrlCol(sheet, "Form Edit URL");
 
 for (var row=1; row <= sheet.getLastRow(); row++) {
 var testTimeCell = sheet.getRange(row, 1).getValue().valueOf();
 
 if (timeStamp == testTimeCell) {
 sheet.getRange(row, editUrlCol).setValue(formEditCell);
 }
 }
 Logger.log(lastResponse.getItemResponses());
 
 var emailAddress = sheet.getRange(sheet.getLastRow(), [COLUMN_NUMBER_OF_EMAIL]).getValue();
 var emailSubject = "Link to Re-Edit your Submission";
 var emailBody = "Thank you for your submission! If you would like to continue editing it, please click the link below." +
 String.fromCharCode(10) + String.fromCharCode(10) + formEditUrl;
 
 Logger.log("Send email to " + emailAddress + " with subject: " + emailSubject); 
 Logger.log("Email body: " + emailBody);
 
 MailApp.sendEmail(emailAddress, emailSubject, emailBody);
}

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());
 
}