CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-apigoogle-docsdata-extraction
user12736831
user12736831

Reputation:

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.

Upvotes: 5

Views: 15678

Answers (2)

tatertank
tatertank

Reputation: 23

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

Upvotes: 1

NightEye
NightEye

Reputation: 11214

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:

Upvotes: 6

Related Questions