Advertisement
RemcoE33

Count background color in combination of value

Jun 21st, 2021 (edited)
124
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /**
  2.  * Created by RemcoE33
  3. */
  4.  
  5.  
  6. /**
  7. * Return count of background in combination of value
  8. *
  9. * @param {A1:D5} range Enter (multicolom) range.
  10. * @param {"#FFFFF"} color Enter colorcode as string.
  11. * @param {"Lifeline"} criteria Enter criteria.
  12. * @return count of cells with given colorbackgrond and criteria.
  13. * @customfunction
  14. */
  15. function COUNT_COLOR_VALUE(range, color, criteria) {
  16.   const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  17.   const formula = ss.getActiveRange().getFormula()
  18.   const colorRange = /\((.*?),/.exec(formula)[1];
  19.   const backgrounds = ss.getRange(colorRange).getBackgrounds()
  20.  
  21.   let count = 0
  22.  
  23.   for (i = 0; i < backgrounds.length; i++) {
  24.     for (j = 0; j < backgrounds[0].length; j++) {
  25.       if (backgrounds[i][j] == color && range[i][j] == criteria) {
  26.         count++
  27.       }
  28.     }
  29.   }
  30.  
  31.   return count
  32. }
  33.  
  34. /**
  35. * Returns background hex color
  36. *
  37. * @param {A1} cell Enter cell reference to get the background
  38. * @return background hex color
  39. * @customfunction
  40. */
  41. function GET_BACKGROUND(cell){
  42.   const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  43.   const formula = ss.getActiveRange().getFormula().toLocaleUpperCase();
  44.   const colorRange = /GET_BACKGROUND\((.*?)\)/.exec(formula)[1];
  45.   return ss.getRange(colorRange).getBackground().toString();
  46. }
  47.  
  48.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement