Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Question - Answer from Web Applications [Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script](http://webapps.stackexchange.com/q/89551/88163)

Revision 3 http://webapps.stackexchange.com/revisions/89551/3

Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script

I'm trying to make the edit URL for a Google Form automatically populate in the response Google Sheet that it's attached to. I've seen this already and want to use it, but I'm having issues trying to figure out where exactly to put the script.

I've tried putting it in the script editor in the Google Spreadsheet that I would like the URL to appear in, but I'm not sure where to go from there. In the script editor I've tried to test it as an add-on but that didn't end up working.

I have little experience with scripts and the script editor in Google Sheets. As a final note I am using one other add-on called AutoCrat in the same Google Spreadsheet.

Revision 5 http://webapps.stackexchange.com/revisions/89566/5

#Short answer The link points to a Q&A that has two answers but non-of them looks to me to be appropiate. I.E. the code of one of answers is incomplete and both of them send emails, so the adoption is too complex to be used as a starting point.

Below is a script that is easier to adopt.

#Instructions

  1. Create a test form and note the form ID (between ../d/ and /edit in the url). New form

  2. Set the form to send responses to a new spreadsheet. Connect a spreadsheet

  3. You'll be redirected to the new spreadsheet. Note the active sheet name (rename it if you want) and add a header to the column to be used to hold the response edit URLs, i.e. Edit Url. NOTE: Capitalization is very important, so be very careful on how do you write it. Responses spreadsheet

  4. Go to Tools > Script editor to add a Google Apps Script project bounded to the spreadsheet. Spreadsheet scripts

  5. Replace the default code with the code below (remember to edit the global variables according to your case). Script code

  6. Save the project, and then add an installable form summit trigger (if you get an "Authorization required" dialog, click "Review Permissions" and then "Allow"). Form submit trigger

  7. Submit some sample responses to test the solution.

#Code

/*
 * Global Variables
 */

// Form URL
var formURL = 'https://docs.google.com/forms/d/form-id/viewform';
// Sheet name used as destination of the form responses
var sheetName = 'Form Responses 1';
/*
 * Name of the column to be used to hold the response edit URLs 
 * It should match exactly the header of the related column, 
 * otherwise it will do nothing.
 */
var columnName = 'Edit Url' ;
// Responses starting row
var startRow = 2;

function getEditResponseUrls(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
  var columnIndex = headers[0].indexOf(columnName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = startRow-1; i < data.length; i++) {
    if(data[i][0] != '' && data[i][columnIndex] == '') {
      var timestamp = data[i][0];
      var formSubmitted = form.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      sheet.getRange(i+1, columnIndex+1).setValue(editResponseUrl);
    }
  }
}
@Sphinxxxx

This comment has been minimized.

Copy link

commented Feb 18, 2017

Alternative code (only small changes):

  • Uses FormApp.openById() instead of FormApp.openByUrl(), in case Google's URL format changes in the future.
  • More robust checking of empty cells (data[][]), in case the value in an empty cell is ever null or undefined instead of an empty string.

_

// Form
var formId = 'INSERT_FORM_ID_HERE';
// Sheet
var sheetName = 'Form Responses 1';
var columnName = 'Edit Url';

// Responses starting row
var startRow = 2;

function getEditResponseUrls(){
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
    var columnIndex = headers[0].indexOf(columnName);
    var data = sheet.getDataRange().getValues();

    var form = FormApp.openById(formId);

    for(var i = startRow-1; i < data.length; i++) {
        if(data[i][0] && !data[i][columnIndex]) {

            var timestamp = data[i][0];
            var formSubmitted = form.getResponses(timestamp);
            if(formSubmitted.length < 1) continue;

            var editResponseUrl = formSubmitted[0].getEditResponseUrl();
            sheet.getRange(i+1, columnIndex+1).setValue(editResponseUrl);
        }
    }
}
@francescob

This comment has been minimized.

Copy link

commented May 3, 2017

hello, i've been trying to use this solution and seems like it works only if the edit url column is the last one of the sheet, am i right?

@prof-cg

This comment has been minimized.

Copy link

commented Jul 11, 2017

The url google form must be in the full 'HTTPS://...../viewform' configuration for this first script to work. The second script had some errors running for me based on ID.

@WhiteRabbitObject

This comment has been minimized.

Copy link

commented Jul 14, 2017

I'm trying to do this and I can't either script to work. I'm hoping its something simple I'm overlooking. when I try the alternative script I get this error on form submit.

7/14/17 1:08 PM | getEditResponseUrls | No item with the given ID could be found, or you do not have permission to access it. (line 16, file "Code") | formSubmit | 7/14/17 1:08 PM

When I try the first script I also get this

7/14/17 1:29 PM | getEditResponseUrls | No item with the given ID could be found, or you do not have permission to access it. (line 23, file "Code") | formSubmit | 7/14/17 1:29 PM
The form is accessible for anyone I have tried the form URL with and without /viewform at the end.

I have verified the form URL and that it works when you put it into a web browser. When you put the form URL in the browser without /viewform, /edit is automatically attached to the end of the URL and it does give a 'not found error'.

Any idea would be appreciated.

@paulolisarb

This comment has been minimized.

Copy link

commented Aug 16, 2017

I have faced the same issue WhiteRabbitObject faced. I used the script Sphinxxxx and I noticed the formID is the ID when you are editing the form. So the correct ID is the one with /edit at the end. Im my case.
image

@dougm-work

This comment has been minimized.

Copy link

commented Jan 27, 2018

I have tried both scripts above and neither work. I have carefully followed the instructions on setting the globals. I have changed the trigger from form to submit to "on open" and still nothing.

In the debugger, the first line of the function always results in sheet = NULL.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

Any hints as to what I am doing wrong?
dougm

@sahoban

This comment has been minimized.

Copy link

commented Feb 3, 2018

Thank you so very much! This worked for me after HOURS of trying to figure it out on my own. Your screenshots were awesome. It is great to see someone with your level of understanding who is willing to address the woefully ignorant in so helpful a manner. Thank you again!

@MilicaS25

This comment has been minimized.

Copy link

commented Feb 5, 2018

Thank you for excellent explanation. Everything worked fine when I had only one form in one spreadsheet. But here is something more challenging. I tried to insert three forms in three different tabs on one google sheet documents and to create three different script files. But that does not working as I planned. Can you help what I did wrong? Thanks
url problem
url problem1

@emilyferguson91

This comment has been minimized.

Copy link

commented Apr 5, 2018

This script is great and I've got it working for when the responses and URLs are being populated for new form entries (i.e. entries post adding in the script)

However, I want to import my historical data into the same tab that will be collecting the new form responses and URLs, which will populate underneath. However, where the 'Edit Url' column is blank, the script populates URL's for my old historic data too. How can I stop this? I only want URL's populated by the script for new form responses.

Any help offered would be much appreciated!

@jaimepelegri

This comment has been minimized.

Copy link

commented Dec 15, 2018

rubenrivera, buenas tardes desde España, intento seguir los pasos que me indicas pero me da el siguiente error, sabes qué puedo estar haciendo mal?

Muchísimas gracias por tu ayuda, me estoy volviendo loco y no sé qué debo hacer.

TypeError: No se puede llamar al método "getRange" de null. at getEditResponseUrls(Código:20)

@rubenrivera

This comment has been minimized.

Copy link
Owner Author

commented Dec 15, 2018

@jaimepelegri Como te comenté en un mensaje privado, es posible que el error se deba a que en tu hoja de cálculo no existe una hoja con el nombre especificado.

Para realizar consultas en español sobre programación te sugiero ir a https://es.stackoverflow.com

@schlos

This comment has been minimized.

Copy link

commented Jan 1, 2019

I have faced the same issue WhiteRabbitObject faced. I used the script Sphinxxxx and I noticed the formID is the ID when you are editing the form. So the correct ID is the one with /edit at the end. Im my case.
image

This worked for me. Thanks!

@josh-meinders

This comment has been minimized.

Copy link

commented Jul 6, 2019

Alternate code I created to solve this problem: https://github.com/josh-meinders/append-google-forms-edit-response-link

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.