Add Note to cell on Google Sheet edit

The following Google Apps Script is designed to automatically insert a Note into the active cell in a Google Sheet when a user edits the content of it. The purpose is to explore an alternative way to interact with editing a spreadsheet than described in this blog post (which uses OnEdit(e)). In this instance we will be making use of the 'edit' installable trigger which needs creating in addition to the code we write.

The Note that will be added to the cell will include the current date/time and the value of the cell before it is edited. We will then collect this information each time the cell is edited - in effect creating a history of edits to a cell.

Screenshot of cell with Note showing edit history

Once we have access to the spreadsheet we need to get the current cell that has been selected ('activeCell') so we can then 'getValue' for logging in our Note.

var activeCell = sheet.getActiveCell();
var currentCellValue = activeCell.getValue();

Because we want to build up a history of edits to the cell we also need to get the contents of the current cell Note so that we can append them to the new Note.

var currentCellNote = activeCell.getNote();

Finally we put this all together and add some line breaks to help with formatting.

activeCell.setNote('Last modified: ' + new Date() + '\n' + 'Cell value: ' + "'" + currentCellValue + "'" + '\n\n' + currentCellNote);

When creating the trigger use the On edit event type so it fires and runs our function when a user modifies a value in the spreadsheet.

Limitation
I did want the option to capture a users email address for adding to the cell Note and producing a more effective audit history but On edit does not allow access to services that require authorisation.

Add Note to cell on Google Sheet edit.xlsx