3

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • 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. – NightEye Feb 08 '21 at 15:42
  • Are there not any add-ons that do this? Seems like it should be an out-of-the-box feature? – Nate Dec 09 '22 at 10:41

1 Answers1

6

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:

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • Thank you for your help. It really worked. –  Feb 08 '21 at 15:46
  • 1
    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 – NightEye Feb 08 '21 at 15:48
  • 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) – RVFET May 29 '22 at 13:20
  • 1
    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'); – swpinchen Oct 14 '22 at 13:58
  • Anyone coming here after Jan 2023- Looks like there is an Add On to do this now. Just search for "comments exporter" – Nate Feb 07 '23 at 02:31