Skip to content

Instantly share code, notes, and snippets.

@branning
Created February 11, 2019 05:17
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save branning/f145c5a0966b542574ba346f5260cb87 to your computer and use it in GitHub Desktop.
Save branning/f145c5a0966b542574ba346f5260cb87 to your computer and use it in GitHub Desktop.
Google Sheets set cell background color based on named range background color
var stages = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("Stages")
function onEdit(e) {//"e" receives the event object
var range = e.range;//The range of cells edited
var value = e.value;//the new value in the cell
var columnOfCellEdited = range.getColumn();//Get column number
if (columnOfCellEdited !== 1) {
return;
};
const stageValues = stages.getValues();
log("stages is shaped (" + stageValues.length + " x " + stageValues[0].length + ")");
var position = stageValues[0].indexOf(value);
if (position === -1) {
log("could not find " + value + " in " + stages.getValues());
return
}
var stage = stages.getCell(1, position + 1);
var bgcolor = stage.getBackground();
range.setBackground(bgcolor);
return
};
function log(line) {
Logger.log(line)
console.log(line)
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment