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 September 9, 2024 06:46
Show Gist options
  • 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
@atalv
Copy link

atalv commented Apr 11, 2020 •
edited
Loading

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
Loading

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
Loading

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

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

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

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