OnChange trigger

Sure, I’m getting the sheet name via

SheetActiveName = e.source.getActiveSheet().getName()

and then the row index from

ss.getSheetByName(SheetActiveName).sheetOnChange.getActiveRange().getRow()

which returns the correct row number, then I’m reading the whole row with .getValues() into an array.
And the three cells are empty.

Interestingly, when reading e.source.getActiveCell().getColumn() I get a column number which has not been changed by the Glide action!

mmm, that possibly confirms a suspicion I’ve had for a while that that method is not 100% reliable in terms of identifying where the change was made.

I never use getActiveRange() when working with triggers, because I’ve always been worried that it could be subject to concurrency/race conditions and may not always return the correct range.

What I do instead is first identify the sheet using e.source.getActiveSheet(), and then I’ll inspect the values in the rows/columns that I need to in order to determine if any action is necessary.

Just as an aside, I also never work with absolute column numbers, as I find having “magic numbers” in the code will eventually come back and bite you. So I’ll always refer to columns by name, and then use indexOf() to locate columns. So something like:

var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues().shift();

Which returns an array of all the column headers, and then:

var user_id_index = headers.indexOf('UserID');
var role_index = headers.indexOf('Role');
etc...

To identify the column indexes, which can then be used in subsequent getValue() or setValue() calls, eg:

var role = row[role_index];

Although this translates into quite verbose code, I find it to be much safer and more robust, and less prone to those “oops!” moments when you insert a column somewhere and your code breaks :wink:

1 Like

Thank you @Darren_Murphy , the indexOf method is a good idea. I usually used to loop through the array, not so smart I know.
By the way, I do not rely on absolute column numbers. I have a lookup table with named ranges for each script and in there all relevant columns which are used by that script are referenced with a formula giving back their column index. So these formulas change if anything in the sheet changes. And each script reads these column indices. So I even don’t rely on column naming.

But I was not relying on the getActiveCell() only. After the onChange trigger fired, I did read the right row into an array, but the data where not in the array!
And still, sometimes the trigger works like expected. But I don’t have an idea what makes it fail the most times.

1 Like

Nice approach :+1:
I also use named ranges, but usually when it’s just a single cell that contains some sort of global variable. I’ll have to look into your approach, maybe it’s better.

yeah, that is quite weird. I don’t really have any further suggestions, sorry.

This is just a quick info how I do it:
There is a sheet tblScript (see image), each script has a named range (blue) with all column indices which are “dynamic”. if the script starts, I first read the named range for that script and get all the column values into variables. in this example the script works with 4 tables and different cells therein. everything is dynamic except the table names.
here, named ranges are very helpful because then you can add rows above and don’t have to worry.
so with this approach, you can move columns around and rename them as you like, an all scripts remain working.

1 Like

Yes, I solved it I think shortly after this post.

1 Like