如何从旧Google Sheet自动生成经筛选与列修改的新表格？

需求回顾 #

• 仅展示现任成员（原始表Col1标记为'y'的行）
• 隐藏带括号的隐私手机号（手机号列包含"("则显示为空）
• 将乐器列移至首位
• 按乐器→姓氏→名字排序

修正公式错误 #

问题根源 #

你之前的公式错误在于：

1. 直接在IMPORTRANGE参数里引用原表列（如A:F）是无效的，跨表引用必须通过IMPORTRANGE返回的数据集操作
2. 循环依赖是因为公式试图修改当前单元格所在列，需先完整导入原始数据再处理

方案1：嵌套公式直接实现 #

把IMPORTRANGE的结果作为数据源，嵌套ARRAYFORMULA处理手机号，再用QUERY筛选排序：

=QUERY(
  ARRAYFORMULA(
    {
      IMPORTRANGE("1jzBPxUMkRIhvGAqEJO4O57vx0D_rl_ZO7LgM5R9mhAs", "A:F"),
      IF(REGEXMATCH(IMPORTRANGE("1jzBPxUMkRIhvGAqEJO4O57vx0D_rl_ZO7LgM5R9mhAs", "G:G"), "\("), "", IMPORTRANGE("1jzBPxUMkRIhvGAqEJO4O57vx0D_rl_ZO7LgM5R9mhAs", "G:G")),
      IMPORTRANGE("1jzBPxUMkRIhvGAqEJO4O57vx0D_rl_ZO7LgM5R9mhAs", "H:I")
    }
  ),
  "SELECT Col5, Col2, Col3, Col4, Col6, Col7, Col8, Col9 WHERE Col1='y' ORDER BY Col5, Col6, Col2, Col3",
  1
)

说明：

• 用REGEXMATCH检测手机号列（G列）是否包含括号，是则返回空，否则保留原内容
• 首次使用需授权IMPORTRANGE访问目标表格

方案2：辅助列分步处理（更易维护） #

1. 在空白单元格（如A1）导入完整原始数据：

   =IMPORTRANGE("1jzBPxUMkRIhvGAqEJO4O57vx0D_rl_ZO7LgM5R9mhAs", "A:I")
   

2. 在另一单元格（如K1）写处理公式：

   =QUERY(
     ARRAYFORMULA({A:F, IF(REGEXMATCH(G:G, "\("), "", G:G), H:I}),
     "SELECT Col5, Col2, Col3, Col4, Col6, Col7, Col8, Col9 WHERE Col1='y' ORDER BY Col5, Col6, Col2, Col3",
     1
   )
   

这种方式避免重复调用IMPORTRANGE，性能更优，也方便调试。

宏/Apps Script实现方案 #

如果公式满足不了更复杂的需求（比如自定义网站样式、定时同步），可以用Google Apps Script：

核心脚本逻辑 #

function syncMemberData() {
  // 原始表格ID
  const sourceId = "1jzBPxUMkRIhvGAqEJO4O57vx0D_rl_ZO7LgM5R9mhAs";
  // 目标表格（用于嵌入网站）
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("成员展示表");
  
  // 获取原始数据
  const sourceData = SpreadsheetApp.openById(sourceId).getSheetByName("Sheet1").getDataRange().getValues();
  // 处理数据：筛选现任、过滤隐私手机号、调整列顺序
  const processedData = sourceData.filter(row => row[0] === 'y').map(row => {
    // 处理手机号：G列是第6位索引（从0开始）
    const phone = row[6].includes("(") ? "" : row[6];
    // 调整列顺序：乐器(4)、名字(1)、姓氏(2)、其他列...
    return [row[4], row[1], row[2], row[3], row[5], phone, row[7], row[8]];
  });
  
  // 清空目标表并写入处理后的数据
  targetSheet.clearContents();
  targetSheet.getRange(1, 1, processedData.length, processedData[0].length).setValues(processedData);
}

配置方法：

1. 打开目标表格，点击「扩展程序」→「Apps Script」
2. 粘贴上述代码，修改sourceId和工作表名称
3. 运行脚本授权，设置时间驱动触发器（如每日更新）
4. 将目标表格发布为网页，嵌入到成员网站

网站嵌入方式 #

把处理后的表格发布为可嵌入的网页：

1. 打开目标表格，点击「文件」→「发布到网页」
2. 选择要发布的工作表，设置「自动重新发布更改」
3. 复制嵌入代码，添加到网站页面中

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