I have a script in Google Docs that sets the background color of a row if the Status column contains a certain value. The problem is that the worksheet has certain protected columns, so when a restricted user triggers the script on his section, it runs, but they get an obnoxious permission error message (because the script colors parts of the protected columns). So I need to create 2 scripts that color the row in chunks so when a restricted user triggers individualized status messages, only their unprotected columns color. Here's the code that colors the whole row (triggered by installable onedit trigger):
function setBackLogColor() {
var range = SpreadsheetApp.getActiveSheet().getDataRange();
var statusColumnOffset = getStatusColumnOffset();
for (var i = range.getRow(); i < range.getLastRow(); i++) {
rowRange = range.offset(i, 0, 1);
status = rowRange.offset(0, statusColumnOffset).getValue();
if (status == 'TO LD')
{
rowRange.setBackgroundColor("#cfe2f3");
}
else if (status == 'TO GB' )
{
rowRange.setBackgroundColor("#d9ead3");
}
else if (status == 'TO OUTSIDE PARTY - WILL COME BACK' )
{
rowRange.setBackgroundColor("#f4cccc");
}
else if (status == 'Hand Delivery 2' )
{
rowRange.setBackgroundColor("#d9ead3");
}
else if (status == 'Hand Delivery' )
{
rowRange.setBackgroundColor("#cfe2f3");
}
else
{
rowRange.setBackgroundColor("#FFFFFF");
}
}
}
//Returns the offset value of the column titled "Status"
//(eg, if the 7th column is labeled "Status", this function returns 6)
function getStatusColumnOffset() {
lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);
for (var i = 0; i < range.getLastColumn(); i++) {
if (range.offset(0, i, 1, 1).getValue() == "Status") {
return i;
}
}
}
Any help would be greatly appreciated! Thank you!