如何用Google Sheets query等函数转换谷歌表单同步的表格数据

以下方案无需编程基础，仅需粘贴函数即可实现原始表单数据到目标格式的自动实时转换，新增的表单提交数据会自动同步到转换结果中，无需手动更新。

前置准备 #

先按你的实际使用场景确认两个工作表的命名：

• 存储谷歌表单自动生成原始数据的工作表，下文统一称为原始数据表
• 存放转换后目标格式数据的独立工作表，下文统一称为结果表

操作步骤 #

1. 打开结果表，点击选中A1单元格
2. 直接粘贴以下函数后按下回车键，即可自动生成全量转换后的结构化数据：

=ARRAYFORMULA(
  QUERY(
    SPLIT(
      FLATTEN(
        '原始数据表'!A2:A&"|"&'原始数据表'!B2:B&"|"&'原始数据表'!C1:1&"|"&'原始数据表'!C2:Z
      ),"|"
    ),
    "SELECT Col2,Col3,Col4 WHERE Col4 IS NOT NULL ORDER BY Col1",1
  )
)

自定义参数调整方法 #

如果你的原始数据表结构和默认规则不一致，按对应要求修改参数即可：

• 如果你需要保留更多固定字段（比如班级、联系方式等），可以在FLATTEN的拼接规则里新增对应列，格式为&"|"&'原始数据表'!对应列号2:对应列号，同时在SELECT后面新增对应的ColX字段（按拼接顺序数字依次递增）
• 如果你要转换的动态字段（比如课程名称、选项）不是从C列开始，把'原始数据表'!C1:1和'原始数据表'!C2:Z里的C改成你实际的起始列号即可
• 如果你的动态字段列数超过Z列，把'原始数据表'!C2:Z里的Z改成更大的列号即可

常见问题排查 #

• 如果返回#REF!报错：说明结果表中有手动输入的内容挡住了函数自动输出的区域，清空报错提示里提到的对应范围的内容即可恢复
• 如果返回空值：检查原始数据表的命名是否和函数里写的完全一致，注意大小写和空格
• 如果数据缺失：检查原始数据表'!C2:Z的范围是否覆盖了所有需要转换的动态字段列

内容的提问来源于stack exchange，提问作者Susy Brommers