We're migrating a lot of our business logic to scripts behind the scenes, but I'm worried that they'll be much more fragile when columns move.
On Sheet Updates Automagically
For example, If I have a formula on a spreadsheet like this:
=If(A1=5,"Yes","No")
And then I Insert 1 Column Left of A, the formula will be automatically updated like this:
=If(B1=5,"Yes","No")
Apps scripts doesn't update
For example, if I have the formula in the script section:
<!-- language: lang-js -->function myFunction() {
var value = SpreadsheetApp.getActiveSheet().getRange("A1").getValue();
var output = (value == 5) ? 'Yes' : 'No';
Logger.log(output);
}
It will not update when the sheet changes.
Q: How can I get stable references in the code behind for columns that could potentially move?