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);
    }
  }
}
@MilicaS25
Copy link

MilicaS25 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
Copy link

emilyferguson91 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
Copy link

jaimepelegri 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
Copy link
Author

rubenrivera 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
Copy link

schlos 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!

@joshm21
Copy link

joshm21 commented Jul 6, 2019

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

@hennar
Copy link

hennar commented Feb 22, 2020

I'm getting stuck with a weird error where the script always returns the same result visibly, namely the text in the cell is the edit link for the very first submission of the form but the cell links to the actual submission for that line.

Screenshot 2020-02-22 at 01 02 55

Screenshot 2020-02-22 at 01 03 19

@rubenrivera
Copy link
Author

rubenrivera commented Feb 22, 2020

@hennar To "solve" this problem just make the Edit Url column wider

@redravioli
Copy link

redravioli commented Mar 20, 2020

Just wanted to say thank you for a well written solution to this problem - just what I need.

@rafialikhan
Copy link

rafialikhan commented Jun 10, 2020

Does this code work even when users update their response (edit response is turned on?) What i have noticed is that the order of form responses change and this results in the wrong EditResponseUrl being assigned against each row. (Does your code avoid this issue?)

@jmendoza4
Copy link

jmendoza4 commented Oct 10, 2020

Hello friends, could you help me? The code does not work for me I do not know what I am doing wrong
form

@AnferLl
Copy link

AnferLl commented Nov 30, 2020

Hi Everyone, I have the same issue as @hennar, same answers. @rubenrivera I tried to wide up as much as I could the column of the EditUrls but it didn't change anything. Is anyone having the same issue? If so, how did you resolve it? Thank you!

I'm getting stuck with a weird error where the script always returns the same result visibly, namely the text in the cell is the edit link for the very first submission of the form but the cell links to the actual submission for that line.

Screenshot 2020-02-22 at 01 02 55 Screenshot 2020-02-22 at 01 03 19

@rubenrivera
Copy link
Author

rubenrivera commented Dec 2, 2020

@AnferLI I suggest you to post a question on https://stackoverflow.com or https://webapps.stackexchange.com.

@rubenrivera
Copy link
Author

rubenrivera commented Dec 2, 2020

@jmendoza4 same advice ^^^

@mustavanov
Copy link

mustavanov commented Jan 9, 2021

@WhiteRabbitObject I faced the same problem as yours , I solved it by using the sharing URL of the form , in the form click SEND and then change permission to everyone with the link and copy that link and paste it instead of the URL given in the script, that's all and it will work

@karahk
Copy link

karahk commented Jun 15, 2021

This script works well however I am wondering if there was a way to hide the "Edit your response" link after form submission as I would only like to provide the Edit Url link for certain responses. The Edit Url link would be provided in a separate email.
image

@arvr50
Copy link

arvr50 commented Aug 25, 2021

I writing the code this way -

image

For testing purpose, when I submitted a new form the response url got generated but it keeps giving me this error -

image

Any workaround for this?

@Baptenda
Copy link

Baptenda commented Aug 20, 2022

I tried the troubleshooting steps above, but the only solution that worked for me was to open the Google Form and go to the menu (3 vertical dots on the right), then add collaborators. I then clicked "Copy editor link". This link grabbed here was pasted and it worked perfectly.

@BeHoSam
Copy link

BeHoSam commented Sep 17, 2022

Hello there !
Is there a way to edit row that was not inserted by Google Form ?
Thanks

@Baptenda
Copy link

Baptenda commented Sep 17, 2022

Hi,

I am not sure I get you well. If the content is in the spreadsheet linked to the Google Forms, then yes, just delete the content. Is there any error?

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