Google Sheets自动化替换城镇名称：匹配用户输入与程序缩写名

Hey there, let's tackle this problem head-on. You're looking to automatically replace full town names in Sheet1 with their abbreviations from Sheet2, and keep this process running smoothly every week as new data comes in. Your previous QUERY approach works but isn't efficient for scaling—so let's break down the best solutions, from no-code formulas to automated scripts.

方案1：VLOOKUP + ARRAYFORMULA 实时动态匹配（无代码首选） #

This is the simplest way to get real-time sync without writing any code, and it automatically handles new rows added to Sheet1.

• 前提准备：确保Sheet2的结构是：A列=完整城镇名（无重复值），B列=对应缩写名。

• 步骤：

  1. 建议新建一个输出工作表（比如Sheet3），避免修改用户输入的原始数据（Sheet1）。
  2. 在Sheet3的A1单元格输入以下公式，它会自动同步Sheet1的所有数据并替换城镇名：

     =ARRAYFORMULA(IF(Sheet1!A2:A="","",{Sheet1!B2:G, IFERROR(VLOOKUP(Sheet1!A2:A, Sheet2!A:B, 2, FALSE), "未匹配到缩写")}))
     

  3. （可选）如果需要保留Sheet1的表头，在Sheet3的A1到G1手动复制Sheet1的表头，把原"城镇名"表头改成"城镇缩写"即可。

• 为什么比你的原QUERY好：

  • 原QUERY需要逐个单元格设置匹配条件，下拉公式才能覆盖所有行；这个公式用ARRAYFORMULA自动应用到整列，新增数据会自动加载。
  • IFERROR处理匹配失败的情况，避免出现#N/A错误，更友好。

方案2：优化版QUERY函数（适合需要筛选的场景） #

If you prefer using QUERY for data filtering alongside abbreviation replacement, you can combine it with VLOOKUP in an array:

=ARRAYFORMULA(QUERY({Sheet1!A:G, VLOOKUP(Sheet1!A:A, Sheet2!A:B, 2, FALSE)}, "select Col2, Col3, Col4, Col5, Col6, Col7, Col8 where Col1 is not null", 0))

• 解释：先把Sheet1的数据和匹配后的缩写列合并成一个临时数组，再用QUERY筛选掉空行，只输出需要的列（Col8是替换后的缩写，替代原Col1的完整城镇名）。
• 适合场景：需要同时筛选Sheet1的数据（比如只保留某类用户输入）时使用。

方案3：Google Apps Script 完全自动化（最高效长期方案） #

If you want to set it and forget it—with weekly automatic runs without any manual work—this is the way to go. It handles large datasets better than formulas and can be extended for extra features like email alerts or CSV exports.

步骤： #

1. 打开你的Google Sheet，点击「扩展程序」→「Apps脚本」。
2. 删除默认代码，粘贴以下脚本：

   function replaceTownNames() {
     const ss = SpreadsheetApp.getActiveSpreadsheet();
     const inputSheet = ss.getSheetByName('Sheet1');
     const lookupSheet = ss.getSheetByName('Sheet2');
     // 要么用已有的Sheet3，要么自动新建
     const outputSheet = ss.getSheetByName('Sheet3') || ss.insertSheet('Sheet3');
     
     // 把对照表转成字典，快速查找
     const lookupData = lookupSheet.getDataRange().getValues();
     const townMap = {};
     lookupData.forEach(row => {
       if (row[0] && row[1]) {
         // 转小写避免大小写匹配错误
         townMap[row[0].toLowerCase()] = row[1];
       }
     });
     
     // 获取Sheet1的所有数据
     const inputData = inputSheet.getDataRange().getValues();
     if (inputData.length <= 1) return; // 没有数据直接退出
     
     // 处理每一行：替换城镇名，保留其他数据
     const outputData = inputData.map((row, index) => {
       if (index === 0) {
         // 处理表头：替换原城镇名列的表头
         return [...row.slice(1), '城镇缩写'];
       }
       const townName = row[0]?.toLowerCase();
       // 匹配不到就保留原名称，避免丢失数据
       const abbreviation = townMap[townName] || row[0];
       // 去掉原城镇名列，换成缩写，保留其他所有列
       return [...row.slice(1), abbreviation];
     });
     
     // 清空输出表并写入处理后的数据
     outputSheet.clear();
     outputSheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
   }
   
   // 设置每周自动触发（比如每周一早上9点运行）
   function createWeeklyTrigger() {
     ScriptApp.newTrigger('replaceTownNames')
       .timeBased()
       .everyWeeks(1)
       .onWeekDay(ScriptApp.WeekDay.MONDAY)
       .atHour(9)
       .create();
   }
   

3. 点击保存，命名脚本为TownNameReplacer。
4. 先手动运行replaceTownNames测试是否正常（第一次运行需要授权，按照提示允许即可）。
5. 运行createWeeklyTrigger设置每周自动任务，以后每周会自动处理新增数据。

• 优势：
  • 完全自动化，不用每周手动操作公式或复制数据。
  • 处理大量数据（几万行）比公式更流畅，不会卡顿。
  • 可以轻松扩展：比如添加代码自动导出结果为CSV，或者发送邮件通知你处理完成。

关键注意事项 #

• 保留原始数据：永远不要直接修改Sheet1的用户输入数据，用Sheet3作为输出表，方便后续核对和回溯。
• 确保对照表唯一：Sheet2的A列（完整城镇名）不要有重复值，否则VLOOKUP和脚本都会返回第一个匹配的结果。
• 大小写兼容：脚本里已经把城镇名转成小写匹配，避免因为大小写差异（比如"Beijing"和"beijing"）导致匹配失败；如果用公式，可以用LOWER()函数处理：=ARRAYFORMULA(IF(A2:A="","",IFERROR(VLOOKUP(LOWER(A2:A),LOWER(Sheet2!A:B),2,FALSE),"未匹配到缩写")))

内容的提问来源于stack exchange，提问作者Mr. Anderson