Script Google Sheets to get a link to modify sent answers

0

I'm trying to copy this code from another post to be able to create links in a Google Sheets column that edit the responses from the sending of Google Forms forms.

The fact is that the script does not work for me and it gives me the following error and I do not know why.

  

TypeError: The "getRange" method of null can not be called. at getEditResponseUrls (Code: 20)

What should I change? I ask it from the utmost igorancia. Thank you very much

/*
 * Global Variables
 */

// Form URL
var formURL = 'https://docs.google.com/forms/d/form-id/viewform';
// Sheet name used as destination of the form responses
var sheetName = 'Form Responses 1';
/*
 * Name of the column to be used to hold the response edit URLs 
 * It should match exactly the header of the related column, 
 * otherwise it will do nothing.
 */
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.openByUrl(formURL);
  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);
    }
  }
}
    
asked by Jaime 16.12.2018 в 00:22
source

1 answer

0

The following line specifies the name of the sheet to use

var sheetName = 'Form Responses 1';

In the following line the previous value is used to obtain an object of the class "Sheet"

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

But since there is no sheet in your spreadsheet with the name Form Responses 1 , null is assigned to the variable sheet .

You can correct the error in two ways

  • Change Form Responses 1 by the name of the sheet linked to the Google form, or
  • Change the name of the sheet linked to the Google form by Form Responses 1
  • answered by 16.12.2018 / 00:41
    source