How do I get Google forms edit url to work

Question

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

Answer 1

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:"[email protected]"});
}

Answer 2

I am using this code and it works perfectly. Make sure:

1. Edit form function is turned on

2. get the right form ID (the one you need to use is the URL of when you are creating the form itself NOT VIEW MODE URL)...I also made this mistake and it took me a while to figure out

   var formRes = FormApp.openById('XXXXXXXXX'); 
   var sheetRes = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YYYYYYYY');
   var data = sheetRes.getDataRange().getValues();
   var urlCol = 10;
   var responses = formRes.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))]:'']);
   }
   sheetRes.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);