Google Sheets背景色单元格统计脚本重复运行报错求助：TypeError: Cannot read property 'pop' of null

Let's break down why you're hitting this error and walk through a solid fix:

The Root Cause #

Your original script depends entirely on parsing the formula from the active cell to grab the countRange and colorRef inputs. When you run the script directly from the Apps Script editor (not via a cell formula), or if the formula in your active cell gets malformed, the regex patterns can't find the expected matches and return null. Trying to call .pop() on null throws exactly the error you're seeing.

Fixed Script with Error Handling & Flexibility #

Here's an updated version that safeguards against invalid inputs, works for both formula calls and direct script runs, and is more robust overall:

function countColoredCells(countRange, colorRef) {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var targetRange, targetColor;

  // Handle cases where function is called via cell formula
  if (!countRange || !colorRef) {
    var activeRange = SpreadsheetApp.getActiveRange();
    var formula = activeRange.getFormula();
    
    // First validate the formula structure
    if (!formula.startsWith('=countColoredCells(') || !formula.endsWith(')')) {
      throw new Error("Run this function through a cell formula like: =countColoredCells(A1:C10, D1)");
    }

    // Extract range and color cell with safer regex matching
    var matches = formula.match(/=countColoredCells\((.*?)\s*\,\s*(.*?)\)/);
    if (!matches || matches.length < 3) {
      throw new Error("Invalid formula format. Use: =countColoredCells(统计范围, 颜色单元格)");
    }
    
    targetRange = activeSheet.getRange(matches[1].trim());
    targetColor = activeSheet.getRange(matches[2].trim()).getBackground();
  } 
  // Handle direct script execution (with explicit parameters)
  else {
    targetRange = activeSheet.getRange(countRange);
    targetColor = activeSheet.getRange(colorRef).getBackground();
  }

  // Count cells matching the target color
  var bgColors = targetRange.getBackgrounds();
  var count = 0;
  for (var row of bgColors) {
    for (var color of row) {
      if (color === targetColor) count++;
    }
  }

  return count;
}

Key Improvements #

• Input Validation: Checks if the formula is properly formatted before parsing, with clear error messages to guide you if not.
• Safer Regex: Uses non-greedy matching (.*?) and trims whitespace to avoid issues with messy formula formatting.
• Dual Execution Support: Works both when called via a cell formula (like =countColoredCells(A2:A50, B2)) and when run directly from the script editor (if you pass valid A1 notation strings as parameters).
• Cleaner Code: Swapped nested for loops for modern for...of syntax to make the counting logic easier to read.

How to Use #

1. Replace your existing script with this updated version in the Apps Script editor.
2. In your Google Sheet, use the formula exactly like this (adjust ranges to match your data):

   =countColoredCells(A1:C10, D1)
   

   Where A1:C10 is the range you want to count, and D1 is the cell with the background color you're targeting.

内容的提问来源于stack exchange，提问作者missing string