google-apps-script - 尝试在 Google 表单上预先填写复选框答案，但无法正常工作

Question

使用此处提供的解决方案，我正在尝试预先填写并提交 Google 表单。它一直有效，直到遇到复选框问题 - 例如：

表格问题：是否与某人讨论过这个话题？可能的答案：约翰、哈利、爱丽丝

我试图预先填写的答案是“Alice” - 但我收到以下错误消息：

例外：参数（字符串）与 FormApp.CheckboxItem.createResponse 的方法签名不匹配。请参阅下面的代码。任何帮助表示赞赏...

function readSpreadsheet() {
  // Adapted from https://stackoverflow.com/questions/65906603/is-it-possible-to-create-google-form-responses-with-data-from-google-sheets

var formid = 'formidgoeshere';
var ssid = 'ssidgoeshere';
var sheetname = 'answers'

  var sheet = SpreadsheetApp.openById(ssid).getSheetByName(sheetname);
  var range = sheet.getDataRange();
  var numRows = range.getNumRows();
  var values = range.getValues();
  var form = FormApp.openById(formid)


  var items = form.getItems();
  for (var i = 1; i < numRows; i++) {
    var value = values[i];
    Logger.log(value)
    var formResponse = form.createResponse();
    var k = 0;
    for (var j = 0; j < items.length; j++) {
      var item;
      switch (items[j].getType()) {
        case FormApp.ItemType.CHECKBOX:
          item = items[j].asCheckboxItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break;  
        case FormApp.ItemType.LIST:
          item = items[j].asListItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break; 
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item = items[j].asMultipleChoiceItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break; 
        case FormApp.ItemType.PARAGRAPH_TEXT:
          item = items[j].asParagraphTextItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break; 
        case FormApp.ItemType.TEXT:
          item = items[j].asTextItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break;         
        case FormApp.ItemType.CHECKBOX:
          item = items[j].asCheckboxItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break;          
        default:
          Logger.log("#" + (i + 1) + ":Do nothing for item " + j + " of type " + items[j].getType());
          continue;
      } 
      if(j==0){
        Logger.log("This item is the FORM NUMBER");        
      };
    }
    formResponse.submit();
  }
}

标签： google-apps-scriptgoogle-forms

Answer 1

尝试这个：

function dataFromGSheets() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Sheet1");
  const [header, ...data] = sheet.getDataRange().getDisplayValues();
  const choices = {};
  header.forEach((title, index) => {
   choices[title] = data.map(row => row[index]).filter(e => e) });
  return choices;
}

function populateGForms() {
  const Google_Form_ID = gobj.globals.editformid2;
  const googleForm = FormApp.openById(Google_Form_ID);
  const items = googleForm.getItems();
  const choices = getDataFromGoogleSheets();
  items.forEach(function (item) {
    const itemTitle = item.getTitle();
    if (itemTitle in choices) {
      const itemType = item.getType();
      switch (itemType) {
        case FormApp.ItemType.CHECKBOX:
          item.asCheckboxItem().setChoiceValues([...new Set(choices[itemTitle])]);
          break;
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues([...new Set(choices[itemTitle])]);
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item.asMultipleChoiceItem().setChoiceValues([...new Set(choices[itemTitle])]);
          break;
        default:
          Logger.log("Ignore question", itemTitle);
      }
    }
  });
}