如何在 Google Sheets 中保留申请的上一状态并通知候补获批用户？

可以用 Google Apps Script 保存每条申请上一次检查时的状态，再与当前状态比较。如果同一申请从“等待资源”变为“已获批”，就发送邮件并更新保存的状态。

每条申请都需要一个稳定且唯一的 ID。不要用行号作为标识，因为排序、插入或删除行都会改变行号。

为什么需要另外保存状态？

如果分配结果由公式计算，单元格通常只保留当前结果，无法直接获取上一次的结果。可以把历史状态保存在专门的状态工作表中，也可以使用 Apps Script 的 PropertiesService。

不能只依赖 onEdit(e)，因为公式重新计算导致的结果变化不会触发它。对于现有的自动分配表格，使用时间驱动触发器定期检查比较容易实现。

示例需要什么表格结构？

假设已有一个名为 Requests 的工作表，第一行为表头，前三列依次为：

WAITING 表示因资源不足而等待，GRANTED 表示已经分配成功。这些是示例值，需要替换为实际表格使用的状态。取消等其他状态也可以保留，但下面的代码只对 WAITING → GRANTED 发送通知。

如果当前表格没有明确的状态列，需要先把现有分配结果整理为可读取的状态。脚本负责检测变化和发送通知，资源分配仍由现有逻辑完成。

保存状态并发送邮件

在关联的 Google Sheets 中打开“扩展程序 → Apps Script”，加入以下代码：

function checkAllocationChanges() {
  const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID';
  const SHEET_NAME = 'Requests';
  const FALLBACK_EMAIL = 'owner@example.com';

  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return;

  try {
    const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
    const sheet = spreadsheet.getSheetByName(SHEET_NAME);
    if (!sheet) throw new Error('Requests sheet was not found.');

    const rows = sheet.getDataRange().getDisplayValues().slice(1);
    const properties = PropertiesService.getScriptProperties();

    for (const row of rows) {
      const id = String(row[0] || '').trim();
      const email = String(row[1] || '').trim();
      const currentStatus = String(row[2] || '').trim();
      if (!id || !currentStatus) continue;

      const key = 'allocation-status:' + id;
      const previousStatus = properties.getProperty(key);

      // First observation establishes a baseline.
      if (previousStatus === null) {
        properties.setProperty(key, currentStatus);
        continue;
      }

      if (previousStatus === 'WAITING' && currentStatus === 'GRANTED') {
        const recipient = email || FALLBACK_EMAIL;

        try {
          MailApp.sendEmail({
            to: recipient,
            subject: 'Your resource request has been granted',
            body: 'Your request ' + id + ' has now been granted.'
          });
        } catch (error) {
          console.error('Notification failed for ' + id, error);
          continue;
        }
      }

      properties.setProperty(key, currentStatus);
    }
  } finally {
    lock.releaseLock();
  }
}

使用前替换 YOUR_SPREADSHEET_ID、工作表名称和备用收件邮箱。申请人邮箱为空时，示例会通知配置的负责人邮箱，不会自动查找表格所有者。

代码使用 ScriptProperties 保存历史状态，适合状态数据量较小的场景。申请 ID 必须唯一，且不能重复使用。如果同一个脚本监控多个表格，还应把表格标识加入状态键，避免不同表格之间的 ID 冲突。

如何运行和验证？

1. 手动运行一次 checkAllocationChanges，完成读取表格和发送邮件所需的授权。这次运行会建立已有申请的状态基线，不发送历史获批通知。
2. 在 Apps Script 的“触发器”中，为该函数添加时间驱动触发器，按需要设置检查频率。
3. 准备一条测试申请，让脚本先读取到它的 WAITING 状态，再通过现有业务流程使它变为 GRANTED，随后检查通知和执行日志。

也可以使用可安装的表单提交触发器，在提交后进行检查。不过，如果分配结果依赖公式、其他工作表或异步更新，不能假定提交处理函数执行时结果已经全部更新。定期检查可以覆盖稍后才出现的状态变化，但通知会有延迟。

哪些情况需要额外处理？

首次看到一条申请时，代码只记录状态。如果它首次被检查时已经是 GRANTED，就不会发送通知。若需要通知所有新获批申请，应另外设计“已通知”记录，并明确哪些历史申请应排除。

定期检查只能比较两次采样结果。如果状态在两次检查之间发生变化又恢复，脚本可能无法发现。需要完整追踪每次分配时，最好在实际执行分配的逻辑中记录事件并触发通知。

邮件发送与状态保存是两个独立操作。示例会在邮件发送失败时保留旧状态，供后续检查重试；但如果邮件已发送、随后状态保存失败，下一次仍可能重复发送。锁可以避免同一脚本的检查任务并发处理，却不能让这两个操作成为一个事务。

上线前还应检查邮箱数据、执行日志，以及账号适用的邮件发送和 PropertiesService 配额。申请数量较大时，可以用独立状态工作表保存历史状态和通知结果，方便查询与维护。

备注：内容仅供参考。