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?

This is a general problem when hardcoding strings or numbers in code.

In general the javascript parser can't tell which strings might be used on a sheet function call. Its sometimes not trivial to solve.

Two approaches are:

  1. If the columns/cells/ranges are known beforehand, use named ranges: Define a named range and use NamedRange in code. Use the range to directly write to it or query its row/column position.
  2. Another for column based ranges like yours is that your code does this naming manually by using the column header as the column names. Code uses those names and reads the header to build the mapping.