CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Cliff Stamp
Cliff Stamp

Reputation: 571

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).

Upvotes: 0

Views: 570

Answers (2)

Xavave
Xavave

Reputation: 685

I know it's not exactly what was asked but, if you just want the number of cells in a range with a background color from another cell, you can use this function:

// countCellsWithBackgroundColor: returns cell count from rangeSpecString where cell background color equals colorCellSpecString cell background color
// example usage: =countCellsWithBackgroundColor("A1:A30", "E12");  // if A1:A30 contains 10 cells with same background than E12 cell background, function returns value: 10
function countCellsWithBackgroundColor(rangeSpecString,colorCellSpecString)
{  
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  var colorRange =  sheet.getRange(rangeSpecString).getBackgrounds()[0];
  var colorToCheck = sheet.getRange(colorCellSpecString).getBackground();
  return colorRange.filter(c=>c==colorToCheck).length;
}

Upvotes: 0

Marios
Marios

Reputation: 27348

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;  
}

Upvotes: 2

Related Questions