0

I have a google drive file with hundreds of comments and im looking to extract them in a google script and print the results (comments.list) to a google sheet

I know step 1 is to create a loop and obtain comments.list, similar to Get more than 100 Google Drive Comments using nextPageToken

After this though, printing to the google sheet is giving me trouble. I can't seem to obtain the entire paginated list and store in an array properly to send to the sheet. I've gotten close, but it either: 1) will only print the last page of comments to the sheet, 2) iterate through each page and overwrite the previous write.

Edit: Seems like I need to store the response page by page into a single, concatenated array, and then iterate through this to render the comments. Any advice for concatenating the response, storing it in single array, etc.

Update w/ answer: Drive API 2 was giving me trouble.. the below is now working

Here's my code

function retrieveComments(fileId) {

var hList = [], cList = [];
var comments = [];
var sheet = SpreadsheetApp.getActiveSheet();
var token = "";
var fileId = '1C4-VtJN-molEb7KEIPw4BUtOC3CGm3oeSSdQ6pDe5XI'; 

  do {
    
    var callArguments = {'maxResults': 100, 'pageToken': token}
    //'fields': 'items(commentId,content,context/value,fileId)'
    let resp = Drive.Comments.list(fileId,callArguments);
    token = resp.nextPageToken;
    for (let i = 0; i < resp.items.length; i++) {
      let item = resp.items[i];
      comments.push([item.content, item.context]);
     // Logger.log(comments)
    }
    if (token == '') {
      Logger.log('That was the last ' + resp.items.length + '. Your Done!')




   //   ss.toast('That was the last ' + resp.items.length + '. Your Done!');
    } else {
      // Logger.log(+ resp.nextPageToken + '. Keep Going!')
        Logger.log('Another ' + resp.items.length + '. Keep Going!')
   //   ss.toast('Another ' + resp.items.length + '. Keep Going!');
    }





  } while (token)

Logger.log (comments);

  Logger.log ('Writing to sheet...');

  

if (comments && comments.length > 0) {
    for (var i = 0; i < comments.length; i++) {
      var comment = comments[i]; 
      // add comment and highlight to array's first element 
      hList.unshift([comment[0]]);
      cList.unshift([comment[1].value]);


      
    sheet.getRange("A1:A" + hList.length).setValues(hList);
    sheet.getRange("B1:B" + cList.length).setValues(cList);

  

  
    }
  }

  // now lets set the headers


  Logger.log ('Now lets add the headers');


function prependRow(sheet, rowData) {

  var rowData = [
   [ "Code", "Data" ]
    ];

  sheet.insertRowBefore(1).getRange(1, 1, 1, rowData.length).setValues([rowData]);
}


  Logger.log ('Done writing to the sheet!');



}
jayburg
  • 53
  • 1
  • 8
  • Your not just getting a list of comments in your variable comments. The response from list is much more complicated than that. And you elected to get five fields so you're going to have parse the response and select the fields that you require from the response object. – Cooper Feb 21 '21 at 15:54
  • Thanks for reply @Cooper . Agreed. Ive got the parsing down, but the response seems to come back page by page - maybe 5 pages . Seems like I need to concatenate this all into one array and then iterate through the data. Any advice? – jayburg Feb 21 '21 at 19:09

1 Answers1

2

Here's something I've done recently

do {
    let resp = Drive.Files.list({ pageToken: token, q: "mimeType='application/vnd.google-apps.script' or mimeType='application/vnd.google-apps.spreadsheet' or mimeType='application/vnd.google-apps.document'" });
    token = resp.nextPageToken;
    for (let i = 0; i < resp.items.length; i++) {
      let item = resp.items[i];
      vA.push([item.id, item.mimeType, item.kind, item.title, getPathFromId(item.id), item.owners.map(function (o) { return o.emailAddress }).join(',')]);
    }
    if (token == '') {
      ss.toast('That was the last ' + resp.items.length + '. Your Done!');
    } else {
      ss.toast('Another ' + resp.items.length + '. Keep Going!');
    }
  } while (token)
Cooper
  • 59,616
  • 6
  • 23
  • 54