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
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.
Nice approach
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.
Hi
Is there any way to capture the range (or ranges) that have been changed? This is mainly for building a simple changelog but I’m sure I’ll find other great uses for it
Based on this, I have tried:
var range = e.source.range;
as well as this
var range = e.range;
But both of these just come back as “undefined” (not error).
Any ideas?
Thanks
I don’t think I have ever returned e.range as a “value” itself. Google says it’s an “object” so I think it returning “undefined” is not an error.
What I have done with range in my scripts is either to get that range/get a specific cell from that range/get a specific column from that range and do something with it, not returning it as a value.
Can you talk about what you want to do with the range as a value? I think this is related to the “log change” thread right?
Indeed.
I’d like to have one sheet which logs all changes, from all sheets.
In the past (pre-Glide) we had two layers of logging: the first is the native “cell history” log from Google. This layer is probably still functional with Glide but it wasn’t convinent anyway.
The second layer was using scripts to add a note to each cell that was changed but this was when we targeted very specific changes so we know “where to look”. Now, we want to capture all changes, all the time, without knowing where they’ll come from.
It would have been great if there was a native feature within Glide but it should be rather doable with Scripts anyway. The issue is that we don’t know which specific columns to look in and also a change can effect multiple cells, sometimes even multiple tables/sheets (one action adding rows and/or setting values within multiple sheets).
Any thoughts about how to capture these changes?