Google Script / Spreadsheet -- Shared permissions with Installed Trigger onEdit

Question

Using an installed trigger inside spreadsheet to call onUpdateBilling(). Purpose of this script is on edit, based on content of column "billed" (i.e. "d") will highlight the entire column the predetermined color.

Page running script is shared with collaborators and they have been given edit access. My expectation at this point is the script should be run with owner permissions. My shared users are unable to run the script with the given error "You don't have permission for this action."

Reached my limited knowledge and googlefu for this workaround.

Any help to allow operation to my collaborators is appreciated.

Script:
function onUpdateBilling(e) {

var statusCol = 16; // replace with the column index of Status column A=1,B=2,etc

var sheetName = "Temple Log"; // replace with actual name of sheet containing Status

var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
var sheet = cell.getSheet();
if(cell.getColumnIndex() != statusCol || sheet.getName() != sheetName) return;

var row = cell.getRowIndex();
var status = cell.getValue();

// change colors to meet your needs
var color;
if (status == "D" || status == "d") {
  color = "red";}
  else if (status >= 1) {
    color = "yellow";}
  else if (status == "X" || status == "x") {
    color = "black";}
  else if (status == "") {
    color = "white";}
  else {
  color = "white";
  }

sheet.getRange(row + ":" + row ).setBackgroundColor(color);
}

inquisitive inquisitive · Accepted Answer · 2012-11-02T06:31:45

The same problem happened to me. After a lot of efforts I found out that one of sheet/range which the script updates was protected by the owner.

once the owner removed the protection, it worked just fine.

please note that the script does not run with owner-permission. it runs with the account of the currently logged in user.

hope it helps.

Google Script / Spreadsheet -- Shared permissions with Installed Trigger onEdit

1 Answers