// Sheets · Apps Script

Create a custom function with autocomplete in Google Sheets.

Write a Google Apps Script custom function that appears in Sheets autocomplete, with the right JSDoc tags and an understanding of what the sandbox will and won't let you do.

“I want to write a formula in Google Sheets that does something built-in formulas can't, and have it show up in autocomplete like a real function.”

The script

copy · paste · trigger

customFunctions.gs

Apps Script

/**
 * Returns the day count between two dates, excluding weekends.
 *
 * @param {Date} startDate The start date.
 * @param {Date} endDate The end date.
 * @return {number} Weekday count.
 * @customfunction
 */
function WEEKDAYS_BETWEEN(startDate, endDate) {
  var start = new Date(startDate);
  var end = new Date(endDate);
  var count = 0;
  while (start <= end) {
    var day = start.getDay();
    if (day !== 0 && day !== 6) {
      count++;
    }
    start.setDate(start.getDate() + 1);
  }
  return count;
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

The one tag that wires autocomplete

The `@customfunction` JSDoc tag is the only non-optional piece. Without it, your function runs fine when called from the formula bar, but it never surfaces in the autocomplete dropdown and Sheets won't generate the tooltip showing parameter names. Add it anywhere inside the doc block above your function.

The rest of the JSDoc matters too, but for different reasons. The `@param` lines populate the inline help card that appears when a user types the opening parenthesis. The `@return` line tells them what comes back. Omit those and the function still works — it just looks raw.

Function names are conventionally ALL_CAPS to match built-in formula conventions (SUM, VLOOKUP, and so on), but that's style, not a requirement. Sheets matches the name case-insensitively in the formula bar.

What the custom function sandbox actually permits

Custom functions run in a restricted execution context. They take arguments passed from the cell range, do computation, and return a value or a 2D array of values. That's the whole contract.

They cannot call services that require user authorization: no `UrlFetchApp`, no `GmailApp`, no `SpreadsheetApp.getActiveSpreadsheet()`, no `setValue()`. The first time I hit this, I spent twenty minutes debugging a `ReferenceError` that turned out to be a deliberate security boundary, not a bug. If you need to fetch a URL or write to a range, you need a menu item or a button-triggered function, not a custom function.

They also cannot use `PropertiesService`, `CacheService`, or `Utilities.sleep()`. Execution time is capped at 30 seconds. For pure computation — string manipulation, date math, unit conversions, custom rounding — they work exactly as expected.

Accepting ranges and returning arrays

When a user selects a range like `A1:A10` as an argument, Sheets passes it as a 2D array: `[[val1], [val2], ...]`. A single cell comes in as a scalar. Your function needs to handle both if you want it to behave like built-in functions that accept either.

Returning an array spills the results into adjacent cells, exactly like ARRAYFORMULA behavior. Return a 1D array and it spills across a row; return a 2D array and it fills a block. If the destination cells are occupied, Sheets throws a `#REF!` error — same as any spill conflict.

Type coercion is Sheets' job on the way in: a cell formatted as a date arrives as a JavaScript `Date` object, a number arrives as a number, a string as a string. You rarely need to parse manually unless the cell is plain text that happens to look like a date.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does my custom function show a 'You do not have permission' error?: You called a service that requires authorization — UrlFetchApp, GmailApp, SpreadsheetApp write methods, and similar. Custom functions run without OAuth. Move the call into a regular function triggered by a menu or button instead.
My function works when I run it from the script editor but returns a loading spinner forever in the cell.: Almost always a 30-second execution timeout. Custom functions have a hard 30s cap per call. If your computation is slow, profile it in the editor — Logger.log() and execution transcripts show per-line timing. Caching intermediate results in a local variable (not CacheService, which is blocked) often helps.
Can I make a custom function that reads data from another sheet or a named range?: Reading via SpreadsheetApp is blocked in custom functions. Pass the range as an argument from the formula bar instead: =MY_FUNC(Sheet2!A1:A100). Sheets resolves the reference and passes the values in.
How do I make the autocomplete tooltip show up for my function?: Two things must both be true: the @customfunction tag must be present in the JSDoc block, and the script must be bound to the spreadsheet (opened via Extensions > Apps Script from inside that sheet). A standalone script in a separate project won't surface autocomplete in a different sheet.

// one good script a week