如何通过Google Apps Script获取Google Sheets评论对应的工作表信息

核心原理 #

Drive API的Comments.list接口是文件级接口，不支持传入gid过滤单工作表评论，但每条评论返回的anchor字段存储了对应位置的编码信息，其中包含工作表的gid，解码后即可匹配到具体工作表。

实现步骤 #

• 先确保你已经在Apps Script编辑器的「扩展>Apps Script>服务」中添加了Drive API，版本和你当前使用的保持一致即可
• 新增解码anchor提取gid的工具函数
• 拿到gid后遍历对应电子表格的所有工作表，匹配gid获取工作表名称

完整代码 #

原有getMatchingFiles_函数无需修改，新增工具函数和修改后的评论拉取逻辑如下：

// 从评论anchor中提取工作表gid
function extractGidFromAnchor_(anchor) {
  try {
    // anchor为base64编码的JSON字符串，先解码
    const decoded = Utilities.newBlob(Utilities.base64Decode(anchor)).getDataAsString();
    const anchorObj = JSON.parse(decoded);
    // Sheets评论的anchor的range字段格式为"gid!单元格范围"
    if (anchorObj.range) {
      return anchorObj.range.split('!')[0];
    }
    return null;
  } catch (e) {
    // 处理格式异常的历史评论
    return null;
  }
}

function comments() {
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const queries = ["fullText contains 'followup:actionitems'"];
  const matches = queries.map(qs => getMatchingFiles_(qs));

  matches.forEach(queryResult => {
    queryResult.forEach(file => {
      const fileId = file.getId();
      const fileLink = "https://docs.google.com/spreadsheets/d/" + fileId;
      const comments = Drive.Comments.list(fileId);

      if (!comments.items || comments.items.length === 0) return;
      
      // 提前构建当前文件所有工作表的gid和名称映射，避免循环内重复调用提升效率
      let sheetMap = new Map();
      try {
        const ss = SpreadsheetApp.openById(fileId);
        ss.getSheets().forEach(sheet => {
          sheetMap.set(sheet.getSheetId().toString(), sheet.getName());
        });
      } catch (e) {
        // 无权限访问的表格直接跳过
        return;
      }

      comments.items.forEach(comment => {
        if (comment.getStatus() !== "open") return;
        
        const gid = extractGidFromAnchor_(comment.anchor);
        const sheetName = gid ? sheetMap.get(gid) || "未知工作表" : "未知工作表";
        const content = comment.getContent();
        const commentId = comment.getCommentId();

        targetSheet.appendRow([file.getName(), sheetName, fileLink, commentId, content]);
      });
    });
  });
}

注意事项 #

• 解码失败的历史评论会统一标记为「未知工作表」，不影响整体流程运行
• 绑定到整个工作表而非具体单元格的评论，同样可以正常解析出对应gid
• 执行前确认脚本有权限访问你需要拉取评论的所有电子表格

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