How to export Google Doc comments to a specific Google Sheet cell

Question

I am working on a collaborative project where a large number of people will be commenting on a single Google Doc. To easily review all the comments, I wanted to find a way to export all the comments to a single Google Sheet file that would display the comment, highlighted text, author, and when the comment was made. I have never used Google Apps Script before, so I found some example code on stack overflow that does exactly what I need.

Here is the original post for reference: Export Google Docs comments into Google Sheets, along with highlighted text?

I needed to update some of the code to fit my needs, and I am left with this code:

Comment Extraction Code

function listComments() {
  // Initialize Variables
  var docId = '1dS9yHUrgsGv6KZhohMhITfgul3jo0MdnM8F6EHgMFnk'; 
  var comments = Drive.Comments.list(docId);
  var hList = [], cList = [], nList = [], dList = [];

  // 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]);
      nList.unshift([comment.author ? comment.author.displayName : '']);
      dList.unshift([comment.createdDate ? new Date(comment.createdDate).toLocaleString() : '']);
    }
    // Update spreadsheet values 
    var sheet = SpreadsheetApp.openById('1JWT0BxJ-MnAzIfP228t9ih3H3ZuswgTIeu98D_j4zPY').getSheetByName('Comments');
    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);
  }
}

This code works as expected, but I have an issue with where the comments are saved on the sheet. This code prints the comments starting from row #1. I need it to start printing the comments on row #2 so that there is space to add titles to the columns in row #1. Does anybody know how to update this code to change the starting row of where the comments are saved to?

I thought I could achieve this by updating "A1:A" on line 19 to "A2:A". This gave me the following error: Exception: The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 2.

I also tried changing "var i = 0" on line 9 to "var i = 1". This gave me the following error: Exception: Range not found

My coding skills are very limited, so I am unsure what else to try.

Any assistance is greatly appreciated!

Answer 1

You need to update your script to shift both top and bottom row numbers so the size of the range matches number of the values:

function listComments() {
  // Initialize Variables
  var docId = '1dS9yHUrgsGv6KZhohMhITfgul3jo0MdnM8F6EHgMFnk'; 
  var comments = Drive.Comments.list(docId);
  var hList = [], cList = [], nList = [], dList = [];

  // 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]);
      nList.unshift([comment.author ? comment.author.displayName : '']);
      dList.unshift([comment.createdDate ? new Date(comment.createdDate).toLocaleString() : '']);
    }
    // Update spreadsheet values 
    var sheet = SpreadsheetApp.openById('1JWT0BxJ-MnAzIfP228t9ih3H3ZuswgTIeu98D_j4zPY').getSheetByName('Comments');
    // Change in these rows:
    sheet.getRange("A2:A" + (hList.length + 1)).setValues(hList);
    sheet.getRange("B2:B" + (cList.length + 1)).setValues(cList);
    sheet.getRange("C2:C" + (nList.length + 1)).setValues(nList);
    sheet.getRange("D2:D" + (dList.length + 1)).setValues(dList);
  }
}

Admittedly, I have not run since I do not have a test sheet, it but it should work.