Google Apps Script code to add Google Form edit response links to spreadsheet

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

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

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('yourWorksheetName');

    //Change the sheet name as appropriate
  var data = sheet.getDataRange().getValues();
  var urlCol = ; // 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);  
}

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

Hi
Great solutions,
Thanks!
Daniel

This is incredible. Thank you.

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!

`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();

}

`

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.

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.

@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);

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.

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

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

line 2 - Google Form ID number;

how do i find it any ideas ?

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

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!

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

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

I am getting this error pllss help

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

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

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

Can anyone please help

@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

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?

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

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

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.

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

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.

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

@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 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.

@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....

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 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....

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,

Please remove me from this thread. I have no relationship to this group. I think you have entered the wrong email.

…

On Mon, Feb 6, 2023, 4:56 AM Eris Sako ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ @2nisi <https://github.com/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.... [image: Screenshot_286] <https://user-images.githubusercontent.com/77392910/216761298-262d3e4a-764f-4e45-b6d9-994d75695b43.jpg> 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, — Reply to this email directly, view it on GitHub <https://gist.github.com/3fcccada803ca34c9e09#gistcomment-4461395> or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEWWEOMBFKJL5XDGCQ3EO33WWDKETBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFQKSXMYLMOVS2I5DSOVS2I3TBNVS3W5DIOJSWCZC7OBQXE5DJMNUXAYLOORPWCY3UNF3GS5DZVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVAZDANJRHE2TMMVHORZGSZ3HMVZKMY3SMVQXIZI> . You are receiving this email because you commented on the thread. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .

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