在Google Sheets中用App Script自动生成YYXX格式4位唯一序列ID

方案说明 #

以下脚本通过Google Apps Script实现，当手动新增行或表单提交新增行时，自动生成YYXX格式的唯一ID：YY为当前年份后两位，XX为当年递增的序号（不足两位补0）。

1. 手动新增行触发脚本（onEdit） #

适用于手动在工作表中添加行的场景：

1. 打开你的Google Sheets，点击「扩展程序」→「Apps Script」进入脚本编辑器。
2. 清空默认代码，粘贴以下脚本：

function onEdit(e) {
  // 配置项：修改为你的目标工作表名称和ID所在列
  const CONFIG = {
    targetSheet: "Sheet1", // 替换成你的工作表名
    idColumn: 1 // ID所在列，A列=1，B列=2，以此类推
  };

  const sheet = e.source.getActiveSheet();
  // 跳过非目标工作表或已有ID的行
  if (sheet.getName() !== CONFIG.targetSheet) return;
  
  const activeRow = e.range.getRow();
  const idCell = sheet.getRange(activeRow, CONFIG.idColumn);
  if (idCell.getValue() !== "") return;

  // 获取当前年份后两位
  const yearSuffix = new Date().getFullYear().toString().slice(-2);

  // 加锁避免多人编辑时序号重复
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) { // 等待10秒获取锁，超时则放弃
    return;
  }

  // 统计当年已生成的ID数量
  const allIds = sheet.getRange(1, CONFIG.idColumn, sheet.getLastRow())
    .getValues().flat().filter(id => typeof id === 'string');
  const yearIdCount = allIds.filter(id => id.startsWith(yearSuffix)).length;

  // 生成序号并补0
  const sequence = (yearIdCount + 1).toString().padStart(2, '0');
  const uniqueId = yearSuffix + sequence;

  // 写入ID并释放锁
  idCell.setValue(uniqueId);
  lock.releaseLock();
}

3. 修改CONFIG中的工作表名称和ID列，保存脚本（点击保存按钮，命名项目如「GenerateUniqueID」）。

2. 表单提交新增行触发脚本（onFormSubmit） #

如果你的工作表通过Google表单提交新增行，需要使用可安装触发器：

1. 在脚本编辑器中添加以下函数：

function onFormSubmit(e) {
  // 配置项：修改为ID所在列
  const ID_COLUMN = 1; // ID所在列，A列=1，B列=2，以此类推

  const sheet = e.range.getSheet();
  const idCell = sheet.getRange(e.range.getRow(), ID_COLUMN);
  if (idCell.getValue() !== "") return;

  const yearSuffix = new Date().getFullYear().toString().slice(-2);
  const lock = LockService.getScriptLock();
  
  if (!lock.tryLock(10000)) {
    return;
  }

  const allIds = sheet.getRange(1, ID_COLUMN, sheet.getLastRow())
    .getValues().flat().filter(id => typeof id === 'string');
  const yearIdCount = allIds.filter(id => id.startsWith(yearSuffix)).length;

  const sequence = (yearIdCount + 1).toString().padStart(2, '0');
  const uniqueId = yearSuffix + sequence;

  idCell.setValue(uniqueId);
  lock.releaseLock();
}

2. 安装可安装触发器：
   • 点击脚本编辑器左侧的「触发器」图标（时钟形状）。
   • 点击「添加触发器」，配置如下：
     • 选择要运行的函数：onFormSubmit
     • 选择部署类型：「Head」
     • 选择事件源：「表单提交」
     • 选择事件类型：「On form submit」
   • 点击保存，按提示授权脚本权限。

关键注意事项 #

• 确保ID列的单元格初始为空，脚本只会给空单元格生成ID。
• 加锁逻辑避免了多人同时操作时的序号重复问题。
• 如果需要重置当年序号，可手动删除当年所有ID后重新新增行，序号会从01开始。

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