Setting And Getting Cell Comments - Spreadsheet Metadata

Comments can be added to spreadsheet cells both manually and programmatically to document a spreadsheet.  This is especially important for complex template-type spreadsheets where users are expected to add values to certain cells to perform calculations or simulations. Comments are possibly an under-used and under-appreciated aspect of spreadsheet usage.  They are an excellent documentation mechanism in that they can add metadata (data about data) and can be used to provide program input just as easily as actual cell values can.  The functions below demonstrate how to set comments on cells, retrieve comments from cells, and check cells for comments.  All the code below is very straightforward and easily deployed for any spreadsheet. When a comment is added, its presence in a cell is indicated by a small orange triangle in the upper righthand side corner of the commented cell.  The comment itself becomes visible when you move the cursor over the cell.  A nice aspect of cell comments is that they preserve white space so formatting is retained, this is useful when, for example, storing and viewing SQL statements.  It may seem odd, but in a soon to be published posting, I'll show how I use cell comments as a convenient place to store SQL statements. Unlike Excel, there does not appear to be a way in Google spreadsheets to make the comments permanently visible.

Code

// Driver function to add comments to cells, to retrieve the comment text, and check for comments.
// Writes its output to the Logger.
function test_CellCommenting() {
  var targetCellAddress = 'A1';
  var sheetName = 'Sheet1';
  var commentText = 'Adding a comment to a cell at date/time: ' + new Date();
  
  try {
    setCellComment(sheetName, targetCellAddress, commentText);
    Logger.log(getCellComment(sheetName, targetCellAddress));
  } catch(error) {
    Logger.log(error);
  }

  if( isCellCommented(sheetName, targetCellAddress)) {
     Logger.log('Cell is commented');
  }
}
// Given a sheet name and a cell address, this function checks to the cell and returns "true" if it contains a comment, otherwise, it returns "false".
// Calls "getCellComment()" and throws an exception if the arguments count != 2.
// Can be used to check that there are no pre-existing comments before a new comment is added to prevent clobbering of comments.
function isCellCommented(sheetName, cellAddress) {
  if(arguments.length !== 2) {
    throw('Function "isCellCommented" expects two arguments only ' + arguments.length + ' given!');
  }
  var cellComment = getCellComment(sheetName, cellAddress);
  
  if(cellComment.length > 0) {
    return true;
  } else {
    return false;
  }
  
}

// Taking a sheet name, a cell address, and a comment text as input, this function sets the comment for the given cell address on the given sheet name.
// It checks that the numer of given arguments = 3 and throws an exception if this is not true.
// Returns "true" if the comment has been set, otherwise throws an error.
function setCellComment(sheetName, cellAddress, commentText) {
  if(arguments.length !== 3) {
    throw('Function "setCellComment" expects three arguments only ' + arguments.length + ' given!');
  }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ws = ss.getSheetByName(sheetName);
    var cellToComment = ws.getRange(cellAddress);
    var comment = cellToComment.setComment(commentText);
    return true;
  } catch(error) {
    throw('Unable to set comment for sheet: "' + sheetName + '" cell address: "' + cellAddress + '"');
  }
  
}

// Given a sheet name and a cell address, return the comment text for that cell in that sheet.
// Check for two arguments and throw an error if two arguments have not been passed.
function getCellComment(sheetName, cellAddress) {
  if(arguments.length !== 2) {
    throw('Function "getCellComment" expects two arguments only ' + arguments.length + ' given!');
  }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ws = ss.getSheetByName(sheetName);
    var commentedCell = ws.getRange(cellAddress);
    var commentText = commentedCell.getComment();
    return commentText;
  } catch(error) {
    throw('Unable to get comment for sheet: "' + sheetName + '" cell address: "' + cellAddress + '"');
  }
  
}

Code Notes

• The driver function sets a cell comment for a cell and appends a date stamp to it.
• The exception handling is added to check that the correct numbers of arguments are passed to each of the functions and exceptions are thrown if bad arguments are passed to any of these functions, a non-existent sheet name, for example.
• I'll show how cell comments can be a useful mechanism for storing and retrieving SQL statements when discussing Google spreadsheets/JavaScript connection to a cloud MySQL instance.  

Final Point:  Cell comments should always be used for adding metadata to spreadsheets and for documenting spreadsheet templates.