Can someone please tell me why my script doesn’t work the way its supposed to?
In google sheets
If any cell in a row turns red then the firsts cell in that row turns red. its supposed to do the same with yellow and orange. with red always taking priority, so if theres a red cell in the the same row with a yellow or orange it will override the firsts cell with a red background.
So far the yellow and orange do not work.
function MakeRed(){
var book = SpreadsheetApp.getActiveSpreadsheet();
var sheet = book.getActiveSheet();
var first_column = "B";
var first_row = 1;
var last_row = sheet.getLastRow();
var last_column = sheet.getLastColumn();
var active_row = 1;
var range_input = sheet.getRange(1,1,last_row,last_column);
var range_output = sheet.getRange("A1");
var cell_colors = range_input.getBackgroundColors();
var color = "#ff0000";
for(var r = 0; r < cell_colors.length; r++) {
var rowWoColA = cell_colors[r].slice(1);
if(rowWoColA.indexOf(color)>-1) {
cell_colors[r][0]=color;
} else {
cell_colors[r][0]="#ffffff";
}
}
range_input.setBackgroundColors(cell_colors);// update sheet colors
}
function ColorOfCol(){
var book = SpreadsheetApp.getActiveSpreadsheet();
var sheet = book.getActiveSheet();
var range_input = sheet.getDataRange();
var cell_colors = range_input.getBackgrounds();
if (cell_colors.length == 0) throw new Error( 'No data in sheet' );
// List of colors in priority order, default at end.
var color = ["#ff0000","#ff9900","#ffff00","#ffffff"];
for (var r = 0; r < cell_colors.length; r++) {
// check for all colors EXCEPT our default, the last in color[]
for (var b=0; b<color.length-1; b++) {
// -1 means not found, but 0 means found in first column, which we don't care about
if (cell_colors[r].indexOf(color[b])>0) {
// found this color, stop searching, with 'b' == found color
break;
}
}
// 'b' will contain the color index. If none found, it will be the last color, our default.
cell_colors[r][0]=color[b];
}
range_input.setBackgrounds(cell_colors);// update sheet colors
}