5

Would there be a way to export comments from Google Docs so that the comments show up in a Google Sheets doc in one column and the highlighted text from the Google Doc shows up in the column next to it?

I understand that file comments are accessible through the API:

https://developers.google.com/drive/v3/reference/comments#methods

But can we use it to extract comments and highlighted text of document. Any help would be appreciated.

Improve this question
2
  • 1
    I have edited my answer to place the setting values of sheet inside the if block statement to prevent errors when there are no highlighted text. you might want to check that. Commented Feb 8, 2021 at 15:42
  • Are there not any add-ons that do this? Seems like it should be an out-of-the-box feature? Commented Dec 9, 2022 at 10:41

2 Answers 2

Reset to default
7

Add Drive API first under services.

add api

Then try this:

Code:

function listComments() {
  // Change docId into your document's ID
  // See below on how to
  var docId = '1fzYPRldd16KjsZ6OEtzgBIeGO8q5tDbxaAcqvzrJ8Us'; 
  var comments = Drive.Comments.list(docId);
  var hList = [], cList = [];

  // 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 
      hList.unshift([comment.context.value]);
      cList.unshift([comment.content]);
    }
    // Set values to A and B
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange("A1:A" + hList.length).setValues(hList);
    sheet.getRange("B1:B" + cList.length).setValues(cList);
  }
}

Document:

document

Output:

output

Resources:

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi @Roomi, glad it worked. If your question was solved, please push the accept button. Other people who have the same problem with you can also base your question as a question which can be solved. If you don't find the button, feel free to tell me. stackoverflow.com/help/accepted-answer
I'm soo confused. Where is the spreadsheet shown in your answer. Do I have to run the script and then start highlighting. I ran the code that you provided after granting permissions and etc it ran and immediately finished with no output (as far as I see, there's no spreadsheet on my drive)
Thanks so much for sharing this! SpreadsheetApp.getActiveSheet() was returning null for me. Updating the line to the following code, allowed me to write everything to a sheet I named "Comments": var sheet = SpreadsheetApp.openById(docId).getSheetByName('Comments');
Anyone coming here after Jan 2023- Looks like there is an Add On to do this now. Just search for "comments exporter"
Gettign the following error: GoogleJsonResponseException: API call to drive.comments.list failed with error: The 'fields' parameter is required for this method. listComments @ Code.gs:5
1

Given some changes to various changes I found that the above didn't quite work. I additionally wanted author and timestamp of comment in the export. So please use the below script as it generated my expected output.

function listComments() {
  // Change docId into your document's ID
  var docId = 'docId'; 
  var comments = Drive.Comments.list(docId).items;
  var hList = [], cList = [], nList = [], dList = [];

  // Get list of comments
  if (comments && comments.length > 0) {
    for (var i = 0; i < comments.length; i++) {
      var comment = comments[i]; 
      hList.unshift([comment.context ? comment.context.value : '']);
      cList.unshift([comment.content]);
      nList.unshift([comment.author ? comment.author.displayName : '']);
      dList.unshift([comment.createdDate ? new Date(comment.createdDate).toLocaleString() : '']);
    }
    // Set values to A, B, C, and D and change DocId to the spreadsheet ID and ensure tab in the spreadsheet is named Comments
    var sheet = SpreadsheetApp.openById('DocId').getSheetByName('Comments');
    if(sheet !== null) {
      sheet.getRange("A1:A" + hList.length).setValues(hList);
      sheet.getRange("B1:B" + cList.length).setValues(cList);
      sheet.getRange("C1:C" + nList.length).setValues(nList);
      sheet.getRange("D1:D" + dList.length).setValues(dList);
    } else {
      Logger.log('Sheet "Comments" not found');
    }
  }
}

enter image description here

Improve this answer

2 Comments

This looks like exactly what I need but I get this error when trying to run Drive.Comments.list(docId).items;: GoogleJsonResponseException: API call to drive.comments.list failed with error: The 'fields' parameter is required for this method.
With the current version of the API, a list of wanted fields is needed. To get all the fields, the call to the list method should be: var comments = Drive.Comments.list(docId, { fields: '*' });