Spreadsheet: function to watch row for changes

google sheets

Is there a spreadsheet function that can watch a row and update a cell with the current date when any cell in that row changes? If not for the entire row then perhaps just watch a single cell?

Best Answer

One way of doing it, would be to use the onEdit trigger. Unfortunately, that doesn't work. Besides that, you have to use extensive programming techniques to achieve your goal.

Issue Tracker

See the following feature request on the issue tracker: issue 200; onEdit: access original data.
If you vote, it might get introduced.

Reference

https://stackoverflow.com/a/11067652

Related Solutions

Google-sheets – Is it possible to add conditional formatting when number changes

This is not possible as far as I can tell. Google's conditional formatting is not very robust.

Here is Google's Conditional formatting help, although I don't think it's very helpful in your situation. It doesn't appear you can even use a cell value for the conditional formatting.

EDIT:

I found this link recently. It's much more complicated, but you can do what you want using their Javascript API.

Google-sheets – Monitor webpage changes using Google Spreadsheets

Yes, it's possible to do that with a simple script such as this one. What it does:

Check whether the current value is equal to the most recent "archived" value.
If not, then archive the current value, inserting a row for this purpose.

After entering the script into the Script Editor, you should create a trigger for it: see Resources > This project's triggers. Two options are: running periodically, or running when the data changes. The second one is more natural for this task, but in case onChange fails to work for you (some people had issues in the past), timed trigger is an option.

function recordChanges() {
  var url = 'the URL of your spreadsheet';   // put URL here
  var ss = SpreadsheetApp.openByUrl(url);
  var sh = ss.getSheetByName('Sheet1');  // or whatever name
  var range = sh.getRange("A1:A2");    // assuming your data appears in A1 cell
  var values = range.getValues();
  if (values[0][0] != values[1][0]) {
    sh.insertRowAfter(1);
    sh.getRange("A2").setValue(values[0][0]);
  }
}

This can go on until you hit the limit of 200,000 cells in a spreadsheet... if the spreadsheet has one column, then it can have up to 200,000 rows, so even if another row is added every hour, it will last more than 20 years.