使用Google Sheets onChange触发器管控行列操作的相关问题咨询

问题解答 #

1. changeType枚举值、HIDE相关类型及OTHER场景说明 #

• 目前onChange的changeType完整有效值为：INSERT_ROW、INSERT_COLUMN、REMOVE_ROW、REMOVE_COLUMN、INSERT_GRID、REMOVE_GRID、FORMAT、EDIT、OTHER，不存在HIDE_开头的枚举值。
• 隐藏行列、修改冻结设置、重命名工作表、修改工作表排序等未单独枚举的表格结构操作，都会触发OTHER类型。
• 区分OTHER对应的具体事件没有原生支持的字段，只能通过预存快照比对的方式实现：比如提前在脚本属性里存储当前隐藏行列列表、冻结行列数等参数，触发OTHER时读取当前表格的对应值和快照对比，就能判断具体操作类型。

2. 修改「视图>冻结」设置的触发情况 #

修改冻结行/列的操作会触发onChange事件，对应的changeType为OTHER，不会单独分配枚举值。

3. onChange事件对象成员说明 #

• 可安装版onChange的事件对象固定包含以下属性：authMode（授权模式）、changeType（操作类型）、user（操作用户对象）、triggerUid（触发器ID）、source（当前电子表格实例）。
• 注意：onChange事件对象没有e.range属性，只有简单onEdit触发器才有该属性。如果需要获取操作对应的行号列号，可以调用e.source.getActiveRange()获取当前激活的范围，对应增删行列的操作位置。

4. 触发时机与拦截/撤销逻辑 #

onChange是在用户操作完全完成后才触发的，没有操作前拦截的能力。如果检测到违规操作，可以直接调用e.source.undo()方法执行系统撤销，回滚用户的操作，效果和用户手动按Ctrl+Z一致。

5. 鉴权方案合理性说明 #

• 用e.user.getEmail()和自己的邮箱比对的方案是可行的，仅适合表格共享对象为同组织域名下的登录用户的场景；如果允许匿名用户访问，匿名用户的邮箱字段会返回空值，无法通过该方式判断。
• 更稳定的方案是优先用Google Sheet自带的保护范围功能：直接将需要限制操作的行列/工作表设置为仅你可编辑，不需要依赖脚本触发，不会有延迟、权限异常导致的拦截失败问题。

修正后的示例代码 #

// 功能：拦截非本人对指定行列的增删、隐藏、修改冻结等操作
function myFunction(e) {
  if (typeof e !== "object") {
    Logger.log('未获取到事件对象');
    return;
  }
  const activeRange = e.source.getActiveRange();
  let allowOps = false;
  // 匹配需要鉴权的操作类型
  if (
    ((e.changeType === "INSERT_ROW" || e.changeType === "REMOVE_ROW") && activeRange.getRow() < 12)
    || e.changeType === "INSERT_COLUMN" || e.changeType === "REMOVE_COLUMN"
    || e.changeType === "OTHER"
  ) {
    allowOps = testAuth(e);
  }
  // 操作不允许则撤销并提示
  if (!allowOps) {
    e.source.undo();
    SpreadsheetApp.getUi().alert('你没有权限执行该操作');
  }
}

function testAuth(e) {
  const adminEmail = '你的邮箱地址'.toUpperCase();
  const operatorEmail = e.user.getEmail().toUpperCase();
  if (operatorEmail === adminEmail) {
    Logger.log('管理员操作，放行');
    return true;
  } else {
    Logger.log('非管理员操作，拦截');
    return false;
  }
}

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