Skip to content

Instantly share code, notes, and snippets.

@joshm21
Created April 10, 2020 05:07
Show Gist options
  • Save joshm21/ae2ac38bb282cb25ea19113462d887e2 to your computer and use it in GitHub Desktop.
Save joshm21/ae2ac38bb282cb25ea19113462d887e2 to your computer and use it in GitHub Desktop.
On Google Form submission, append the response id and edit response link to the Google Sheet row. Supports rapid form submissions. #google-apps-script #forms
function createFormTrigger() {
var triggerFunctionName = "addFormResponseIdAndUrl_";
deleteAllExistingTriggers_();
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
ScriptApp.newTrigger(triggerFunctionName)
.forSpreadsheet(spreadsheet)
.onFormSubmit()
.create();
}
function deleteAllExistingTriggers_() {
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
}
function addFormResponseIdAndUrl_(e) {
// Get the Google Form linked to the response
var responseSheet = e.range.getSheet();
var googleFormUrl = responseSheet.getFormUrl();
var googleForm = FormApp.openByUrl(googleFormUrl);
// Get the values submitted to the sheet
// (can't rely on e.values since it returns empty strings for unedited fields when an editted response is submitted)
var sheetValues = e.range.getValues()[0];
// Loop through form responses backward (most recent first) and check for match
var formResponses = googleForm.getResponses();
for (var i = formResponses.length - 1; i >=0; i--) {
var formResponse = formResponses[i]
if(isEqual_(sheetValues, formResponse)) { break; }
}
// Get the Form response URL and Id and add it to the Google Spreadsheet
var responseUrl = formResponse.getEditResponseUrl();
var responseId = formResponse.getId();
var row = e.range.getRow();
var responseColumn = e.values.length + 1;
responseSheet.getRange(row, responseColumn, 1, 2).setValues([[responseId, responseUrl]]);
}
function isEqual_(sheetValues, formResponse) {
// Does not take into account timestamp (Google Form and Sheets timestamp often differ by 1 second)
// Should be ok unless...
// from time when addFormResponseIdAndUrl_ starts to var formResponses = googleForm.getResponses() (~0.5 seconds)...
// another response with exact same answers is submitted
// uncomment these two lines to see that the Sheet timestamp and Form timestamp differ
// Logger.log(sheetValues[0]);
// Logger.log(formResponse.getTimestamp());
sheetValues.shift(); // remove timestamp and check all other values
var itemResponses = formResponse.getItemResponses();
for (var i = 0; i < sheetValues.length; i++) {
if (sheetValues[i] != itemResponses[i].getResponse()) { return false }
}
return true;
}
@victorpod
Copy link

It does not work,
generates the same link (first response) for all responses

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment