Google Sheets堆叠表单响应时Query数组ARRAY_LITERAL错误求助

我使用Google表单做现场巡检，每份提交最多可录入25个“问题”，提交后数据会记录在表格的单行中。为便于筛选单个问题，需要将每份提交中的问题拆分并堆叠，而非逐行查找。当前使用的函数会重复A:D列数据作为筛选常量，但Query数组会堆叠仅含A:D列的空数据。

当前使用的公式：

={query(Inspection!A2:FJ,"Select A,B,C,D,E,F,G,H,I where E is not null");
query(Inspection!A2:FJ,"Select A,B,C,D,K,L,M,N,O where K is not null");
query(Inspection!A2:FJ,"Select A,B,C,D,Q,R,S,T,U where Q is not null");
query(Inspection!A2:FJ,"Select A,B,C,D,W,X,Y,Z,AA where W is not null");
query(Inspection!A2:FJ,"Select A,B,C,D,AC,AD,AE,AF,AG");
query(Inspection!A2:FJ,"Select A,B,C,D,AI,AJ,AK,AL,AM");
query(Inspection!A2:FJ,"Select A,B,C,D,AO,AP,AQ,AR,AS");
query(Inspection!A2:FJ,"Select A,B,C,D,AU,AV,AW,AX,AY");
query(Inspection!A2:FJ,"Select A,B,C,D,BA,BB,BC,BD,BE");
query(Inspection!A2:FJ,"Select A,B,C,D,BG,BH,BI,BJ,BK");
query(Inspection!A2:FJ,"Select A,B,C,D,BM,BN,BO,BP,BQ");
query(Inspection!A2:FJ,"Select A,B,C,D,BS,BT,BU,BV,BW");
query(Inspection!A2:FJ,"Select A,B,C,D,CE,CF,CG,CH,CI");
query(Inspection!A2:FJ,"Select A,B,C,D,CK,CL,CM,CN,CO");
query(Inspection!A2:FJ,"Select A,B,C,D,CQ,CR,CS,CT,CU");
query(Inspection!A2:FJ,"Select A,B,C,D,CW,CX,CY,CZ,DA");
query(Inspection!A2:FJ,"Select A,B,C,D,DC,DD,DE,DF,DG");
query(Inspection!A2:FJ,"Select A,B,C,D,DI,DJ,DK,DL,DM");
query(Inspection!A2:FJ,"Select A,B,C,D,DO,DP,DQ,DR,DS");
query(Inspection!A2:FJ,"Select A,B,C,D,DU,DV,DW,DX,DY");
query(Inspection!A2:FJ,"Select A,B,C,D,EA,EB,EC,ED,EE");
query(Inspection!A2:FJ,"Select A,B,C,D,EG,EH,EI,EJ,EK");
query(Inspection!A2:FJ,"Select A,B,C,D,EM,EN,EO,EP,EQ");
query(Inspection!A2:FJ,"Select A,B,C,D,ES,ET,EU,EV,EW");
query(Inspection!A2:FJ,"Select A,B,C,D,EY,EZ,FA,FB,FC")
}

当给后面的Query添加where COL is not null条件时，执行到第4个Query时出现错误：In ARRAY_LITERAL, an Array Literal was missing values for one or more rows。

错误原因排查 #

该错误的核心原因是：当某个Query的where条件筛选后无匹配数据时，Query默认会返回一个包含“无匹配记录”提示的单列单元格，而非与其他Query一致的9列空数组。数组拼接时要求所有子数组的列数完全相同，列数不一致就会触发此错误。

解决方案 #

方案1：改进现有Query公式 #

给每个Query添加,0参数（指定无表头），确保无匹配数据时返回空数组（0行9列），同时给所有Query添加where条件过滤空问题行：

={
query(Inspection!A2:FJ,"Select A,B,C,D,E,F,G,H,I where E is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,K,L,M,N,O where K is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,Q,R,S,T,U where Q is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,W,X,Y,Z,AA where W is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,AC,AD,AE,AF,AG where AC is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,AI,AJ,AK,AL,AM where AI is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,AO,AP,AQ,AR,AS where AO is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,AU,AV,AW,AX,AY where AU is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,BA,BB,BC,BD,BE where BA is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,BG,BH,BI,BJ,BK where BG is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,BM,BN,BO,BP,BQ where BM is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,BS,BT,BU,BV,BW where BS is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,CE,CF,CG,CH,CI where CE is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,CK,CL,CM,CN,CO where CK is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,CQ,CR,CS,CT,CU where CQ is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,CW,CX,CY,CZ,DA where CW is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,DC,DD,DE,DF,DG where DC is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,DI,DJ,DK,DL,DM where DI is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,DO,DP,DQ,DR,DS where DO is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,DU,DV,DW,DX,DY where DU is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,EA,EB,EC,ED,EE where EA is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,EG,EH,EI,EJ,EK where EG is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,EM,EN,EO,EP,EQ where EM is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,ES,ET,EU,EV,EW where ES is not null",0);
query(Inspection!A2:FJ,"Select A,B,C,D,EY,EZ,FA,FB,FC where EY is not null",0)
}

方案2：Google Apps Script脚本（更高效） #

手动写25个Query不够灵活，用脚本可自动遍历所有问题组，适合后续扩展：

function splitInspectionData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("Inspection");
  const targetSheet = ss.getSheetByName("SplitData"); // 替换为你的目标工作表名称
  
  // 获取源数据（跳过表头）
  const sourceData = sourceSheet.getRange(2, 1, sourceSheet.getLastRow()-1, sourceSheet.getLastColumn()).getValues();
  const result = [];
  
  // 遍历每行巡检数据
  sourceData.forEach(row => {
    const fixedCols = row.slice(0, 4); // 提取A:D列固定数据
    let colIndex = 4; // 从第5列（E列）开始处理问题组
    
    // 遍历25个问题组（每组5列，组间空1列）
    for (let i = 0; i < 25; i++) {
      const problemGroup = row.slice(colIndex, colIndex+5);
      // 问题组第一列非空则保留
      if (problemGroup[0] !== "") {
        result.push([...fixedCols, ...problemGroup]);
      }
      colIndex += 6; // 跳过组间空列（如E-I后跳J列到K-O）
    }
  });
  
  // 写入目标表
  targetSheet.clearContents();
  if (result.length > 0) {
    targetSheet.getRange(1, 1, result.length, result[0].length).setValues(result);
  }
  
  // 添加表头（按需启用）
  const headers = sourceSheet.getRange(1, 1, 1, 4).getValues()[0].concat(["问题项1","问题项2","问题项3","问题项4","问题项5"]);
  targetSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
}

使用说明：

1. 打开Google Sheets，点击「扩展程序」→「Apps脚本」进入编辑器
2. 粘贴代码，修改targetSheet的名称为你预先创建的工作表
3. 保存并运行，首次运行需完成授权
4. 可设置表单提交触发器，实现数据实时拆分

内容的提问来源于stack exchange，提问作者J.Raab