试图将表格合并到表格中

Question

我正在尝试构建一个应用程序脚本，该脚本将采用一大堆表格并将其答案合并到一个电子表格上。我不需要所有答案，只有是/否问题的答案，只要我知道哪个是Yes，哪个是什么格式/否答案都没有关系。否。

我已经写了下面的书，上半场收集了答案，然后下半年是我尝试将这些答案输入电子表格的尝试。据我所知，我不确定我是否不了解表格的工作原理，或者我是否不了解表的工作方式 - 任何指导都将不胜感激！

const auditReportConsol = () => {
  const folder = DriveApp.getFolderById('1Y2QgLbHncLzPDe-UCD6WizSNnHkjkt4z');

  const forms = folder.getFilesByType(MimeType.GOOGLE_FORMS);

  while (forms.hasNext()) {
    const file = forms.next();
    const form = FormApp.openById(file.getId());
    var formResponses = form.getResponses();
      for (var i = 0; i < formResponses.length; i++) {
      var formResponse = formResponses[i];
      var itemResponses = formResponse.getItemResponses();

     switch (itemResponses[i].getItem().getIndex()) {
        case 7:
         var gov1 = itemResponses[i].getResponse();
          break;
        case 9:
         var gov2 = itemResponses[i].getResponse();
         break;
        case 11:
          var gov3 = itemResponses[i].getResponse();
          break;
        case 13:
          var gov4 = itemResponses[i].getResponse();
          break;
        case 15:
          var risk1 = itemResponses[i].getResponse();
          break;
        case 17:
          var risk2 = itemResponses[i].getResponse();
          break;
        case 19:
          var risk3 = itemResponses[i].getResponse();
          break;
        case 21:
          var risk4 = itemResponses[i].getResponse();
          break;
        case 23:
          var risk5 = itemResponses[i].getResponse();
          break;
       case 25:
          var risk6 = itemResponses[i].getResponse();
          break;
        case 27:
          var breach1 = itemResponses[i].getResponse();
          break; 
        case 29:
          var breach2 = itemResponses[i].getResponse();
          break; 
        case 31:
          var breach3 = itemResponses[i].getResponse();
          break; 
        case 33:
          var asset1 = itemResponses[i].getResponse();
          break; 
        case 35:
          var asset2 = itemResponses[i].getResponse();
          break; 
        case 37:
          var asset3 = itemResponses[i].getResponse();
          break;
        case 39:
          var asset4 = itemResponses[i].getResponse();
          break; 
        case 41:
          var asset5 = itemResponses[i].getResponse();
          break; 
        case 43:
          var dsr1 = itemResponses[i].getResponse();
          break; 
        case 45:
          var dsr2 = itemResponses[i].getResponse();
          break; 
        case 47:
          var dsr3 = itemResponses[i].getResponse();
          break; 
        case 49:
          var dsr4 = itemResponses[i].getResponse();
          break; 
        case 51:
          var dsr5 = itemResponses[i].getResponse();
          break; 
        case 53:
          var dsr6 = itemResponses[i].getResponse();
          break;
        case 55:
          var dsr7 = itemResponses[i].getResponse();
          break;
        case 57:
          var dsr8 = itemResponses[i].getResponse();
          break;
        case 59:
          var dsr9 = itemResponses[i].getResponse();
          break;
        case 61:
          var access1 = itemResponses[i].getResponse();
          break;
        case 63:
          var access2 = itemResponses[i].getResponse();
          break;
        case 65:
          var access3 = itemResponses[i].getResponse();
          break;
        case 67:
          var change1 = itemResponses[i].getResponse();
          break;
        case 69:
          var change2 = itemResponses[i].getResponse();
          break;
        }
        Logger.log;
        Logger.getLog;

      }     
    }
    var ss = SpreadsheetApp.openById('1Qlv4v5dU6caBnRfa4Z58OBnoT0g3uFEmKewTENsBOLo');
    var sheet = ss.getActiveSheet();
    var newForm = sheet.appendRow([gov1],[gov2],[gov3]);

}

Answer 1

您可以简单地列出所需字段，然后仅在最终结果行中将它们推出以显示，例如，

const auditReportConsol = () => {
  const folder = DriveApp.getFolderById('1Y2QgLbHncLzPDe-UCD6WizSNnHkjkt4z');

  const forms = folder.getFilesByType(MimeType.GOOGLE_FORMS);

  const FIELD_LIST = [7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,55,57,59,61,63,65,67,69];

  const rows = [];

  while (forms.hasNext()) {
      const file = forms.next();
      const form = FormApp.openById(file.getId());
      var formResponses = form.getResponses();
      for (var i = 0; i < formResponses.length; i++) {
        var formResponse = formResponses[i];
        var itemResponses = formResponse.getItemResponses();

        var row = [];
        // Pick required items
        itemResponses.map( (index, item) => FIELD_LIST.indexOf(index) >=0 && row.push(item.getResponse()))

      }     
   }
   // Save rows array into sheet
   var ss = SpreadsheetApp.openById('1Qlv4v5dU6caBnRfa4Z58OBnoT0g3uFEmKewTENsBOLo');
   var sheet = ss.getActiveSheet();
   sheet.clear();
   sheet.getRange(1,1, rows.lengths, rows[0].length)

}

我也可能建议将表单/名称作为第一列以区分不同的表单：

row.unshift(form.getTitle())

Answer 2

要使用链接的Google表格中的Google表格进行设置，您需要进行以下

打开每个Google表单和“响应”选项卡，请选择“创建电子表格”按钮。

然后将在保存Google表单的同一目录中创建一个新的Google表。该表将具有一个名为“表单响应”的选项卡1，

然后创建一个单独的Google表格，然后使用查询和Incortrange函数从表单响应中导入数据。

=query({importrange("GoogleSheetsID","Form responses1!A1:Z")}, "Select * WHERE Col1 is not null",1)    

如果GoogleSheetSID替换为您自己的Google Sheet ID，

则需要授予表格许可才能访问链接的文件，

可以更改末尾的选择语句以仅选择所需的列。因此，如果您希望按照该顺序2,8和1列，那就是

=query({importrange("GoogleSheetsID","Form responses 1!A1:Z")}, "Select Col2, Col8, Col1 WHERE Col1 is not null",1)   

如果您想查看多个床单，则可以使用以下公式

=QUERY({importrange("GoogleSheetsID1","Form responses1!A1:Z");importrange("GoogleSheetsID2","Form responses1!A2:Z")}, "Select Col2, Col8, Col1 WHERE Col1 is not null",1)      

在此中添加更多元素，您只需复制

InporTrange（“ GoogleSheetSid2”，“ formes响应1！a1：z”）
{}括号内的元素

Answer 3

以一种形式，您可以从当前的工作表中断开连接，然后连接到另一个工作表。这样，您可以在一个电子表格中收集几种表格的结果！

然后，您可以通过OnFormSubmit触发功能收集所需的信息，并将所需的信息放在一张主表中。

您可以通过这种方式识别信息的起源

function getFormUrl() {
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet =>  console.log (sheet.getFormUrl()));
}