Google表单脚本中的TypeError

问题描述

在Google表单脚本编辑器中，我使用下面的代码在表单数据中插入电子表格中的编辑响应链接。

  function assignEditUrls（）{
 var form = FormApp.openById（'1bAXKuBKQny9CLU3WOK4xxxxxxxxxxxxxxxxxxxxxxxxxxxx' ）; 
 //在此处输入表单ID 
 
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（'Form Responses'）; 
 
 //根据需要更改工作表名称
 var data = sheet.getDataRange（）。getValues（）; 
 var urlCol = 7; //填充URL的列号; A = 1，B = 2等
 var responses = form.getResponses（）; 
 var timestamps = []，urls = []，resultUrls = []; （var i = 0; i< responses.length; i ++）{
 timestamps.push（responses [i] .getTimestamp（）。setMilliseconds（0））; 
 
 
 urls.push（responses [i] .getEditResponseUrl（））; （数据[j] [0]？url [时间戳.indexOf（数据[J] [0] .setMilliseconds（0））]： '']）; 
} 
 sheet.getRange（2，urlCol，resultUrls.length）.setValues（resultUrls）; 
} 
  

但是当我运行这个脚本时，它显示这样的错误

  TypeError：无法调用null的方法getSheetByName。 （第5行，文件代码）关闭
  

如何解决这个问题？

解决方案

这个脚本通过ID打开一个表单，并希望响应表位于脚本包含的电子表格中。所以你有两个选择： / p>

• 在收集响应的Google Spreadsheet的脚本编辑器中添加代码，而不是在表单编辑器中添加代码。




• 或者，。

    var sheet = SpreadsheetApp.openByID（### SHEET -ID ###）
   .getSheetByName（'Form Responses'）; 
    

  或更好，正如Serge所说：
  
  

    var sheet = SpreadsheetApp.openByID（form.getDestinationId（））
   .getSheetByName（'Form Responses'）; 
    

说明：错误消息指示对象您试图使用的方法 getSheetByName（） on是 null 对象。该对象来自 SpreadsheetApp.getActiveSpreadsheet（）。该方法仅适用于Spreadsheet绑定脚本。

In the Google form script editor, I'm using below code for insert Edit response link in the spreadsheet with form data.

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

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

    //Change the sheet name as appropriate
  var data = sheet.getDataRange().getValues();
  var urlCol = 7; // 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);
}

But when i run this script, it shows error like this

TypeError: Cannot call method "getSheetByName" of null. (line 5, file "Code")Dismiss

How can solve this?

解决方案

This script opens a Form by ID, and expects the response sheet to be in a spreadsheet the script is contained in. So you have two options:

• Add the code inside the Script Editor of the Google Spreadsheet that is collecting the responses and not inside the Form editor.

• Alternatively, open the Spreadsheet by ID.

    var sheet = SpreadsheetApp.openByID(###SHEET-ID###)
                              .getSheetByName('Form Responses');
  

  or better yet, as Serge suggests:

    var sheet = SpreadsheetApp.openByID(form.getDestinationId())
                              .getSheetByName('Form Responses');
  

Explanation: The error message indicates that the object that you attempted method getSheetByName() on was a null object. That object was obtained from SpreadsheetApp.getActiveSpreadsheet(). That method is only applicable within Spreadsheet-bound scripts.

这篇关于Google表单脚本中的TypeError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！