How to edit a spreadsheet with Coldfusion?

Google Apps Script to detect changes to spreadsheet, add timestamp?

• I want to use Google Apps Script to detect when certain cells in my Google Sheet are changed, and write a timestamp in another cell to denote when they were changed. I can't use the NOW() function because it gets re-evaluated every time the sheet is calculated. I need a more permanent timestamp. I found a similar example in this Stack Overflow thread: http://stackoverflow.com/a/11464015. It works great when I edit the spreadsheet, but it doesn't detect when cells are changed via Formula evaluations. How can I detect new formula evaluations and write a timestamp? Below is the code I used that only worked when I manually edited the cell. I think the issue is that it's looking only at the active cell, whereas I want it to look at all cells in a given range: function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet var r = s.getActiveCell(); if( r.getColumn() == 4 ) { //checks the column var nextCell = r.offset(0, 1); if( nextCell.getValue() === '' ) //is empty? nextCell.setValue(new Date()); } } }

• Answer:

  I doubt you're going to be able to hook into a calculation event. That's pretty low level and the number one design goal of Google Apps Script seems to be making it really difficult to write code that hammers on Google's servers. The best you can do is hook an onEdit event on the cells that the formulas depend on (or the cells that depend on those cells, until you get to the actual cells that humans are editing). Or you could also just write a script that runs every few minutes and checks if the cells have changed. I'd recommend you rethink your approach. Google Sheets is getting pretty useful but it's still at the stage where you usually need to settle for 90% solutions.