Google Sheets通过Apps Script实现勾选复选框自动填用户名

前置准备 #

• 打开你的任务核对表，点击「扩展程序」-「Apps 脚本」打开脚本编辑器
• 在脚本编辑器左侧边栏找到「服务」板块，点击旁边的+号，在弹出的服务列表里找到「Admin Directory API」，点击添加完成服务授权
• 提前确认你的表格结构：固定复选框列为单独一列，姓名填充列放在复选框右侧，记录下两列对应的列号（A列为1，B列为2，以此类推）和工作表名称

实现代码 #

将以下代码粘贴到脚本编辑器里，根据你自己的表格实际情况修改开头CONFIG里的配置参数：

// 自定义配置项，按需修改
const CONFIG = {
  checkboxCol: 2, // 复选框所在列号，示例为B列
  nameCol: 3, // 勾选人姓名要填充的列号，示例为C列
  targetSheetName: "每日任务核对" // 存放核对清单的工作表名称
}

function onEditTrigger(e) {
  const editRange = e.range;
  const activeSheet = editRange.getSheet();
  const colOffset = CONFIG.nameCol - CONFIG.checkboxCol;

  // 过滤非目标范围的编辑操作
  if (activeSheet.getName() !== CONFIG.targetSheetName || editRange.getColumn() !== CONFIG.checkboxCol) {
    return;
  }

  // 取消勾选时清空对应姓名
  if (editRange.getValue() === false) {
    editRange.offset(0, colOffset).clearContent();
    return;
  }

  // 勾选时查询用户信息填充
  try {
    const triggerUser = e.user;
    const userEmail = triggerUser.getEmail();
    let fillText = userEmail;
    // 从Workforce Directory查询用户全名
    if (userEmail) {
      const directoryUser = AdminDirectory.Users.get(userEmail);
      fillText = directoryUser.name.fullName;
    }
    editRange.offset(0, colOffset).setValue(fillText);
  } catch (error) {
    // 接口调用失败时兜底填充
    editRange.offset(0, colOffset).setValue(e.user.getEmail() || "用户信息获取失败");
    console.log(`填充失败，错误信息：${error.message}`);
  }
}

触发器配置 #

代码保存后，需要配置可安装编辑触发器才能正常调用Directory接口（默认简单触发器没有目录读取权限）：

• 点击脚本编辑器左侧边栏的时钟图标进入触发器页面
• 点击右下角「添加触发器」，按以下选项配置：
  • 选择要运行的函数：onEditTrigger
  • 选择事件来源：来自电子表格
  • 选择事件类型：编辑时
• 点击保存，按照弹窗提示完成账号授权即可

注意事项 #

• 仅同Google Workspace域内的账号触发编辑时，才能正常从Workforce Directory拉取用户姓名，外部访客账号受目录权限限制无法读取完整信息
• 如果后续调整了列位置或者工作表名称，只需要修改CONFIG里的对应参数即可，不需要改动核心逻辑
• 触发时如果因为权限问题拿不到用户全名，会自动填充用户邮箱作为兜底，不会出现空白单元格

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