How to send data from Google Sheets cells to custom Apps Script functions

Other Questions Google Sheets
1

I’m having trouble with something that should be straightforward. I can’t figure out how to properly send data from my spreadsheet to a custom function I wrote in Apps Script.

Here’s what I have in my script editor:

function calculateMonthlyData(dataRange, selectedDate) {
    // process the data that should be coming from the sheet
}

And in my spreadsheet I’m calling it like this:

=calculateMonthlyData('Data Sheet'!C2:C,D5)

The problem is when I try to debug this, both parameters show up as “undefined”. I’ve tried copying examples from different tutorials but I keep getting the same issue. What am I missing here? The function runs but it can’t access the values I’m trying to pass to it.

2

I faced a similar issue while creating a budgeting tool. It’s crucial to ensure that your custom function follows the right setup specifically for Google Sheets. Avoid using any services like Gmail or Drive APIs, as these can cause custom functions to fail. Also, refrain from using any SpreadsheetApp methods, since they aren’t accessible within custom functions. Be mindful that sheet names also need to be placed correctly, as they are case-sensitive. To troubleshoot, you might want to run a few initial tests with hardcoded values instead. Once you confirm that your function works properly, incrementally reintroduce the parameters. If problems persist, consider changing the function name temporarily and retrying, as this can help the script registry acknowledge its updates.

3

try passing the range without quotes: =calculateMonthlyData(Data Sheet!C2:C,D5) or use getValues() inside your function to read the data. apps script doesn’t automatically convert range references to values.

4

This happens frequently with Apps Script custom functions. When you pass a range like ‘Data Sheet’!C2:C, Apps Script converts it to a 2D array automatically, but your sheet reference syntax might be incorrect. Try removing the quotes around your sheet name: =calculateMonthlyData(Data Sheet!C2:C,D5). If spaces exist in the sheet name, ensure you use single quotes around the sheet name: =calculateMonthlyData('Data Sheet'!C2:C,D5). Additionally, confirm that your function is saved and authorized properly—undefined parameters often indicate issues with saving or permissions. As a quick test, add Logger.log(dataRange) at the beginning of your function and review the execution log.