Export Google Docs comments into Google Sheets, along with highlighted text?

Question

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.

Slim

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. – Ecdysis

Are there not any add-ons that do this? Seems like it should be an out-of-the-box feature? – Azzieb

Answer 1

Add Drive API first under services.

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:

Output:

Resources:

• Access Google docs comments from Google app scripts
• Document ID

Ecdysis

Thank you for your help. It really worked. – Slim

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 – Ecdysis

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) – Shayna

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'); – Ouellette

Anyone coming here after Jan 2023- Looks like there is an Add On to do this now. Just search for "comments exporter" – Azzieb

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 – Battled

Answer 2

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');
    }
  }
}

Midterm