Search code examples
google-apps-scriptgoogle-sheetsgoogle-drive-apigoogle-docsdata-extraction

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


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.


Solution

  • 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: