时间戳

首页 > 解决方案 > 使用谷歌表格脚本编辑器自动填写谷歌表单时如何修复代码401？

google-apps-script - 使用谷歌表格脚本编辑器自动填写谷歌表单时如何修复代码401？

问题描述

我正在尝试开发一个使用谷歌表格中提供的数据自动填写谷歌表单的程序。

这是我的代码。

function auto_data_entry() {
  var formURL = "(URL of the form would be put here)";
  var workbook = SpreadsheetApp.getActiveSpreadsheet();
  var worksheet = workbook.getSheetByName("Sheet1");

  var full_name = worksheet.getRange("A2").getValue();
  var year = worksheet.getRange("B2").getValue();   
  var month = worksheet.getRange("C2").getValue();
  var day = worksheet.getRange("D2").getValue();
  var period = worksheet.getRange("E2").getValue();

  var datamap = 
  {
  "entry.1901360617": full_name,
  "entry.43103907_year": year,
  "entry.43103907_month": month,
  "entry.43103907_day": day,
  "entry.1047848587": period
  };

  var options = 
  {
    "method": "post",
    "payload": datamap
  };

  UrlFetchApp.fetch(formURL, options); //Line 27

}

然而，它返回...

Exception: Request failed for https://docs.google.com returned code 401.
Truncated server response: <!DOCTYPE html><html lang="en"><head><meta name="description" 
content="Web word processing, presentations and spreadsheets"><meta name="viewport" c... 
(use muteHttpExceptions option to examine full response) (line 27, file "Code")

是我使用学校拥有的谷歌帐户还是我的代码有错误的问题。

我很迷茫，如果有人能提供帮助，我将不胜感激。

标签： google-apps-scriptgoogle-sheetsgoogle-forms

解决方案

无需使用UrlFetchApp，因为您可以使用Class FormResponse和Class ItemResponse。此代码将帮助您解决问题：

function autoDataEntry() {

  // Get the desire form with its questions and create
  // a response to later be submitted 
  var form = FormApp.openById("YOUR-FORM-ID");
  var formResponse = form.createResponse();
  var formQuestions = form.getItems();

  var workbook = SpreadsheetApp.getActiveSpreadsheet();
  var worksheet = workbook.getSheetByName("Sheet1");
  // Get all the needed values in the second row 
  var answers = worksheet.getRange("A2:E2").getValues();

  answers[0].forEach((answer, answerNumber) => {
    // Get the question depending of its type
    var question = getQuestion(formQuestions, answerNumber);
    // Create the response to your question with the value obtained in the sheet
    var formAnswer = question.createResponse(answer);
    // Add the answer to the response
    formResponse.withItemResponse(formAnswer);  
  });
  // submit the form response  
  formResponse.submit();

}

我所做的是获取您想要发送回复的表格和答案所在的表格。然后我遍历这些答案以将它们添加到相应的问题中，这些问题将添加到表单响应中。该过程完成后，您只需要提交表单响应。

编辑

我通过添加以下函数并forEach在我的autoDataEntry函数中调用它来修改我的代码：

// This function will return the question as the requiered type
function getQuestion(formQuestions, answerNumber){
  var questionType = formQuestions[answerNumber].getType();
  switch(questionType){
    case FormApp.ItemType.TEXT:
      return formQuestions[answerNumber].asTextItem();
    case FormApp.ItemType.MULTIPLE_CHOICE:
      return formQuestions[answerNumber].asMultipleChoiceItem();
    case FormApp.ItemType.DATE:
      return formQuestions[answerNumber].asDateItem();
  }
}

这样，只要您在 switch 语句中将其设置为条件，您就会根据情况需要获得正确的问题类型。您可以在Enum ItemType中查看所有类型。

推荐阅读