如何调整Google Apps Script使生成的Sheet标签与People标签数据同步？

修改思路 #

原脚本通过setValue写入静态数据，导致标签与People表数据完全独立。要实现数据同步，需改用单元格公式引用People表对应单元格，同时确保仅在新建标签时设置公式，避免覆盖已存在标签里的手动录入内容。

修改后的完整脚本 #

function add_sheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var start_sheet = ss.getSheetByName('People');
  var template_sheet = ss.getSheetByName('_Template_');

  // 获取People表A2:C的非空数据，同时保留对应行号
  var allData = start_sheet.getRange('A2:C').getValues();
  var validData = allData.filter(row => row[0]); // 过滤A列为空的无效行

  validData.forEach((row, index) => {
    var name = row[0];
    var rowNumber = index + 2; // 计算对应People表的行号（从A2开始计数）
    var dest_sheet;

    // 尝试获取已存在的同名工作表
    try {
      dest_sheet = ss.getSheetByName(name);
      dest_sheet.getRange('A1'); // 验证工作表可正常访问
    } catch(e) {
      // 工作表不存在时，复制模板并命名
      dest_sheet = template_sheet.copyTo(ss).setName(name);
      // 仅新建工作表时设置关联公式，实现数据同步
      dest_sheet.getRange('B2').setFormula(`=People!A${rowNumber}`);
      dest_sheet.getRange('B3').setFormula(`=People!B${rowNumber}`);
      dest_sheet.getRange('B4').setFormula(`=People!C${rowNumber}`);
    }

    // 已存在的工作表不修改B2-B4单元格，保留手动录入内容
  });
}

关键改动说明 #

• 行号精准对应：通过index + 2计算每个条目在People表中的准确行号，确保公式引用的单元格位置完全匹配
• 避免覆盖手动内容：仅在新建工作表时写入同步公式，已存在的标签不会被修改，手动录入的数据完全保留
• 实时同步生效：公式直接关联People表的对应单元格，当People表中的数据更新时，标签里的公式单元格会自动同步最新值

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