Resolution on setting background color in google spreadsheet

Question

I have a script in Google Docs that sets the background color of a row if the Status column contains a specific value. The problem is that the worksheet has certain protected columns, so when a limited user runs a script in its section, it starts, but they receive an error message of unwanted permission (because the script colors parts of the protected columns). Therefore, I need to create 2 scripts that color the string in pieces, therefore, when a user with limited access runs individualized status messages, only the color of their insecure columns. Here's the code that colors the entire line (triggered by the onedit built-in 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! Thanks!

Answer 1

This would be a good Try..Catch block application.

 var statusColor = "#FFFFFF"; // default value switch (status) { case 'TO LD': case 'Hand Delivery': statusColor = "#cfe2f3"; break; case 'TO GB': case 'Hand Delivery 2': statusColor = "#d9ead3"; break; case 'TO OUTSIDE PARTY - WILL COME BACK': statusColor = "#f4cccc"; break; default: // do nothing, default already set break; } try { // try coloring whole range // If non-privileged user, this will throw an exception. rowRange.setBackgroundColor(statusColor); } catch (err) { // You could validate that the error is indeed a permission error. // Let assume that the case, and color only the unprotected columns. var unprotRowRange = someSubsetOf(rowRange); // <<<<< Adjust as needed. unprotRowRange.setBackgroundColor(statusColor); }