在Google电子表格中设置背景颜色时的权限问题 [英] Permission issue when setting background color in google spreadsheet

问题描述

我在Google文档中有一个脚本，用于在状态列包含特定值时设置行的背景颜色。问题在于工作表具有某些受保护的列，因此当受限用户触发其部分上的脚本时，它会运行，但会得到令人讨厌的权限错误消息（因为脚本会对受保护列的部分进行着色）。因此，我需要创建2个脚本，以块的形式对行进行着色，以便当受限用户触发个性化状态消息时，只有他们未受保护的列颜色。这是为整行着色的代码（由可安装的onedit触发器触发）：

$ p $函数setBackLogColor（）{

var range = SpreadsheetApp.getActiveSheet（）。getDataRange（）;
var statusColumnOffset = getStatusColumnOffset（）; （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 PARTTS 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）;
}
}
}

//返回标题为状态
//的列的偏移值（例如，如果第7列是标签为Status，这个函数返回6）
函数getStatusColumnOffset（）{
lastColumn = SpreadsheetApp.getActiveSheet（）。getLastColumn（）;
var range = SpreadsheetApp.getActiveSheet（）。getRange（1,1,1，lastColumn）; （var i = 0; i< range.getLastColumn（）; i ++）{
if（range.offset（0，i，1，1）.getValue（）=

=状态）{
return i;
}
}
}

任何帮助将不胜感激！谢谢！

解决方案

这将是 Try..Catch block 。

  var statusColor =#FFFFFF; //默认值
 switch（status）{
 case'TO LD'：
 case'Hand Delivery'：
 statusColor =＃cfe2f3; 
休息; 
 case'TO GB'：
 case'Hand Delivery 2'：
 statusColor =＃d9ead3; 
休息; 
 case'to outside party PART  -  will come back'：
 statusColor =＃f4cccc; 
休息; 
默认值：
 //不执行任何操作，默认情况下已设置
 break; 
} 
 
尝试{
 //尝试着色整个范围
 //如果非特权用户，则会抛出异常。 
 rowRange.setBackgroundColor（statusColor）; 
} 
 catch（err）{
 //你可以验证错误确实是一个权限错误。 
 //假设情况是这样，并且只对不受保护的列进行着色。 
 var unprotRowRange = someSubsetOf（rowRange）; //<<<<<<根据需要调整。 
 unprotRowRange.setBackgroundColor（statusColor）; 
} 
  

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!

解决方案

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);
}

这篇关于在Google电子表格中设置背景颜色时的权限问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！