Permission issue when setting background color in

2019-06-13 15:16发布

问题:

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!

回答1:

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

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's assume that's the case, and color only the unprotected columns.
  var unprotRowRange = someSubsetOf(rowRange);  // <<<<< Adjust as needed.
  unprotRowRange.setBackgroundColor(statusColor);
}