0

I use this script to get all the spreadsheets of my drive that have comments

    function getMatchingFiles_(query, folderId) {
      folder = folderId ? DriveApp.getFolderById(folderId) : DriveApp;
      const search = folder.searchFiles(query);
      const results = [];
      while (search.hasNext())
        results.push(search.next());
      return results;
    }

Now I want to know the comments I have by spreadsheet this is my code

    function comments() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var Sheet = ss.getSheetByName("Sheet1");
      const queries = ["fullText contains 'followup:actionitems'"];
      const matches = queries.map(function (qs) {
        return getMatchingFiles_(qs);
      });
      matches.forEach(function (queryResult, i) {
        queryResult.forEach(function (file) {
          var link = "https://docs.google.com/spreadsheets/d/" + file.getId();
          var comments = Drive.Comments.list(file.getId());
          if (comments.items && comments.items.length > 0) {
              for (var i = 0; i < comments.items.length; i++) {
              var status = comments.items[i].getStatus();  
                if(status == "open"){
                var name = comments.items[i].getFileTitle();
                var content = comments.items[i].getContent(); 
                var commentDir = comments.items[i].getCommentId();
                BacklogRequestControl_QonsiusSheet.appendRow([file.getName(), link, commentDir, content])
                }
              }
            } 
          }
        });
      });
    }

with this code I get the comments by spreadsheets has but, is there a way to know the sheet where each comment is?, because all of my google spreadsheets have several sheets... Drive.Comments.list(file.getId()) doesn't work when I put the sheet ID, the gid

idfurw
  • 5,727
  • 2
  • 5
  • 18
Ines
  • 1
  • 1
  • According to [this answer](https://stackoverflow.com/a/37692298/16125139), it seems there is no way to get the sheet nor range directly. Although it is deprecated, but you may try to iterate through the sheet to achieve it. – idfurw Sep 28 '21 at 08:26
  • I can iterate the comments by spreadsheet, but, how to know when the comment belongs to a different sheet? – Ines Sep 28 '21 at 12:53

0 Answers0