Google Script / Spreadsheet -- Shared permissions with Installed Trigger onEdit
- by user1761852
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);
}
