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

addFormResponseIdAndUrl.gs

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

Usage

1. In the Google Sheet linked to your Google Form(s), go to Tools > Script Editor
2. Copy Code.gs from this repository into the Script Editor
3. In the Script Editor, go to Run > Run function > createFormTrigger
4. Now on each Google Form submission, the response id and edit response link will be added to the end of the row; it also works for multiple forms if you link more than one to your spreadsheet.

Supports Rapid Submissions?

Many other scripts that add the edit response url grab the last form response, but if responses are submitted in rapid succession, this can cause the wrong url to be posted in the Google Sheet row.

This implementation tries to more accurately match the form response with the corresponding row by comparing the form submit event object values to the form response item response values. Unfortunately, the timestamp in Google Sheets and Google Forms seem to differ by about one second, so you can't match a row to a response based on the timestamp.

The best we can do is compare all the form response values to the form submit event object values. This should be enough to uniquely match the response to the correct row unless two responses are received with identical values in rapid succession (less than ~0.5 seconds).

Relevent Documentation

https://developers.google.com/apps-script/guides/triggers/events#form-submit

https://developers.google.com/apps-script/reference/forms/form-response

https://developers.google.com/apps-script/reference/script/script-app

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