google-sheetsgoogle-apps-scriptgoogle-drive-apigoogle-docs

How to export more than 20 comments on a Google Doc to a Google Sheet


I recently posted a question regarding a different issue with my program. Here is a link to my original post: How to export Google Doc comments to a specific Google Sheet cell

This program exports comments made on a specific Google Doc to a specific Google sheet where they can all be reviewed in one location. The program appears to work great after getting assistance with my previous problem.

However, upon using the program more with a larger number of people, we have discovered that the code is only capable of exporting the 20 most recent comments. Any comments written before the last 20 are not exported to the Google Sheet. This file has considerably more than 20 comments, so this problem is putting my project at a standstill.

The code for my program is provided below:

function onOpen() {
  const ui = DocumentApp.getUi();
  ui.createMenu('Custom Functions')
      .addItem('Export Comments to Google Sheets', 'listComments')
      .addToUi();
}

function listComments() {
  // Initialize Variables
  var docId = '1h-BJrklSFgfGNCzdAWziNx5b5EDRjHHJ3DTkXHNhtUE'; 
  var comments = Drive.Comments.list(docId);
  var sList = [], hList = [], cList = [], nList = [], dList = [];

  // Get list of comments
  if (comments.items && comments.items.length > 0) {
    for (var i = 0; i < comments.items.length; i++) {
      var comment = comments.items[i]; 
      // add comment and highlight to array's first element 
      sList.unshift([comment.status]);
      hList.unshift([comment.context.value]);
      cList.unshift([comment.content]);
      nList.unshift([comment.author ? comment.author.displayName : '']);
      dList.unshift([comment.createdDate ? new Date(comment.createdDate).toLocaleString() : '']);
    }
    // Update spreadsheet values 
    var sheet = SpreadsheetApp.openById('1XNJdSRMB1KR9_gpzbd6_rIC6WYpPoExuWkb12Nd1tRI').getSheetByName('Comments');
    sheet.getRange("A2:A" + (sList.length + 1)).setValues(sList);
    sheet.getRange("B2:B" + (hList.length + 1)).setValues(hList);
    sheet.getRange("C2:C" + (cList.length + 1)).setValues(cList);
    sheet.getRange("D2:D" + (nList.length + 1)).setValues(nList);
    sheet.getRange("E2:E" + (dList.length + 1)).setValues(dList);
  }
}

The bulk of this program was copied from an older post and updated to suit my needs for this project: Export Google Docs comments into Google Sheets, along with highlighted text?

Note: This program requires you to add the "Drive API (V2)" service in order to access your Google Drive files.

I have done some troubleshooting to try to find the problem, but I haven't found what is causing only the last 20 comments to be extracted. I have tried deleting the comments and adding them back, but that did nothing. It doesn't seem to matter who leaves the comments, or who runs the program.

I am not very skilled when it comes to coding, but I cannot find anything with my code that would cause only 20 comments to be exported. I worry that this issue has something to do with Google Docs itself and how it manages comments.

The only thing I can think to try is changing the Drive API from version V2 to V3. Unfortunately, I haven't been able to properly run the code with the V3 version. When running the code from the Apps Script page, it appears to run successfully, but none of the comments are extracted. When running the code from the custom menu button on the Google Doc page itself, I get the following error message: GoogleJsonResponseException: API call to drive.comments.list failed with error: The 'fields' parameter is required for this method.

Any ideas on other troubleshooting methods would be greatly appreciated!


Solution

  • The API returns 20 comments at a time by default. You can increase the amount returned with the maxResults query parameter.

    var comments = Drive.Comments.list(docId, {fields: "*", maxResults: 100})
    

    But, a more robust solution is to use page tokens. Whenever there are more comments to return, the API responds with a nextPageToken attribute.

    {
      ...snip
      "comments": [...],
      "nextPageToken": "MjA="
    }
    

    You get the next page of comments with the pageToken query parameter and nextPageToken value.

    var comments = Drive.Comments.list(docId, {fields: "*", pageToken: "MjA="})
    

    You can also combine both query parameters.

    var comments = Drive.Comments.list(docId, {fields: "*", maxResults: 100, pageToken: "MjA="})
    

    For more perspectives, here are a couple stackoverflow discussions on how to use the nextPageToken: 1 2