合并Google Sheets多工作表时如何将工作表名添加到新增列

你当前使用的方法存在问题：自定义函数mySheetName()默认获取的是公式所在工作表的名称，无法识别你引用的L&D1、L&D2等子表的名称，因此无法实现给各子表行填充对应表名的需求。 #

方案1：无需自定义函数，直接调整公式写法 #

适合工作表数量少的场景，直接在拼接数组时给每个子表的区域横向追加表名列即可，示例公式如下：

=QUERY({
  'L&D1'!A2:E, IF(ROW('L&D1'!A2:E), "L&D1", );
  'L&D2'!A2:E, IF(ROW('L&D2'!A2:E), "L&D2", )
}, "SELECT * WHERE Col1 IS NOT NULL", 0)

逻辑说明：

• 大括号内逗号代表横向扩展列，分号代表纵向拼接行
• IF(ROW(子表区域), 子表名, )作用是给该子表所有行的新增列批量填充对应子表名称
• QUERY语句过滤掉首列为空的无效行

方案2：优化自定义函数，支持批量合并多表 #

如果需要合并的工作表数量多，可以修改自定义函数实现自动匹配工作表、追加表名，无需手动逐个拼接子表区域：

1. 打开Apps Script编辑器，替换原有代码为以下内容：

// 自定义函数功能：合并匹配名称规则的工作表区域，自动追加表名列
// 用法示例：=MERGE_SHEETS_WITH_NAME("L&D.*", "A2:E")
// 参数1：工作表名匹配正则，比如"L&D.*"代表匹配所有名称以L&D开头的工作表
// 参数2：每个工作表需要提取的单元格区域
function MERGE_SHEETS_WITH_NAME(sheetNamePattern, rangeStr) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = ss.getSheets();
  const result = [];
  const nameRegex = new RegExp(sheetNamePattern);
  allSheets.forEach(sheet => {
    const sheetName = sheet.getName();
    if (nameRegex.test(sheetName)) {
      const values = sheet.getRange(rangeStr).getValues();
      values.forEach(row => {
        if (row[0] !== "") {
          row.push(sheetName);
          result.push(row);
        }
      })
    }
  })
  return result;
}

2. 保存代码后回到表格，在要放合并结果的单元格输入公式即可：

=MERGE_SHEETS_WITH_NAME("L&D", "A2:E")

使用该公式会自动遍历所有名称包含L&D的工作表，提取指定区域的数据并在最后一列填充对应工作表名称，自动过滤空行。 #

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