Google sheet app sheet for get hex color and count based on color

gistfile1.txt

/**
 * Get the background color of a cell or top-left of a range.
 * @param {string} a1 - A1 notation like "C18" or "B2:D5" (can include sheet name e.g. "Sheet2!C18")
 * @return {string} Hex color like "#ffffff"
 * @customfunction
 */
function GETCELLCOLOR(a1) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // Allow "Sheet!A1" or just "A1"
  let sheet = ss.getActiveSheet();
  let ref = a1;

  // If user passed "Sheet!A1", split it
  if (typeof a1 === 'string' && a1.includes('!')) {
    const [sheetName, rangeA1] = a1.split('!');
    sheet = ss.getSheetByName(sheetName.replace(/^'/, '').replace(/'$/, '')) || sheet;
    ref = rangeA1;
  }

  const range = sheet.getRange(ref);
  // For ranges, return the top-left cell’s color
  return range.getBackgrounds()[0][0];
}

/**
 * COUNTBYCOLOR
 * Counts cells in a range whose background equals the given color.
 *
 * @param {string} colorRefOrHex  A1 of a cell whose color to match (e.g. "H1")
 *                                OR a hex like "#fce4d6"
 *                                (You can also pass "Sheet2!H1")
 * @param {string} rangeA1        Range in A1 notation (e.g. "C18:F40" or "Sheet1!C18:F40")
 * @return {number} Count of cells with matching background color
 * @customfunction
 */
function COUNTBYCOLOR(colorRefOrHex, rangeA1) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Resolve color hex
  let colorHex = String(colorRefOrHex || "").trim();
  if (!colorHex) return 0;

  // If user passed an A1 ref (contains '!' or letters+digits), read that cell's bg
  const looksLikeA1 = /!|[A-Za-z]+\d+/.test(colorHex) && !/^#([0-9A-Fa-f]{3}|[0-9A-Fa-f]{6})$/.test(colorHex);
  if (looksLikeA1) {
    let sheet = ss.getActiveSheet();
    let ref = colorHex;
    if (colorHex.includes('!')) {
      const [sheetName, a1] = colorHex.split('!');
      sheet = ss.getSheetByName(sheetName.replace(/^'/, '').replace(/'$/, '')) || sheet;
      ref = a1;
    }
    colorHex = sheet.getRange(ref).getBackground();
  }

  colorHex = normalizeHex(colorHex);
  if (!colorHex) return 0;

  // Resolve range
  let sheet = ss.getActiveSheet();
  let ref = rangeA1;
  if (rangeA1.includes('!')) {
    const [sheetName, a1] = rangeA1.split('!');
    sheet = ss.getSheetByName(sheetName.replace(/^'/, '').replace(/'$/, '')) || sheet;
    ref = a1;
  }

  const bgs = sheet.getRange(ref).getBackgrounds();
  let count = 0;
  for (let r = 0; r < bgs.length; r++) {
    for (let c = 0; c < bgs[r].length; c++) {
      if (normalizeHex(bgs[r][c]) === colorHex) count++;
    }
  }
  return count;
}

function COUNTBYCOLOR(colorRefOrHex, rangeRefOrStr) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // --- Resolve color ---
  let colorHex = String(colorRefOrHex || "").trim();
  if (!colorHex) return 0;

  // If it's an A1 ref instead of hex, get that cell's color
  const looksLikeA1 = /[A-Za-z]+\d+/.test(colorHex) &&
    !/^#([0-9A-Fa-f]{3}|[0-9A-Fa-f]{6})$/i.test(colorHex);
  if (looksLikeA1) {
    const range = ss.getActiveSheet().getRange(colorHex);
    colorHex = range.getBackground();
  }
  colorHex = normalizeHex(colorHex);
  if (!colorHex) return 0;

  // --- Resolve range ---
  const rangeStr = String(rangeRefOrStr || "").trim();
  let sheet = ss.getActiveSheet();
  let ref = rangeStr;

  if (rangeStr.includes('!')) {
    const [sheetName, a1] = rangeStr.split('!');
    sheet = ss.getSheetByName(sheetName.replace(/^'|'$/g, "")) || sheet;
    ref = a1;
  }

  const range = sheet.getRange(ref);
  const bgs = range.getBackgrounds();

  // --- Count matches ---
  let count = 0;
  for (let r = 0; r < bgs.length; r++) {
    for (let c = 0; c < bgs[r].length; c++) {
      if (normalizeHex(bgs[r][c]) === colorHex) count++;
    }
  }
  return count;
}

function normalizeHex(x) {
  if (!x) return "";
  const s = String(x).trim().toLowerCase();
  if (/^#([0-9a-f]{6})$/i.test(s)) return s;
  if (/^#([0-9a-f]{3})$/i.test(s)) {
    return "#" + s[1] + s[1] + s[2] + s[2] + s[3] + s[3];
  }
  return "";
}