如何在Google Sheets中实现跨语言排期的双语内容自动同步

核心绕开「下拉列表无法直接嵌入公式」限制的思路是：把动态翻译逻辑从数据验证规则里剥离，用「实体辅助映射表+动态范围引用+编辑触发器」三层结构实现双向同步，全程不需要在下拉规则里写任何公式。

具体实现步骤 #

• 搭建隐藏翻译映射辅助表
  新建名为Translation_Map的工作表并设置为隐藏，设置4列固定结构：
  • A列：存储所有录入过的英文原始条目
  • B列：写入公式=ARRAYFORMULA(IF(A:A<>"",GOOGLETRANSLATE(A:A,"en","zh-CN"),""))，自动生成对应中文翻译
  • C列：写入公式=UNIQUE(FILTER(A:A,A:A<>""))，自动去重输出所有有效英文条目作为英文区下拉数据源
  • D列：写入公式=ARRAYFORMULA(IF(C:C<>"",VLOOKUP(C:C,A:B,2,FALSE),""))，匹配输出对应去重后的中文条目作为中文区下拉数据源
• 绑定双区域动态数据验证规则
  分别给英文、中文录入区设置下拉规则：
  • 英文区所有录入单元格的数据验证，数据源直接引用Translation_Map!C:C范围，拒绝无效输入
  • 中文区所有录入单元格的数据验证，数据源直接引用Translation_Map!D:D范围，拒绝无效输入
    禁止在数据验证规则里手动输入固定选项值，所有选项全部从辅助表动态读取，选项更新不需要手动调整规则。
• 配置Apps Script双向同步触发器
  打开扩展程序里的Apps Script编辑器，写入onEdit触发函数，实现两个核心逻辑：
  1. 监测到英文录入区单元格内容修改时，先判断新值是否已存在于Translation_Map的A列，不存在则追加到A列末尾触发自动翻译，同时自动把中文区对应位置的单元格值替换为匹配到的翻译结果
  2. 监测到中文录入区单元格内容修改时，反向在Translation_Map的B列匹配对应英文值，同步更新英文区对应位置的单元格内容
     保存脚本后不需要额外部署，简易触发器会在表格被任意用户编辑时自动运行，同步延迟在1秒内。

实操避坑：Google Sheets的数据验证模块无法直接读取动态数组公式的溢出结果，这是下拉嵌公式失效的核心原因。必须把翻译、去重后的结果落地到辅助表的实体单元格范围，再给数据验证引用这个实体范围，才能保证下拉选项随内容实时更新。

可选优化项 #

• 可以在Translation_Map里加一列自定义翻译列，手动修正机翻不准的词条，脚本优先匹配自定义翻译结果，不会被机翻内容覆盖
• 可以给脚本加日志记录逻辑，把所有修改、同步操作记录到单独的日志表，方便回溯排期修改历史
• 不需要给协作者开放脚本或辅助表的编辑权限，表格所有者完成配置后，所有拥有编辑权的协作者都可以正常触发同步逻辑

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