First cell in active row during script execution -

First cell in active row during script execution -

i using code below view through sheet, , alter first cell background reddish if other cells red. like, alter first cell in each row red, if row contains reddish cell. code works through spreadsheet, makes cell a1 red. how impact first cell of each row independently?

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"; var count = 0; for(var r = 0; r < cell_colors.length; r++) { for(var c = 0; c < cell_colors[0].length; c++) { if(cell_colors[r][c] == color) { count++; range_output.setbackground("#ff0000"); } else { count--; if (count == 0) { range_output.setbackground("#ffffff"); } } } } }

i suggest whole stuff in array of background colors have instead of calling spreadsheet service each time status true.

code becomes simpler because used indexof array method in condition

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"; var count = 0; for(var r = 0; r < cell_colors.length; r++) { logger.log(cell_colors[r].indexof(color));// check, can remove of course of study ... if(cell_colors[r].indexof(color)>-1) { count++; cell_colors[r][0]=color; } else { count--; if (count == 0) { cell_colors[r][0]="#ffffff"; } } } range_input.setbackgroundcolors(cell_colors);// update sheet colors }

edit : don't understand thought behind "count" thing... works same way without (code below) :

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++) { logger.log(cell_colors[r].indexof(color)) if(cell_colors[r].indexof(color)>-1) { cell_colors[r][0]=color; } else { cell_colors[r][0]="#ffffff"; } } range_input.setbackgroundcolors(cell_colors);// update sheet colors }

edit 2 : version reset col white if no other reddish cell in row

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 }

google-apps-script