1

I want to generate an edit url link in the google sheet for a google form which is linked to it.

There are already instructions on how to do it. However they do not work for me.

I have followed the instructions from Ruben and Sphinxx yet I am still unable to get it to work.

There instructions are below in this link

Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script

and

Sphinxx instruction below on gist

https://gist.github.com/rubenrivera/4ed2110cda3fbdbc29d2d2d3a4af29c0

Steps taken

1- I have used both methods from Sphinxx and Ruben with no luck.

3 things I have to change in the code.

  • form link
  • sheet name
  • column name for location of edit url

I have ensured no spelling mistakes. Paying particular attention to capitalisation.

I have used both versions of the links.

edit form id version https://docs.google.com/forms/d/example/edit

view form link https://docs.google.com/forms/d/e/example/viewform

When using the edit form link I get no error messages however nothing happens in the sheet and no link is generated.

When I use the view form link I get an error message.

'No item with the given ID could be found, or you do not have permission to access it. (line 16, file "Code")'

2- I have ensured no spelling mistakes for the sheet name and also the column name where the link should appear

3- I get 0 errors when running the code and 0 errors for the trigger. I created the trigger in the sheet, to generate on form submission.

all of this and it does not work.

Any help would be greatly appreciated.

Current Code for script in google sheets.

using the edit url form id

https://docs.google.com/forms/d/example/edit

// Form
var formId = 'example';
// Sheet
var sheetName = 'Form Responses 1';
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.openById(formId);

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);
    }
  }
}
  • is the sheet yours? (the sheet you want to create edit url for form) – user0 Jun 28 at 14:13
  • yes the sheet and form are mine – Jamil Jun 28 at 20:18
0

Most likely, your code is not getting executed.

Please use the below function in the sheet. It will trigger when a new row is inserted into your sheet from the form. You have to insert your code into it.

function onFormSumbit(e) {
  var item = e.source.getActiveSheet().getRange(e.range.rowStart,4).getValue();
  var email = e.source.getActiveSheet().getRange(e.range.rowStart,17).getValue();

  var subject="New Item Code request"
  var message="A new material code for "+item+" has been requested. \n\nR&D head and Design head are requested to approve it by clicking Google Drive-New Erp Code and approve it by clicking the date column."
  //MailApp.sendEmail(email, subject, message);
  MailApp.sendEmail(email, subject, message, {cc:"xx@xx.com"});
}
  • Thank you for your response. However this does not work, I get the following error message. Also I do not want an email sent out. I just want the link to be put in the column. TypeError: Cannot read property "source" from undefined. (line 31, file "Code") – Jamil Jul 2 at 13:45
  • Sorry, I forgot to mention that you have to execute this function on-form-submit. Use the "trigger" in the scripting page (with clock icon). I had given you a working model which I am using. You can change the code to do anything you want using the variables (like putting the variable in a column etc) – arul selvan Jul 5 at 4:02
  • @arulselvan Please add all the relevant details to the question it self. Comments by the post author are supposed to be used only to share transitional info. – Rubén Jul 30 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.