Google Sheet单元格评论自动导出至新表并删除原评论的技术问询

修正后的完整脚本 #

function exportAndClearComments() {
  // 配置参数：修改为你的目标表名称
  const TARGET_SHEET_NAME = "交易记录导出";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const docId = ss.getId();
  
  // 获取目标表，若不存在则创建
  let targetSheet = ss.getSheetByName(TARGET_SHEET_NAME);
  if (!targetSheet) {
    targetSheet = ss.insertSheet(TARGET_SHEET_NAME);
  }
  
  // 准备存储评论数据的数组，添加表头
  let commentsData = [["单元格位置", "评论内容", "评论作者", "创建时间"]];
  
  // 分页获取所有评论（Drive API 默认每次最多返回100条）
  let pageToken = null;
  do {
    const comments = Drive.Comments.list(docId, {
      maxResults: 100,
      pageToken: pageToken
    });
    
    if (comments.items && comments.items.length > 0) {
      comments.items.forEach(comment => {
        // 提取单元格位置（例如A1）
        const cellLocation = comment.context.value;
        // 提取评论内容
        const content = comment.content;
        // 提取作者邮箱和名称
        const author = `${comment.author.displayName} (${comment.author.emailAddress})`;
        // 提取创建时间并格式化为可读格式
        const createdAt = new Date(comment.createdDate).toLocaleString();
        
        // 添加到数据数组
        commentsData.push([cellLocation, content, author, createdAt]);
      });
    }
    
    pageToken = comments.nextPageToken;
  } while (pageToken);
  
  // 将数据写入目标表：先清空原有内容，再写入新数据
  targetSheet.clear();
  targetSheet.getRange(1, 1, commentsData.length, commentsData[0].length).setValues(commentsData);
  
  // 删除所有已导出的评论
  pageToken = null;
  do {
    const comments = Drive.Comments.list(docId, {
      maxResults: 100,
      pageToken: pageToken
    });
    
    if (comments.items && comments.items.length > 0) {
      comments.items.forEach(comment => {
        Drive.Comments.remove(docId, comment.id);
      });
    }
    
    pageToken = comments.nextPageToken;
  } while (pageToken);
  
  // 提示操作完成
  SpreadsheetApp.getUi().alert(`成功导出并删除 ${commentsData.length - 1} 条评论，数据已存入表 "${TARGET_SHEET_NAME}"`);
}

脚本改进说明 #

• 解决20条评论限制: 通过分页循环（pageToken）批量获取所有评论，每次请求最大100条，覆盖全部数据。
• 定向导出到新表: 指定目标表名称，自动创建不存在的表，避免覆盖原库存数据。
• 补充实用字段: 新增单元格位置、作者、时间戳，为后续VLOOKUP关联库存提供关键依据。
• 自动清理评论: 导出完成后批量删除原单元格评论，释放评论额度。

操作步骤 #

1. 打开你的Google Sheet，点击「扩展程序」→「Apps脚本」。
2. 删除原有代码，粘贴上述脚本。
3. 修改TARGET_SHEET_NAME为你需要的目标表名称（例如"交易记录导出"）。
4. 启用Drive API服务：点击左侧「服务」→「添加服务」→ 找到「Drive API」并添加，确认。
5. 保存脚本（点击顶部保存按钮，命名为exportAndClearComments）。
6. 运行脚本：点击运行按钮，首次运行需完成权限授权，按提示操作即可。

后续VLOOKUP用法示例 #

在库存表中，可使用以下公式合并对应单元格的所有交易评论：

=TEXTJOIN(CHAR(10), TRUE, FILTER('交易记录导出'!B:B, '交易记录导出'!A:A = ADDRESS(ROW(), COLUMN(A:A), 4)))

该公式会将当前单元格的所有评论用换行符拼接显示。

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