2

My code below gives me the number of notes on a sheet, not comments. I need the number of comments on a sheet. The number of comments per sheet is shown on the worksheet tab, but I have many tabs and I want to get this number programmatically.

There are methods for notes in the class range but there are no non-deprecated methods that deal with comments. People have made this an issue.

function getCommentNumber() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange("A1:A12");
  var results = range.getNotes();
  Logger.log(results);
  var commentNumber = 0;
  for ( i in results) {
    if (results[i] != ""){
    commentNumber += 1;
     }
  }
  Logger.log(commentNumber);
 }

Is there a way to count up the number of comments on a particular worksheet?

Could comments be converted to notes then converted back?

Is there a way to programmatically get the comment number from the data that is shown on the worksheet tab?

pnuts
  • 58,317
  • 11
  • 87
  • 139
GuitarViking
  • 165
  • 2
  • 12

3 Answers3

2

You may want to try by enabling Google Drive API?

function countComments() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var commentsList = Drive.Comments.list(sheet.getId());
  Logger.log(commentsList.items.length);
}

Above code will give you all the comments associated with the spreadsheet. Once you get the list you can process the response to check what comments you are interested in. Google Drive API needs to be enabled from Resources -> Advanced Google services

Srikanth
  • 836
  • 10
  • 20
  • 1
    Is there any way to differentiate the comments returned, to determine which worksheet within the spreadsheet the comment is from? – GuitarViking Nov 08 '15 at 01:36
  • 1
    I tried the Google Drive API approach you suggested. I got the list of comments and the anchor. But I still can't figure out how to tell which worksheet each comment is from. There is data associated with the anchor, like this for example: "range":"973678574". This link discusses how comments in spreadsheets should be anchored https://developers.google.com/drive/web/manage-comments#anchoring_comments. But I can't figure out how to translate the comments anchor and range into the spreadsheet's worksheet. The worksheet tab shows this information, so it must be available. – GuitarViking Nov 08 '15 at 16:02
  • Yes I am also trying the same. I thought there would be some or other info about the anchor documentation but nothing so far. Dint find anything about what's that mystery range number!!! – Srikanth Nov 08 '15 at 22:10
1

Here is a workaround.

When a comment is created an email is sent. So I ask the Gmail APU for some text I know is in comments templated emails. This example takes data from one sheet with master info about all sheets I want to know how much comments they have.

function countComments(){
  var data = SpreadsheetApp.getActiveSheet()
    .getDataRange()  // Get all non-blank cell    
    .getValues()
    .splice(1);// Remove header line 

  //for each row I construct the file name of spreadsheet and then ask Gmail
  for (var i = 0; i < data.length; i++) {
    var id = data[i][0];
    var horario = data[i][3];
    var fileName = horario + "#" + id;
    var query = "category:updates \"" + fileName + "\" -{Marcado como resuelto}";
    var threads = GmailApp.search(query); 
    Logger.log(fileName +": "+ threads.length);

  }
}

I natively speak Spanish so please excuse my English, I don´t know how comment template is written in English.

Peter Hall
  • 53,120
  • 14
  • 139
  • 204
gentilmente
  • 73
  • 6
0

I believe your goal as follows.

  • You want to retrieve the number of comments of the specific sheet in a Google Spreadsheet using Google Apps Script.

Issue and workaround:

Unfortunately, in the current stage, there are no methods for directly retrieving the number of comments from the specific sheet of Google Spreadsheet in Spreadsheet service, Sheets API and Drive API. This has also mentioned at this thread. So in this answer, I would like to propose a workaround for retrieving the number of comments from the specific sheet using Google Apps Script.

In this workaround, I use Microsoft Excel Data converted from Google Spreadsheet. Even when Google Spreadsheet is converted to Microsoft Excel Data, the comments are not removed. This workaround uses this situation. So I would like to propose to retrieve the number of comments from the specific sheet using Excel Data converted from Spreadsheet. The flow of this workaround is as follows.

  1. Convert Google Spreadsheet to Microsoft Excel (XLSX data) using Drive API.
  2. Parse XLSX data using Google Apps Script.
    • When the converted XLSX data is unzipped, the data can be analyzed as the XML data. Fortunately, at Microsoft Docs, the detail specification is published as Open XML. So in this case, XLSX data can be analyzed using XmlService of Google Apps Script.
  3. Retrieve the number of comments from the analyzed XLSX data using the sheet name.

When above flow is converted to the script, I thought that it is a bit complicated. So I created it as a Google Apps Script library (DocsServiceApp (Author is tanaike.)).

Sample script:

Before you use this script, please install DocsServiceApp of the Google Apps Script library.

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId(); // Please set Spreadsheet ID.
  const res = DocsServiceApp.openBySpreadsheetId(spreadsheetId).getSheetByName(sheetName).getComments();
  console.log(res.length);
}
  • In this script, the number of comments of "Sheet1" on the active Spreadsheet is retrieved.
  • When you see the log of console.log(res), you can also confirm the comments including the cell coordinates and authors in the specific sheet.

Note:

  • Please use this script with enabling V8.
  • If an error related to Drive API occurs, please enable Drive API at Advanced Google services. Ref

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165