Pass Comments & Suggested Edits from a Google Document to a Google Sheet

Question

Fellow Stackers. I'm using a Google Apps Script to (a) capture all "Comments" in a Google Document... ...and (b) list them in a column of a Google Sheet...

However, I'm wondering if it's possible to...

(1) Array "Comments" into individual cells down a column in my Sheet rather than into a single column, as I have now. This is the bit of GAS I'm currently using to grab comment contents:

 var comments = JSON.parse(Drive.Comments.list(id));
 var items=comments.items;

 var string = "";
  for(var i in items){
    string+='\n';
    string+=items[i].content;
  }

(2) Order the "Comments" by anchor position in my Google Document—i.e. the comment anchored highest in the doc would appear in the first cell of the Sheet's column.

(3) Also include "Suggested Edits" from my Google Document alongside the comments. Can those be accessed via API yet?

Thanks in advance to anyone who may be able to help!

• Google Document: https://docs.google.com/document/d/1O7zAdkCmxhYihtfJhZ3OGkWfO8UUJ_deoHEYr7rQHW4/edit?usp=sharing
• Google Apps Script: https://script.google.com/macros/d/1MgTtU0cKSS_XghRjAMtjZFQAdsbU9SkD_2zx03KVKb1Vy4iBBp3MI2QW/edit?uiv=2&mid=ACjPJvHY-vp53Ek1wBR4-W3Q1Ur8dSdyN0g6ZI7n3I48-e7EWyq6v9gY82OAeVNlnpQBbY3ICOzi4PCRtp-pjuqAbH3oePLelcIp-YUPs2FNbB7Cl7CC-AvgnoJPcXCnrO8CrIJEI2v8ns8&splash=yes
• Google Sheet: https://docs.google.com/spreadsheets/d/1uAtmAO0we7h3HUAFlDBLXlShSLvHVM_W6OSXPwpX_t8/edit?usp=sharing

Answer 1

(1) Array "Comments" into individual cells down a column in my Sheet rather than into a single column, as I have now.

This bit of code is taking an array of comments, and concatenating them into a single string:

  var string = "";
  for(var i in items){
    string+='\n';
    string+=items[i].content;
  }

To be able to put each comment into a separate cell in a column, you need to change that array into a 2-dimensional array, with each of the original elements in its own "row". Something like:

  var data = [];           // start with an empty array
  for (var i=0; i<items.length; i++) {
    var item = items[i];   // current comment
    // A row is an array of cells
    var row = [item.htmlContent,item.author.displayName,item.createdDate];
    data.push(row);        // Add this row to the data array
  }

This line writes the content of a single cell, albeit using setValues() which can fill a rectangular range:

var targetRange = sheet.getRange(lastRow+1,1,1,1).setValues([[string]]);

With the 2-D array created earlier, you can append to the sheet like so:

var targetRange = sheet.getRange(lastRow+1,1,data.length,data[0].length);
targetRange.setValues(data);

Result:

function driveApiComment(id){

  var comments = JSON.parse(Drive.Comments.list(id));
  var items=comments.items;

  var data = [];           // start with an empty array
  for (var i=0; i<items.length; i++) {
    var item = items[i];   // current comment
    // A row is an array of cells
    var row = [item.htmlContent,item.author.displayName,item.createdDate];
    data.push(row);        // Add this row to the data array
  }

  var sheet = SpreadsheetApp.openById(submissionSSKey).getSheets()[0];
  var lastRow = sheet.getLastRow();
  var targetRange = sheet.getRange(lastRow+1,1,data.length,data[0].length);
  targetRange.setValues(data);

}

(2) Order the "Comments" by anchor position in my Google Document—i.e. the comment anchored highest in the doc would appear in the first cell of the Sheet's column.

You're out of luck (for now, at least). See:

• How to match comments on an image using kix anchor (or not) in Google Docs
• Anchor documentation does not exist?
• Creating anchored comments programmatically in Google Docs

Summary: Google's anchors are not decipherable. (Likely they are a key to a hidden database that includes the actual line & char refs to your document, along with your social security number and mother's maiden name.) You could retrieve them & sort them alphabetically... but that would have no relation to where the comments appear in a document.

(3) Also include "Suggested Edits" from my Google Document alongside the comments. Can those be accessed via API yet?

Nope.