Skip to content

Instantly share code, notes, and snippets.

@pierandrea

pierandrea/Apps Script Code - Copy the edit response link from Google Forms entries into a Google Sheet

Last active October 19, 2023 19:25
Show Gist options
  • Star 28 You must be signed in to star a gist
  • Fork 11 You must be signed in to fork a gist
  • Save pierandrea/3fcccada803ca34c9e09 to your computer and use it in GitHub Desktop.
Save pierandrea/3fcccada803ca34c9e09 to your computer and use it in GitHub Desktop.
Download ZIP
Google Apps Script code to add Google Form edit response links to spreadsheet
Raw
How to use it
Copy the code above.
Paste it in the Google Sheet where you want to collect the edit response links.
Change the following parameters:
line 2 - Google Form ID number;
line 5 - Destination Worksheet name;
line 9 - Column number where edit response links should be collected;
Save and run script.
Check that edit response links are collected in the right column in your destination Sheet
@danielwebguy
Copy link

Hi
Great solutions,
Thanks!
Daniel

@Moonage62
Copy link

This is incredible. Thank you.

@zeddock
Copy link

zeddock commented Oct 18, 2017

Like @danielracic, My response sheet is getting long and I would like to only update the links for those rows that had an edit. Is there a way you can help me to do this, so as to limit the amount of waiting and sometimes, misfires of the script?

Thank you for an excellent script!

@franklinbaldo
Copy link

`function shortenUrl(longUrl) {
var url = UrlShortener.Url.insert({
longUrl: longUrl
});
return url.id;
}

function SubmitEditUrlFromForm1ToForm2 () {
// This function gets the timestamp end the edit ID from the form1 last response and sends it as a response to the form2

// Here you identifys your form1 and form2 by ID
var form1 = FormApp.openById('form1ID');
var form2 = FormApp.openById('form2ID');

// Let's get the last response from form 1
var formResponses = form1.getResponses();
var numResponses = formResponses.length;
var lastResponse = formResponses[numResponses - 1];

// now get the Edit Url (long and shor) and the timestam of the last response
var lastResponseEditUrl = lastResponse.getEditResponseUrl();
var lastResponsetimestamp = lastResponse.getTimestamp();
var lastResponseEditUrlShort = shortenUrl(lastResponseEditUrl);

// You may need to ajust the timestamp to your locale format
var timezone = SpreadsheetApp.openById(form1.getDestinationId()).getSpreadsheetTimeZone();
lastResponsetimestamp = Utilities.formatDate(lastResponsetimestamp, timezone, "yyyy.MM.dd HH:mm:ss,SSS")

// let's get the question from form2;
var questions = form2.getItems();

// now create a response for form2 and submit
var FormResponse = form2.createResponse();
FormResponse.withItemResponse( questions[0].asTextItem().createResponse(lastResponsetimestamp) );
FormResponse.withItemResponse( questions[1].asTextItem().createResponse(lastResponseEditUrl) );
FormResponse.withItemResponse( questions[2].asTextItem().createResponse(lastResponseEditUrlShort));
FormResponse.submit();

}

`

@nexeh
Copy link

nexeh commented May 29, 2018

I changed line 19 to the following

resultUrls.push(['=HYPERLINK("' + data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'' + '","Edit")']);

Which results in the same url but creates a hyperlink with "Edit" as the visible text in the sheet which is visually a bit better for me and self documenting. So i thought id share. Feel free to use as well.

@letgals
Copy link

letgals commented Jun 8, 2018 •
edited

nexeh
I tried to change the line, but url is still the same long string as before. Not sure why, because everything seems fine and no any errors.

@2nisi
Copy link

2nisi commented Jul 12, 2018 •
edited

@letgals put this code instead, I had the same problem:
resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);

If anyone gets a similar error message:

TypeError: Cannot find function setMilliseconds in object .
Then you should change
for (var j = 1; j < data.length; j++) { resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']); } sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);

j = 1 -> you have to change the number 1 to the number of the first row that you have a value from the form -z-1. For example, if you have added some more rows on top of the ones used filled by the form, let's say you added one row, then 1 should become 2 (1+1). Also, the number 2 in the last line should get also +1 and thus should become 3.

I have changed my code to this:
var pos1st = 3; // row number where the first response is located; 1 = 1, 2 = 2, etc for (var j = pos1st-1; j < data.length; j++) { resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']); } sheet.getRange(pos1st, urlCol, resultUrls.length).setValues(resultUrls);

@ihtzme
Copy link

ihtzme commented Jul 5, 2019

Good day! This is an excellent script. May I ask how to solve "Exceeded Maximum Execution Time"? Is there a way to run the script again and pick up from where it left off? I appreciate your help.

@joshm21
Copy link

joshm21 commented Jul 6, 2019 •
edited

Here is an alternate code option I wrote to solve this problem: https://gist.github.com/josh-meinders/ae2ac38bb282cb25ea19113462d887e2

@sanguinechris
Copy link

Hi, thanks for sharing. Pls can we get a code to edit the link "submit another response" link in google form? I'm trying to change the url of that link to something else

@AMG-90
Copy link

AMG-90 commented Feb 6, 2020

line 2 - Google Form ID number;

how do i find it any ideas ?

@adamkpl585
Copy link

I have ~100 rows. For the last 5 its working while the others all show up as undefined. PLEASE HELP!

@amkstevens63
Copy link

I am having trouble with setting values. This is the message:
sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);
SyntaxError: Invalid or unexpected token (line 14, file "Code.gs")Dismiss HELP!

@atalv
Copy link

atalv commented Apr 11, 2020 •
edited

line 2 - Google Form ID number;

how do i find it any ideas ?

Google form ID number is the text between https://docs.google.com/forms/d/ and /edit on the extracted from the URL to edit the form:
For example:
https://docs.google.com/forms/d/11-YoZ5meaLll9fz12gxZoVA2_OaTNQlgCA0Wwj6pIuQ/edit

@aditikumar-png
Copy link

TypeError: data[j][0].setMilliseconds is not a function

I am getting this error pllss help

@aditikumar-png
Copy link

TypeError: data[j][0].setMilliseconds is not a function
I am getting this error plss help

@atalv
Copy link

atalv commented Jun 13, 2021

TypeError: data[j][0].setMilliseconds is not a function
I am getting this error plss help

This may help - https://gist.github.com/pierandrea/3fcccada803ca34c9e09#gistcomment-2645353

@amenahmehak
Copy link

amenahmehak commented Dec 8, 2021 •
edited

I am getting this error TypeError: Cannot read property 'getSheetByName' of null

@amenahmehak
Copy link

Can anyone please help

@Fentadroid
Copy link

@2nisi

resultUrls.push(['=HYPERLINK("' + data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'' + '","Edit")']);

Tried to put this in line 19 and I get an #ERROR

I'm trying to find a solution to get an "Edit" hyperlink......

@2nisi
Copy link

2nisi commented Jan 11, 2023

@2nisi

resultUrls.push(['=HYPERLINK("' + data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'' + '","Edit")']);

Tried to put this in line 19 and I get an #ERROR

I'm trying to find a solution to get an "Edit" hyperlink......

Did you follow my instructions? What Error do you get?

@Fentadroid
Copy link

Fentadroid commented Jan 11, 2023 •
edited

@2nisi
Did you follow my instructions? What Error do you get?

If i use in line 19 what @nexeh said, i always get a full link like the original script.

resultUrls.push(['=HYPERLINK("' + data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'' + '","Edit")']);

If i use in line 19 what you wrote

resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);

I get #ERROR in google sheet.
Look at the image above.

Thank you!!!

Screenshot_283

@2nisi
Copy link

2nisi commented Jan 11, 2023

@2nisi
Did you follow my instructions? What Error do you get?

If i use in line 19 what @nexeh said, i always get a full link like the original script.

resultUrls.push(['=HYPERLINK("' + data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'' + '","Edit")']);

If i use in line 19 what you wrote

resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);

I get #ERROR in google sheet. Look at the image above.

Thank you!!!

Screenshot_283

Then use the original script. If you want to check what is wrong with the error, hold your cursor on the cell with the error. It will have some message indicating what the error is.

@Fentadroid
Copy link

@2nisi
Did you follow my instructions? What Error do you get?

If i use in line 19 what @nexeh said, i always get a full link like the original script.
resultUrls.push(['=HYPERLINK("' + data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'' + '","Edit")']);
If i use in line 19 what you wrote
resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);
I get #ERROR in google sheet. Look at the image above.
Thank you!!!
Screenshot_283

Then use the original script. If you want to check what is wrong with the error, hold your cursor on the cell with the error. It will have some message indicating what the error is.

Screenshot_284

Of course, I have no idea of what the problem could be.....

@Fentadroid
Copy link

Fentadroid commented Jan 28, 2023 •
edited

@2nisi
that is my final code, I'm getting the same #ERROR.
Are you really sure you wrote the correct formula?

function assignEditUrls() {
var form = FormApp.openById('Form Key');
//enter form ID here

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Worksheet Name');

//Change the sheet name as appropriate

var data = sheet.getDataRange().getValues();
var urlCol = 40; // column number where URL's should be populated; A = 1, B = 2 etc
var responses = form.getResponses();
var timestamps = [], urls = [], resultUrls = [];

for (var i = 0; i < responses.length; i++) {
timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
urls.push(responses[i].getEditResponseUrl());
}
for (var j = 1; j < data.length; j++) {

resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);

}
sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);
}

Is there anything I can do to underestand why the formula is not correct?
Of Course "Form Key" and "Worksheet Name" are correct.
If I use

resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);

On line 19, I get a working full link.
Thanks, I really don't know what to do to fix that.

@2nisi
Copy link

2nisi commented Feb 3, 2023

@2nisi that is my final code, I'm getting the same #ERROR. Are you really sure you wrote the correct formula?

function assignEditUrls() { var form = FormApp.openById('Form Key'); //enter form ID here

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Worksheet Name');

//Change the sheet name as appropriate

var data = sheet.getDataRange().getValues(); var urlCol = 40; // column number where URL's should be populated; A = 1, B = 2 etc var responses = form.getResponses(); var timestamps = [], urls = [], resultUrls = [];

for (var i = 0; i < responses.length; i++) { timestamps.push(responses[i].getTimestamp().setMilliseconds(0)); urls.push(responses[i].getEditResponseUrl()); } for (var j = 1; j < data.length; j++) {

resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);

} sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls); }

Is there anything I can do to underestand why the formula is not correct? Of Course "Form Key" and "Worksheet Name" are correct. If I use

resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);

On line 19, I get a working full link. Thanks, I really don't know what to do to fix that.

Try to copy the cell with the error message and paste the content to another cell. Do you still receive an error on the new cell? I am using the exact same code and should normally work.

@Fentadroid
Copy link

Fentadroid commented Feb 4, 2023 •
edited

@2nisi
If I copy the cell with the error message and paste the content to another cell, I got the same #ERROR

This is the code:

resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);

Are you really sure i didn't make any syntax error?
On the top, you can see the content of che cell as result of the code....

Screenshot_286

That is really wierd because if in an empty cell I write a simple correct formula like

=HYPERLINK("www.google.com","Edit")

The result is always a #ERROR (Formula parse error), even if everything seems to be correct.

EDIT:
I guess i've found the solution:

https://support.google.com/docs/thread/126991179/hyperlink-function-doesn-t-work?hl=en

I have modified the formula to:

resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '"; "Edit")']);

Now everything works.

@2nisi
Copy link

2nisi commented Feb 6, 2023

@2nisi If I copy the cell with the error message and paste the content to another cell, I got the same #ERROR

This is the code:

resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);

Are you really sure i didn't make any syntax error? On the top, you can see the content of che cell as result of the code....

Screenshot_286

That is really wierd because if in an empty cell I write a simple correct formula like

=HYPERLINK("www.google.com","Edit")

The result is always a #ERROR (Formula parse error), even if everything seems to be correct.

EDIT: I guess i've found the solution:

https://support.google.com/docs/thread/126991179/hyperlink-function-doesn-t-work?hl=en

I have modified the formula to:

resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '"; "Edit")']);

Now everything works.

Ugh!! this stupid convection thing! Didn't even think about this. I'm glad you figure it out.

Best,

@bobbyburns
Copy link

bobbyburns commented Feb 6, 2023 via email

@cmoli018
Copy link

cmoli018 commented May 17, 2023 •
edited

I tried doing this in the spreadsheet where my form responses are being put and I can't get it to populate. When I run it I don't get any error messages (execution started and completed) but it doesn't show up in my spreadsheet. Can someone help me troubleshoot this?

As a site note, what does the sheet name refer to? I've tried it both ways and neither worked but is it the file name (top left) or the tab name (bottom left)?

function assignEditUrls() {
  var form = FormApp.openById('1CTdJNu9_goN5lLbmQeHmeFySUfQLTg-SYp2rGpfCiD8');
    //enter form ID here

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");

  var data = sheet.getDataRange().getValues();
  var urlCol = 59; // column number where URL's should be populated; A = 1, B = 2 etc
  var responses = form.getResponses();
  var timestamps = [], urls = [], resultUrls = [];
  
  for (var i = 0; i < responses.length; i++) {
    timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
    urls.push(responses[i].getEditResponseUrl());
  }
  for (var j = 1; j < data.length; j++) {
    resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
  }
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  
}

My spreadsheet: This should be populating in column BG

image

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