解决Google Apps Script导出评论仅获取前20条的限制问题

问题原因 #

你的脚本只能获取前20条评论，是因为Drive API v2的Comments.list方法默认采用分页机制，单次请求最多返回20条结果。当文件评论数超过20时，接口会返回nextPageToken，需要通过该token循环请求后续分页的评论数据。

修改后的完整脚本 #

function ExtractComments() {
//Important: For this to work, you must first go to the Services menu on the left and add Drive API v2 (it won't work with other versions).

// Get spreadsheet and sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
  
// Get file Id (user enters the ID in B4 of the active sheet and this grabs it from there)
    var fileID = sheet.getRange(4, 2).getValue();
    Logger.log('File Id is: ' + fileID);

// Get new tab name (user enters the name in B5 of the active sheet and this grabs it from there)
    var tabName = sheet.getRange(5, 2).getValue();
    Logger.log('Tab name is: ' + tabName);

// Create a new sheet and give it the tab name we grabbed above 
    var create = ss.insertSheet(tabName);
    Logger.log('New tab created');

// Get ALL comments (解决分页限制)
    var allComments = [];
    var pageToken = null;
    do {
        var response = Drive.Comments.list(fileID, {pageToken: pageToken});
        if (response.items && response.items.length > 0) {
            allComments = allComments.concat(response.items);
        }
        pageToken = response.nextPageToken;
    } while (pageToken);
    
    Logger.log('Number of comments:'+ allComments.length);

// Define how comment data will appear in columns in new sheet: d = date, c = text in comment, n = author name, h = highlighted text    
    var dList = [], cList = [], nList = []; hList = []; // 2d array

// Execute extraction
    if (allComments && allComments.length > 0) {
        for (var i = 0; i < allComments.length; i++) {
          var comment = allComments[i]; 
          dList.unshift([comment.author ? comment.author.displayName : '']);
          cList.unshift([comment.content]);
          nList.unshift([comment.context ? comment.context.value : '']);  
          hList.unshift([comment.createdDate ? new Date(comment.createdDate).toLocaleString() : '']);
    }
// Put comment data in new sheet
    var newSheet = ss.getSheetByName(tabName);
    if(newSheet !== null) {
      newSheet.getRange("A1:A" + dList.length).setValues(dList);
      newSheet.getRange("B1:B" + cList.length).setValues(cList);
      newSheet.getRange("C1:C" + nList.length).setValues(nList);
      newSheet.getRange("D1:D" + hList.length).setValues(hList);
    } else {
      Logger.log('Sheet "' + tabName + '" not found');
    }
  }
}

关键修改说明 #

• 初始化allComments数组用于存储所有分页的评论数据
• 新增pageToken变量跟踪分页标记，通过do-while循环持续请求下一页数据，直到nextPageToken为空（没有更多评论）
• 将每次请求返回的评论列表合并到allComments中，最终得到完整的评论集合

参考资料 #

1. Google Apps Script创建新标签页的方法
2. 将Google Docs评论导出到Google Sheets并包含高亮文本的解决方案

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