Skip to content
Advertisement

Google-Sheets counting cells based on strike through (and background color)

This is a follow up to :

Get the sum of values based on their background color – Google Sheets

I needed a slight modification :

-just count cells, not entries

-don’t count a cell if it was struck-out

This was my attempt which doesn’t work :

function totalColor(cells,color) {
    
const jsonColor = {
    redberry: [ '#980000','#e6b8af','#dd7e6b','#cc4125','#a61c00','#85200c','#5b0f00'],
    red: [ '#ff0000','#f4cccc', '#ea9999','#e06666','#cc0000','#990000','#660000' ],
    orange:[ '#ff9900','#fce5cd','#f9cb9c','#f6b26b','#e69138','#b45f06','#783f04' ],
    yellow: [ '#ffff00','#fff2cc','#ffe599','#ffd966','#f1c232','#bf9000','#7f6000' ],
    green: [ '#00ff00','#d9ead3','#b6d7a8','#93c47d','#6aa84f','#38761d','#274e13' ],
    cyan:  [ '#00ffff','#d0e0e3','#a2c4c9','#76a5af','#45818e','#134f5c','#0c343d' ],
    cornflowerblue: [ '#4a86e8','#c9daf8','#a4c2f4','#6d9eeb','#3c78d8','#1155cc','#1c4587' ],
    blue:[ '#0000ff','#cfe2f3','#9fc5e8','#6fa8dc','#3d85c6','#0b5394','#073763' ],
    purple: [ '#9900ff','#d9d2e9','#b4a7d6','#8e7cc3','#674ea7','#351c75','#20124d' ],
    magenta: [ '#ff00ff','#ead1dc','#d5a6bd','#c27ba0','#a64d79','#741b47','#4c1130' ],
    grey:["#666666", "#999999", "#b7b7b7", "#cccccc", "#d9d9d9", "#efefef", "#f3f3f3"],
    white: ["#ffffff"],
    black: ["#000000"]
  };  
  
const colorArr = jsonColor[color]; 
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const range = sheet.getRange(cells);
const hex_array = range.getBackgrounds().flat();
const format_array = range.getFontLines().flat();
  
const values = range.getValues().flat();
  
Logger.log(format_array);
  
var total = 0; 
hex_array.forEach((h,i,j)=>{                    
  if(colorArr.includes(h) && format_array[j] != "line-through"){
  total ++;
  }                    
}); 
return total;  
}

I also was trying to see what I was actually putting in that array, but where does the Logger go to as nothing shows in the console window (Edge, F12, select console).

Answer

You are very close.

In the forEach() method you must use only the i index:

function totalColor(cells,color) {
    
const jsonColor = {
    redberry: [ '#980000','#e6b8af','#dd7e6b','#cc4125','#a61c00','#85200c','#5b0f00'],
    red: [ '#ff0000','#f4cccc', '#ea9999','#e06666','#cc0000','#990000','#660000' ],
    orange:[ '#ff9900','#fce5cd','#f9cb9c','#f6b26b','#e69138','#b45f06','#783f04' ],
    yellow: [ '#ffff00','#fff2cc','#ffe599','#ffd966','#f1c232','#bf9000','#7f6000' ],
    green: [ '#00ff00','#d9ead3','#b6d7a8','#93c47d','#6aa84f','#38761d','#274e13' ],
    cyan:  [ '#00ffff','#d0e0e3','#a2c4c9','#76a5af','#45818e','#134f5c','#0c343d' ],
    cornflowerblue: [ '#4a86e8','#c9daf8','#a4c2f4','#6d9eeb','#3c78d8','#1155cc','#1c4587' ],
    blue:[ '#0000ff','#cfe2f3','#9fc5e8','#6fa8dc','#3d85c6','#0b5394','#073763' ],
    purple: [ '#9900ff','#d9d2e9','#b4a7d6','#8e7cc3','#674ea7','#351c75','#20124d' ],
    magenta: [ '#ff00ff','#ead1dc','#d5a6bd','#c27ba0','#a64d79','#741b47','#4c1130' ],
    grey:["#666666", "#999999", "#b7b7b7", "#cccccc", "#d9d9d9", "#efefef", "#f3f3f3"],
    white: ["#ffffff"],
    black: ["#000000"]
  };  
  
const colorArr = jsonColor[color]; 
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const range = sheet.getRange(cells);
const hex_array = range.getBackgrounds().flat();
const format_array = range.getFontLines().flat();
  
const values = range.getValues().flat();
  

  
var total = 0; 
hex_array.forEach((h,i)=>{                    
  if(colorArr.includes(h) && format_array[i] != "line-through"){
  total ++;
  }                    
}); 
return total;  
}
Advertisement