Google Sheets Script to retrieve comment, highlighted text, and document name for all documents in a folder

Question

I am working on a script for Google Sheets to perform the following function:

1. Look through all documents in a specific folder ('this folder')
2. For each document, look through the comments
3. For each comment, retrieve the name of the document, the highlighted text, and the contents of the comment
4. Append a row to the spreadsheet with the Document Name, highlighted text, and comment contents

I wrote a code that does #2, #3, and #4 (i.e. looking through the comments and collecting the comment data), but this only works for ONE document.

function listComments() {  
 
var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet.appendRow(["Interview", "Highlight", "Code"]);

var docId = '[document title]'; 

  var comments = Drive.Comments.list(docId);

  if (comments.items && comments.items.length > 0) {
    for (var c = 0; c < comments.items.length; c++) {
      var comment = comments.items[c];  

            data = [
                comment.fileTitle,
                comment.context.value,
                comment.htmlContent,
            ]
        sheet.appendRow(data);
    }
  }
}

Below is the code I tried to adapt, to ask the function to look through ALL documents in the folder, and do this for ALL documents. However, it is not working.

I think that it might be getting stuck in a loop somewhere?

function listComments() {      
var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet.appendRow(["Interview", "Highlight", "Code"]);

var folder = DriveApp.getFolderById("[this folder]");
var contents = folder.getFiles();

var file

    if (contents.items > 0) {
    for (var i = 0; i < contents.items.length; i++) {
        file = contents[i];
        
            while (file.hasNext()) {
                var file = contents.next();
                var comments = Drive.Comments.list(file);

                    if (comments.items && comments.items.length > 0) {
                    for (var c = 0; c < comments.items.length; c++) {
                    var comment = comments.items[c];  

                        data = [
                            comment.fileTitle,
                            comment.context.value,
                            comment.htmlContent,
                        ]
                    sheet.appendRow(data);
                    }
                    }                   
            }
    }
    }
}

I appreciate any help or insight you can offer!

Answer 1

This should work:

function listComments() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet.appendRow(["Interview", "Highlight", "Code"]);

  var folder = DriveApp.getFolderById("[this folder]");
  var files = folder.getFiles();

  while (files.hasNext()) {
    var file = files.next();
    var comments = Drive.Comments.list(file.getId());

    if (comments.items && comments.items.length > 0) {
      for (var comment of comments.items) {
        data = [
          comment.fileTitle,
          comment.context.value,
          comment.htmlContent,
        ]
        sheet.appendRow(data);
      }
    }
  }
}

If you have many files and many comments this implementation will work faster:

function listComments_improved() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = [["Interview", "Highlight", "Code"]]; // built a 2d array

  var files = DriveApp
    .getFolderById("[this folder]")
    .getFiles();

  while (files.hasNext()) {
    var comments = Drive.Comments.list(files.next().getId());
    if (!comments.items) continue;
    if (comments.items.length == 0) continue;
    comments.items.forEach(comment => {
      data.push([                                // fill the array with rows
        comment.fileTitle, 
        comment.context.value, 
        comment.htmlContent
        ]);
    });
  }

  sheet.clear()
    .getRange(1,1,data.length,data[0].length)
    .setValues(data);                            // set the array to the sheet
}