如何用ARRAYFORMULA简化多Google Sheets工作表数据导入公式？

问题说明 #

当前通过手动拼接LET+IMPORTRANGE的方式合并多个工作表数据，但新增工作表时必须手动修改公式，效率低下。尝试用ARRAYFORMULA和MAP函数简化时分别出现「ARRAY_LITERAL中某行缺失值」和「找不到电子表格」「结果应为单行」的错误，需要可自动适配新增工作表的简化公式。

原手动合并公式 #

={
  {"Source","Date","Amount","Business","Category","TransactionID","Account","Status"};
  Let(SheetID,B1,SheetName, A1, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0));
  Let(SheetID,B2,SheetName, A2, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0));
  Let(SheetID,B3,SheetName, A3, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0))
}

尝试过的错误公式 #

1. ARRAYFORMULA版本（报错「ARRAY_LITERAL中某行缺失值」）

={
  {"Source","Date","Amount","Business","Category","TransactionID","Account","Status"};
  ARRAYFORMULA( Let(SheetID,B1:B3,SheetName, A1:A3, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0))
}

2. MAP版本（报错「找不到电子表格」「结果应为单行」）

=MAP(A1:A3,B1:B3,LAMBDA(SheetName,SheetID,query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0)))

可行简化方案 #

使用REDUCE+IMPORTRANGE组合实现自动合并，新增工作表仅需在A/B列添加对应名称和ID即可：

=LET(
  // 定义表头
  headers, {"Source","Date","Amount","Business","Category","TransactionID","Account","Status"},
  // 过滤掉空的工作表名称/ID对
  valid_pairs, FILTER(A1:B, A1:A<>"", B1:B<>""),
  // 逐个合并每个工作表的数据
  merged_result, REDUCE(headers, valid_pairs, LAMBDA(acc, current_pair,
    LET(
      sheet_name, INDEX(current_pair, 1),
      sheet_id, INDEX(current_pair, 2),
      // 导入目标工作表数据
      imported_data, IMPORTRANGE(sheet_id, "___Transactions"),
      // 过滤掉第一列为空的无效行
      filtered_data, FILTER(imported_data, INDEX(imported_data,,1)<>""),
      // 给每行添加Source列（填充当前工作表名称）
      data_with_source, HSTACK(REPT(sheet_name, ROWS(filtered_data)), filtered_data),
      // 将当前工作表数据堆叠到累积结果中
      VSTACK(acc, data_with_source)
    )
  )),
  // 输出最终合并结果
  merged_result
)

关键说明 #

• 权限前置：先单独对每个SheetID执行=IMPORTRANGE(SheetID, "___Transactions")完成授权，避免公式运行时权限错误
• 自动适配：A列存工作表名称，B列存对应SheetID，新增工作表仅需在A/B列追加行，公式自动纳入合并范围
• 错误处理：通过FILTER过滤空的名称/ID对，避免无效数据导入；通过FILTER(imported_data, INDEX(imported_data,,1)<>"")过滤原表空行

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