I’m experiencing an extremely frustrating and persistent issue with a Google Apps Script custom function in my Google Sheet. Despite extensive troubleshooting, my custom function (designed to sum cells by background color) is consistently receiving literal cell values (e.g., “1268.74,,263.98,…”) as arguments, instead of actual Range objects (e.g., C33:C45). This prevents the script from executing correctly.
Problem Summary:
My custom function’s signature (function SUM_UNCOLORED_CELLS(…ranges)) is correctly defined and the JSDoc /** @param {…GoogleAppsScript.Spreadsheet.Range} ranges … */ is in place. When I type the formula in the sheet, the helper tooltip correctly displays ranges (Range, …). However, upon execution, the Apps Script logs show:
Skipping invalid or empty range argument at index 0: 1268.74,,263.98,100,,48,,231.86,123,0,0,0,0
(and similar lines for all arguments), with a — Final sum: 0 —.
Goal: Sum numbers in non-contiguous ranges (e.g., C33:C45, C48:C54, …) that have no fill color (white/transparent background).
Formula in Sheet:
=SUM_UNCOLORED_CELLS(C33:C45,C48:C54,C57:C64,C67:C70,C73:C90,C93:C97,C100:C108,C111:C116)
Apps Script Code (Code.gs):
JavaScript
/**
* Sums the values in multiple ranges that do NOT have a custom fill color.
* It considers both truly transparent backgrounds ("") and default white backgrounds ("#ffffff") as "no fill".
*
* @param {...GoogleAppsScript.Spreadsheet.Range} ranges A list of ranges to sum (e.g., C1:C10, D1:D10).
* @return {Number} The sum of cells without a custom background color across all provided ranges.
* @customfunction
*/
function SUM_UNCOLORED_CELLS(...ranges) {
var sum = 0;
Logger.log('--- Starting SUM_UNCOLORED_CELLS execution ---');
for (var k = 0; k < ranges.length; k++) {
var currentRange = ranges[k];
if (currentRange && typeof currentRange.getValues === 'function') {
// This block should execute, but based on logs, currentRange is not a Range object
Logger.log('Processing range: ' + currentRange.getA1Notation()); // This line is never reached
var values = currentRange.getValues();
var backgrounds = currentRange.getBackgrounds();
for (var i = 0; i < values.length; i++) {
for (var j = 0; j < values[0].length; j++) {
var cellValue = values[i][j];
var cellBackground = backgrounds[i][j];
if ((cellBackground === '' || cellBackground === '#ffffff') && typeof cellValue === 'number' && !isNaN(cellValue)) {
sum += cellValue;
}
}
}
} else {
// THIS IS THE PROBLEM SEEN IN MY LOGS:
Logger.log('Skipping invalid or empty range argument at index ' + k + ': ' + currentRange);
}
}
return sum;
}
What I’ve Tried (All Unsuccessful):
Ensured script code is accurate, authorized, and uses correct JSDoc.
Cleared browser cache and cookies (all time).
Refreshed/reopened the Google Sheet/browser multiple times.
Deleted and re-entered the formula from scratch.
Renamed the custom function (from SUM_NO_COLOR to SUM_UNCOLORED_CELLS) to force re-indexing by Google Sheets.
Tried testing with a single range (e.g., =SUM_UNCOLORED_CELLS(C33:C40)), which also failed with the same “Skipping invalid argument” error in logs.
Confirmed the script is correctly bound to the specific Google Sheet.
(Also previously got an “only takes one argument” error message in the sheet, despite …ranges.)
My Conclusion:
It appears there’s a specific, persistent issue within my Google Sheets environment (potentially a bug or deep caching problem) that prevents range references from being passed as Range objects to custom functions, even when the function’s signature seems to be recognized by the sheet’s tooltip.
Any help or insights on why Sheets might be passing literal values instead of Range objects for custom functions would be greatly appreciated. Thank you!”
Attempted to use a custom Google Apps Script function: (SUM_UNCOLORED_CELLS) to sum cells based on background color.
Confirmed custom function script code is correct and designed to accept Range objects.
Confirmed the script is authorized for my Google account.
Observed contradictory behavior:
The formula helper tooltip in Google Sheets correctly shows the function expects (Range, …) arguments.
However, Apps Script execution logs consistently show the function is receiving literal values (e.g., “1268.74,,263.98,…”) instead of actual Range objects from the sheet.
This leads to Skipping invalid or empty range argument in logs and a final sum of 0.
Performed standard browser/sheet troubleshooting:
Cleared browser cache and cookies (all time).
Refreshed the Google Sheet page (F5/Ctrl+F5) multiple times.
Closed and reopened the Google Sheet tab and browser.
Deleted and re-entered the formula from scratch in the sheet.
Renamed the custom function (to SUM_UNCOLORED_CELLS) to force Google Sheets to re-index its signature.
Tested the function with a single, simple range (e.g., =SUM_UNCOLORED_CELLS(C33:C40)) which also failed with the same argument-passing error.
Attempted to get help through official channels: Tried to post to the Google Sheets Help Community forum but was blocked due to a “community policy” violation.