可以使用Google Sheets中的数据创建Google Form响应吗？ [英] Is it possible to create Google Form responses with data from Google Sheets?

问题描述

我正在尝试将数据从Google电子表格导入到Google表单作为响应。我有大约1000行数据需要作为单独的表单响应提交。所有列对应于不同类型的问题(如多项选择、文本、下拉菜单、日期等)。我在整个互联网和Stackoverflow上都搜索过，但类似问题的答案似乎都不符合我想要做的事情。我发现有几个人在使用form.createResponse()，但我对编程非常陌生，所以可能这就是我无法让他们满足我的需求的原因...

尽管如此，如果有人能帮我编写一些Google Apps脚本代码，使我能够遍历电子表格的每一行，并通过表单将它们作为单独的答复提交，我将非常感激。

需要回答的表单如下所示：

以下是需要作为答复添加的相应标题和数据：

下面的代码是我认为最接近我要查找的代码：

function ApendResponses() {
  var form = FormApp.openByUrl('https://docs.google.com/forms/d/e/1FAIpQLSfVz48wSVwri7cPxWsAHszaF7eqfQi4xdpU2seXiZrgTwaWTA/viewform');
  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1Se_74kAX6zEX71I_kFcKxScnuAslKCPZdE3yHptX-2Q/edit#gid=1047564688");
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var x = 0; x < values.length; x++) {

    var formResponse = form.createResponse();
    var items = form.getItems();

    var row = values[x];

    var formItem = items[0.0].asTextItem;   
    var response = form.createResponse(column[0]);     
    formResponse.withItemResponse(response);

    var formItem = items[1.0].asDateItem();   
    var response = formItem.createResponse(column[1]);     
    formResponse.withItemResponse(response);

    var formItem = items[2.0].asDateItem();   
    var response = formItem.createResponse(column[2]);     
    formResponse.withItemResponse(response);

    var formItem = items[3.0].asDateItem();   
    var response = formItem.createResponse(column[3]);     
    formResponse.withItemResponse(response);

    var formItem = items[4.0].asMultipleChoiceItem();   
    var response = formItem.createResponse(column[4]);     
    formResponse.withItemResponse(response);

    var formItem = items[5.0].asMultipleChoiceItem();   
    var response = formItem.createResponse(column[5]);     
    formResponse.withItemResponse(response);

    var formItem = items[6.0].asTextItem();   
    var response = formItem.createResponse(column[6]);     
    formResponse.withItemResponse(response);

    var formItem = items[7.0].asTextItem();   
    var response = formItem.createResponse(column[7]);     
    formResponse.withItemResponse(response);

    var formItem = items[8.0].asMultipleChoiceItem();   
    var response = formItem.createResponse(column[8]);     
    formResponse.withItemResponse(response);

    var formItem = items[9.0].asTextItem();   
    var response = formItem.createResponse(column[9]);     
    formResponse.withItemResponse(response);

    var formItem = items[10.0].asMultipleChoiceItem();   
    var response = formItem.createResponse(column[10]);     
    formResponse.withItemResponse(response);

    var formItem = items[11.0].asMultipleChoiceItem();   
    var response = formItem.createResponse(column[11]);     
    formResponse.withItemResponse(response);

    formResponse.submit();
    Utilities.sleep(500);

  }

};

但是，此代码不起作用。

推荐答案

终于，我设法让它工作了。如果任何人正在寻找一种方法来使用来自Google电子表格的数据填充现有的Google表单，请使用以下代码：

function readSpreadsheet() {
  var sheet = SpreadsheetApp.openById("[THE ID OF THE SPREADSHEET]");
  var range = sheet.getDataRange();
  var numRows = range.getNumRows();
  var values = range.getValues();
  var form = FormApp.getActiveForm();
  var items = form.getItems();
  for (var i = 1; i < numRows; i++) {
    var value = values[i];
    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();
  }
}

致谢，感谢GitHub的bachmeb撰写this code。

这篇关于可以使用Google Sheets中的数据创建Google Form响应吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！