Google Apps Script Spreadsheet Comment Automation

Question

Today I have a question about Google Apps Scripts, specifically for Spreadsheets. I've already looked at the documentation here (yes, documentation on a Sheet within a Spreadsheet), but I haven't been able to find what I'm looking for. Here's the sitch:

1.) When a cell is edited in a Google Spreadsheet, the function I have SETS a NOTE using the function (yes it says cell.setComment(), but in reality it creates a Note. Thanks for being consistent, Google!):

function onEdit() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getActiveSheet();
var cell = sheet.getActiveCell();
cell.setComment("Last modified: + (new Date()));
}

2.) Notes have been 'replaced' by Comments as of... September 5th, 2012? Maybe? I would rather use those instead.

3.) For both types of 'cell notation', there exist functions only to SET the Note/Comment, and not ADD (according to the documentation).

4.) I would like to reference or write a function that has the capability to ADD a new Note/Comment (preferably Comment, they are more easily read), instead of setting the Note/Comment.

5.) You can MANUALLY add a Note/Comment through the GUI in the Spreadsheet itself (right-click a cell and select "Insert Note" or "Insert comment". Because these right-click functions exist leads me to believe that we could write a script to do the same thing, but have it be called auto-magically when the cell has been edited.

6.) This function will be used to keep track of cell revision history. I know that I can create a new spreadsheet and send the revision history to that new spreadsheet easily, but considering that I have 10 spreadsheets that need to be tracked, I would rather not have 10 new spreadsheets to keep track of history. Keeping it in the same spreadsheet will keep things simple.

If anyone could help me out, it would be greatly appreciated!

Answer 1

There is no way to manipulate Comments via Spreadsheet Services - see Issue 36756650. (Star it if you wish.)

Instead, all the "Comments" methods work on Notes.

The following method will append a new "Modified" timestamp to any existing one - not quite as nice looking as an actual Comment would be, unfortunately.

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var cell = sheet.getActiveCell();
  var comments = cell.getComment();
  // Newline works in msgBox, but not in Note.
  comments = comments + "\\nModified: " + (new Date());
  //Browser.msgBox(comments);
  cell.setComment(comments);
}

Answer 2

Updated answer for dic 2018:

Now Google supports the setNote(String) and setNotes(Object[]) method in the Range class.

the getComment() and setComment() methods no longer exist.

Answer 3

Using Google Drive API in Google Apps Script, we could create Comments in a Google Document.
And after a test, I confirm you can also do it in a Spreadsheet (which is normal because Comments depends only on Drive API)

function insertDriveComment(fileId, comment, context) {
  var driveComment = {
    content: comment,
    context: {
      type: 'text/html',
      value: context
    }
  };
  Drive.Comments.insert(driveComment, fileId);  
}

Keep in mind that you cannot attach programmatically the comment to a cell (or to words in Google Document), because anchors for Document and Spreadsheet Comments are proprietary (check the video at the bottom of the page here)

Hope it could help.

Answer 4

Now you can use setNote() and getNote()

var sheet = SpreadsheetApp.getActiveSpreadsheet();
 var cell = sheet.getActiveCell();
 var comments = cell.getNote();
 cell.setNote(comments);