Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Apps Script code to add Google Form edit response links to spreadsheet
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
@cjoly1811

This comment has been minimized.

Copy link

commented Feb 8, 2016

Greetings:

I am getting the following error message when running the script:
"You do not have permission to call openById (line 394, file "Code")"
This refers to
var form = FormApp.openById('1XfO_F..................................................-kk');
Any ideas?

Thanks.

@karmiphuc

This comment has been minimized.

Copy link

commented Mar 2, 2016

@cjoly1811 You need to authenticate your application first.

@philrincon

This comment has been minimized.

Copy link

commented Apr 16, 2016

Hi Karmiphuc,

I am getting the following error: TypeError: Cannot call method "getDataRange" of null. (line 8, file "Code").
What am I doing wrong? Thanks.

function assignEditUrls() {
var form = FormApp.openById('12KhQSP6h8N4S7IMbwgcNMOsO9rODTqfcLMbj_IBm-ks');

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Rincon Cigar Evaluation Form Responses');

var data = sheet.getDataRange().getValues();
var urlCol = 45;
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);
}

@nseyahi

This comment has been minimized.

Copy link

commented Apr 30, 2016

Try to write the sheet (to worksheet in line 5) name not the workbook name.

@bobbyburns

This comment has been minimized.

Copy link

commented Jun 4, 2016

I am also getting the following error:

TypeError: Cannot call method "getDataRange" of null. (line 8, file "Code")

Any suggestions on why this might be. I don't understand nseyahi's suggestion above.

Thanks!

@debfant

This comment has been minimized.

Copy link

commented Sep 18, 2016

I also got the same error
TypeError: Cannot call method "getDataRange" of null. (line 8, file "Code").

using nseyahi's comment . . .
You need to use the worksheet name (ie name on the worksheet tab) instead of the workbook name in line 5 of the code.

assume it already knows the workbook since the script is for this specific workbook.

Thanks!! Big Help!!!!!

@odark007

This comment has been minimized.

Copy link

commented Sep 23, 2016

I have a few questions please, i am totally new to App script

  1. I am getting this feedback from the compiler: Invalid ID (line 2, file "Code")

i believe the following are also correct?

yourFormKey = 1BYnqtKSRZRdTuhz4aLz_RSz4cUH6qOm4DBl9-AEvgro
yourWorksheetName = i took this from the (Response Sheet tab which i have renamed)

var urlCol = I entered 9 representing column I

see the code below:

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

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet name 2');

//Change the sheet name as appropriate

var data = sheet.getDataRange().getValues();
var urlCol = 9; // 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);
}

@danielracic

This comment has been minimized.

Copy link

commented Sep 28, 2016

I'm running this script on a sheet that's continuing to receive form responses (now around 2000 and climbing). Occasionally it fails to run, and I receive this error:

9/27/16 10:58 AM assignEditUrls Service using too much computer time for one day formSubmit 9/27/16 10:58 AM

I suspect that's because the script is regenerating the URLs for EVERY response, each time it runs, instead of just the latest form submission. Am I correct, and is there a way to modify the script to ask it to only start generating URLs for submissions received after a certain time stamp, or after a certain row?

Thanks!

@oreow2

This comment has been minimized.

Copy link

commented Dec 9, 2016

Is there a way to make script run automatically after every new entry? And only for the new entry?

@goedtkindt

This comment has been minimized.

Copy link

commented Mar 6, 2017

@orew2:
You can specify a trigger that runs after each submit:
while in the editor, go to menu "Resources" ans select "Current Project's triggers"
Click on the line to add a trigger.
Select the script, select "From spreadsheet" and then "On form submit", and save.
You can customise notifications if you wish too.

I have noted that sometimesmy scripts do not always fire, meaning you are left with an empty entry.
For that reason I added a timed trigger (10 minutes) to run the same function and tell my users to revisit reload the sheet after a whiel if they need the link.

It's also possible to deploy an application that rus the same script on demand by visiting a URL.

@zeddock

This comment has been minimized.

Copy link

commented Mar 21, 2017

Thank you SO MUCH for this script!

Simple. Straight forward. And it works for me!

A few comments and a more detailed question:

  1. When you ask for the formID, uninitiated folks should know that means to get the ID from the URL of the EDITable form, not the viewable one.
  2. Other scripts may count on a certain column being something different so it is usually a good idea to pick the column at the end of what currently exists, so it does not throw off other counts.

The comment 15 days ago seems to be an answer to a concern of the script running when it should, however, another posted comment from September 28th, showed a concern that the script was running too much. Wouldn't an additional trigger create more of a problem?

So my question is for you or others, what would be the best way to trigger this great script to be dependable, especially if there is a growing concern about the number of responses coming in?

PS. I have a script that IS triggered every time there is a formsubmit, because I am taking some things from the sheet that the form just received and sticking them into gdocs in my drive. Would it be best for me to simple call this function after every run of my other script?

Thanx!

@vishae

This comment has been minimized.

Copy link

commented Jul 24, 2017

So I've been trying to get this script to work.

Initially I was getting the "Cannot find ID" error (even though I've given it permission), until I read that I was supposed to enter in the edit form ID as opposed to the viewable form ID.

However, after changing that ID, I've tried running it, it seems to all run through just fine, but nothing shows up in any of the tables.

I've tried putting the URLcol both at the start and at the end of the table, but nothing is happening. And without even an error message, I don't know where I went wrong.

Any help?

`function assignEditUrls() {
var form = FormApp.openById('1PJoV0QcW2-cVA5ORa4U9kWXBn39bBiut7QfKFWKh-x4');
//enter form ID here

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Attendance Log');

//Change the sheet name as appropriate

var data = sheet.getDataRange().getValues();
var urlCol = 53; // 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);
}`

@modeswitch

This comment has been minimized.

Copy link

commented Jul 28, 2017

You should be able to do this instead:

var formUrl = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
var form = FormApp.openByUrl(formUrl);
@danielwebguy

This comment has been minimized.

Copy link

commented Aug 6, 2017

Hi
Great solutions,
Thanks!
Daniel

@Moonage62

This comment has been minimized.

Copy link

commented Oct 16, 2017

This is incredible. Thank you.

@zeddock

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

commented Nov 25, 2017

`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

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

commented Jun 8, 2018

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

This comment has been minimized.

Copy link

commented Jul 12, 2018

@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

This comment has been minimized.

Copy link

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.

@josh-meinders

This comment has been minimized.

Copy link

commented Jul 6, 2019

Here is an alternate code option I wrote 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.