How to display optional parameters in Google Sheets custom function autocomplete?

Other Questions Google Sheets
1

I’m trying to create a custom function in Google Sheets with optional arguments. I want the autocomplete to show these optional parameters similar to built-in functions like WEEKDAY.

I’ve tried using JSDoc syntax for optional arguments, but it doesn’t seem to work in Google Sheets. The documentation doesn’t mention how to achieve this either.

Here’s what I’ve tried so far:

/**
 * Calculates something
 * @param {string} mainArg Main argument description
 * @param {string} [extraArg] Extra argument (optional)
 * @return {string} Result description
 * @customfunction
 */
function MYCALC(mainArg, extraArg) {
  // Function logic here
}

This shows up in the autocomplete, but it doesn’t indicate that extraArg is optional. How can I make it clear in the autocomplete that some arguments are optional, just like Google’s built-in functions do?

Has anyone figured out a way to do this? Any help would be appreciated!

2

hey, i’ve struggled with this too. one trick i use is naming the function differently for each param combo. like MYCALC_BASIC(mainArg) and MYCALC_ADVANCED(mainArg, extraArg). not perfect, but it helps users see options. good luck!

3

I’ve encountered this issue before, and unfortunately, there’s no straightforward way to display optional parameters in Google Sheets custom function autocomplete. Google’s internal system for built-in functions likely handles this differently than what’s available to us for custom functions.

However, one workaround I’ve found is to use default values in your function definition. For example:

function MYCALC(mainArg, extraArg='default') {
  // Function logic here
}

This approach doesn’t visually indicate optional parameters in the autocomplete, but it allows users to omit the extra argument. You could also consider creating separate functions for different parameter combinations if it’s crucial for users to see the optionality clearly.

Alternatively, you might want to thoroughly document your function’s usage in a separate sheet or README file, explaining which parameters are optional and how to use them effectively.