Google Apps Script中length函数异常：自定义AVG_ZERO函数返回数组长度不符合预期

AI 小北

2026-4-27

Troubleshooting Your Google Sheets Custom Function Issue

Hey there! Let's break down why your custom function is behaving differently for row vs column ranges, and fix it up right away.

Why the Length Mismatch Happens

When Google Sheets sends a cell range to your custom function, it packages the input as a 2D array—but the structure shifts based on whether you're using a column or row:

For a column range (like A1:A3), the input arrives as [[1], [2], [3]]. The outer array has 3 elements (one per row), so input.length correctly returns 3.
For a row range (like B1:D1), the input is formatted as [[1, 2, 3]]. Here, the outer array only has 1 element (the entire row wrapped in an array), so input.length gives you 1 instead of the 3 cells you're expecting.

Fixes to Get the Correct Length

Depending on your use case, here are two simple solutions:

1. Handle Single Row/Column Ranges

If you only work with single rows or columns (no multi-row/column blocks), this function will correctly count cells for both orientations:

function AVG_ZERO(input) {
  // Check if we're dealing with a range (2D array)
  if (Array.isArray(input[0])) {
    // Row range: use the inner array's length (number of columns)
    if (input.length === 1) {
      return input[0].length;
    }
    // Column range: use the outer array's length (number of rows)
    else {
      return input.length;
    }
  }
  // Handle single cell input
  else {
    return 1;
  }
}

2. Total Cell Count for Any Range

If you might use multi-row/column ranges (like A1:C3) and want the total number of cells, use this more robust version:

function AVG_ZERO(input) {
  let totalCells = 0;
  // Loop through each row in the input range
  for (const row of input) {
    // Add the number of cells in the row to the total
    totalCells += Array.isArray(row) ? row.length : 1;
  }
  return totalCells;
}