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');
To identify the column indexes, which can then be used in subsequent
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