I’m having trouble with parameter passing in Google Sheets custom functions. I created a custom function in the Apps Script editor but the parameters I send from the spreadsheet formula aren’t reaching the function properly.
My Apps Script function looks like this:
function calculateMonthlyData(dataRange, targetDate) {
// process the parameters here
console.log(dataRange); // shows undefined
console.log(targetDate); // shows undefined
}
In my spreadsheet, I’m calling it with:
=calculateMonthlyData('Data Sheet'!B2:B50, C3)
When I debug this function, both parameters show up as undefined. I’ve tried several different approaches and looked at various examples online, but I keep getting the same issue. The parameters just don’t seem to be passed correctly from the sheet to the script function. What am I missing here?
try removing the quotes around Data Sheet. use: =calculateMonthlyData(Data Sheet!B2:B50, C3). if your sheet name has spaces, wrap it in single quotes instead.
Apps Script parameter debugging is a nightmare. I’ve wasted entire days on this stuff.
You’re trapped in the worst cycle - save, refresh, test, check logs, repeat. It’s brutal.
Stop fighting with parameter passing and automate the whole pipeline instead. Set up something that watches your sheet and processes changes automatically.
I had the same problem with sheet formulas and custom functions. Ditched it for an automation platform that hits Google Sheets API directly and handles processing outside Apps Script. No more parameter headaches or undefined variables, and you can actually see what’s happening.
It triggers when your cells change, grabs the data you need, processes it with proper error handling, and writes results back. Way more reliable than custom functions.
You get real logging and can modify logic without Apps Script’s weirdness.
Had this exact problem last month - drove me crazy for hours. The issue’s usually how Apps Script handles parameter types from sheet formulas. Your function expects specific data types, but sheets might pass something completely different. Add type checking at the start like if (!dataRange || !targetDate) { return 'Invalid parameters'; } to see what’s actually coming through. Double-check your sheet reference too - the sheet name needs to match the tab exactly, including any trailing spaces. I’d test with simpler parameters first, like a single cell value instead of a range, to isolate the problem.
This looks like a sheet name reference issue. Google Sheets won’t accept string literals with sheet references in custom functions - you need direct cell references or named ranges instead. Try =calculateMonthlyData('Data Sheet'!B2:B50, C3) and only use single quotes if your sheet name has spaces. Better yet, create a named range like =calculateMonthlyData(MyDataRange, C3) for cleaner code. Double-check that your function’s saved and authorized too - Apps Script sometimes needs explicit permission to access your data.
make sure your function’s deployed as a custom one, not just saved. Apps Script sometimes doesn’t recognize em until you trigger em manually. also check if C3 is empty - if it is, the parameter will be undefined.