Form owners may need edit a few form responses in the spreadsheet to correct some information. This topic seems rippling over and over again for years but Google still doesn't support the feature. We used to think that by editing the cells in the linked spreadsheet, these responses will change too. But unfortunately it doesn't. To edit the real responses, you need a link called Form Response Edit URL.

This tutorial shows you how an apps script xfanatical built can allow you edit form responses directly in spreadsheet.

Step 1. Prepare your form and spreadsheet

  • You have a spreadsheet connected with the form as a response destination.
A dialog shows select response destination, a spreadsheet, for the forms
  • You have enabled Respondents can Edit after submit option in the Settings of form.
Enable Respondents can edit after submit options in the google forms settings

Step 2. Set up the magic Apps Script in Your Spreadsheet

  • Copy the apps script in the Source code section below.
  • Open up the spreadsheet with which the form is associated.
  • Click Tools in the menu bar, followed by Script editor.
This image shows you the script editor submenu in the Tools menu.
script editor in google spreadsheets
  • In the popped Script Editor tab
    • Give the script editor a name, say Add Form Response Edit URLs
    • Paste the code.
    • Click save.
Set up apps script in script editor
Set up apps script in script editor
  • Now go back to Spreadsheet tab. Refresh the page.
  • You will see a new Menu Forms > Add Form Response Edit URLs by the end of menu bar.
The image shows you the new menu shown after a refresh of the spreadsheet
the form response edit url menu

Step 3. Automation

It's tiring of setting up all the pieces, isn't it? I know, it's boring to handle technical issues sometimes. But we're almost there.

  • Click that Add Form Edit Response URLs menu.
  • Wait for the script running. If the Authorization window popups, don't panic, just go through it. The Apps Script is safe.
  • You will see the script adds a new column Form Response Edit URL at the far right of sheet (If you don't see it, scroll right). It then adds one-by-one the urls to the responses. Amazing, huh?
this image shows you a final result sample after adding form response edit urls to the spreadsheet. The links are automatically added in a spreadsheet column.
final result of adding form response edit urls to the spreadsheet
  • BONUS: Now ask your respondent to submit a new response. The Form Response Edit URL will be automatically attached to Spreadsheet for the new response!

Step 4. Edit responses in the spreadsheet!

  • Now you have a response edit url for every submission, you are free to click those links and make changes to them.
  • The changes will be reflected soon in the spreadsheet.

Source code

/**
 * @license MIT
 * 
 * © 2019-2020 xfanatical.com. All Rights Reserved.
 *
 * @since 1.0.0 Add all edit response urls and update new urls for new submissions
 */
function registerNewEditResponseURLTrigger() {
  // check if an existing trigger is set
  var existingTriggerId = PropertiesService.getUserProperties().getProperty('onFormSubmitTriggerID')
  if (existingTriggerId) {
    var foundExistingTrigger = false
    ScriptApp.getProjectTriggers().forEach(function (trigger) {
      if (trigger.getUniqueId() === existingTriggerId) {
        foundExistingTrigger = true
      }
    })
    if (foundExistingTrigger) {
      return
    }
  }

  var trigger = ScriptApp.newTrigger('onFormSubmitEvent')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onFormSubmit()
    .create()

  PropertiesService.getUserProperties().setProperty('onFormSubmitTriggerID', trigger.getUniqueId())
}

function getTimestampColumn(sheet) {
  for (var i = 1; i <= sheet.getLastColumn(); i += 1) {
    if (sheet.getRange(1, i).getValue() === 'Timestamp') {
      return i
    }
  }
  return 1
}

function getFormResponseEditUrlColumn(sheet) {
  var form = FormApp.openByUrl(sheet.getFormUrl())
  for (var i = 1; i <= sheet.getLastColumn(); i += 1) {
    if (sheet.getRange(1, i).getValue() === 'Form Response Edit URL') {
      return i
    }
  }
  // get the last column at which the url can be placed.
  return Math.max(sheet.getLastColumn() + 1, form.getItems().length + 2)
}

/**
 * params: { sheet, form, formResponse, row }
 */
function addEditResponseURLToSheet(params) {
  if (!params.col) {
    params.col = getFormResponseEditUrlColumn(params.sheet)
  }
  var formResponseEditUrlRange = params.sheet.getRange(params.row, params.col)
  formResponseEditUrlRange.setValue(params.formResponse.getEditResponseUrl())
}


function onOpen() {
  var menu = [{ name: 'Add Form Edit Response URLs', functionName: 'setupFormEditResponseURLs' }]
  SpreadsheetApp.getActive().addMenu('Forms', menu)
}

function setupFormEditResponseURLs() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var formURL = sheet.getFormUrl()
  if (!formURL) {
    SpreadsheetApp.getUi().alert('No Google Form associated with this sheet. Please connect it from your Form.')
    return
  }
  var form = FormApp.openByUrl(formURL)

  // setup the header if not existed
  var headerFormEditResponse = sheet.getRange(1, getFormResponseEditUrlColumn(sheet))
  var title = headerFormEditResponse.getValue()
  if (!title) {
    headerFormEditResponse.setValue('Form Response Edit URL')
  }

  var timestampColumn = getTimestampColumn(sheet)
  var editResponseUrlColumn = getFormResponseEditUrlColumn(sheet)
  for (var i = 2; i <= sheet.getLastRow(); i += 1) {
    var isUrlCellEmpty = sheet.getRange(i, editResponseUrlColumn).getValue() === ''
    if (isUrlCellEmpty) {
      var timestamp = new Date(sheet.getRange(i, timestampColumn).getValue())
      if (timestamp) {
        var formResponse = form.getResponses(timestamp)[0]
        addEditResponseURLToSheet({
          sheet: sheet,
          form: form,
          formResponse: formResponse,
          row: i,
          col: editResponseUrlColumn,
        })
      }
    }
  }

  registerNewEditResponseURLTrigger()
  SpreadsheetApp.getUi().alert('You are all set! Please check the Form Response Edit URL column in this sheet. Future responses will automatically sync the form response edit url.')
}

function onFormSubmitEvent(e) {
  var sheet = e.range.getSheet()
  var form = FormApp.openByUrl(sheet.getFormUrl())
  var formResponse = form.getResponses().pop()
  addEditResponseURLToSheet({
    sheet: sheet,
    form: form,
    formResponse: formResponse,
    row: e.range.getRow(),
  })
}

Takeaway

As you would iterate the form and spreadsheet over time, the apps script may not handle those changes and perform weirdly. If so, please leave any comments below. Thanks for reading.