Custom Functions in Google Sheets: Build Your Own Formulas with Apps Script

Every Google Sheets user has moments where the built-in formula library runs out of road. You need to calculate something specific to your business — applying the correct GST rate, validating an ABN before saving it to a client record, or converting a date into the DD/MM/YYYY format that every Australian accountant expects — and there is no native formula that does it cleanly.

Custom functions are the answer. Google Apps Script lets you write your own formula in JavaScript, give it a name, and then call it from any cell in your spreadsheet exactly like =SUM() or =VLOOKUP(). Your colleagues do not need to know Apps Script. They just type =GST_AMOUNT(B2) and get the result.

This tutorial covers everything you need to start writing custom functions today: how they work, how to document them so they show autocomplete hints just like built-in formulas, how to handle ranges and return arrays, the important limitations you need to plan around, and three complete practical examples built for Australian business workflows.

What Custom Functions Are

A custom function in Google Sheets is a JavaScript function written in Apps Script that can be called from a spreadsheet cell as a formula. When a cell contains =MY_FUNCTION(A1), Google evaluates the Apps Script code, passes the value of A1 as an argument, and displays the return value in the cell.

Custom functions work like built-in formulas in almost every way:

• They appear in the autocomplete dropdown as you type (with proper documentation)
• They accept cell references, ranges, and literal values as arguments
• They recalculate when their input cells change
• They can return a single value or an array of values
• They can be used inside other formulas, including =ARRAYFORMULA()

The key difference from other Apps Script code is that custom functions run with a restricted permission set. They cannot send emails, access external URLs, modify spreadsheet structure, or call most Workspace services. They can read the values passed to them as arguments and return a result. That constraint is intentional — it keeps custom functions safe to call automatically as a spreadsheet recalculates.

Opening the Script Editor

Custom functions live in the Apps Script project bound to your spreadsheet.

1. Open your Google Sheet.
2. Click Extensions in the top menu.
3. Select Apps Script.
4. The Apps Script editor opens in a new tab showing a Code.gs file.

Delete the default empty function and you are ready to write your first custom function.

Creating a Basic Custom Function

Here is the simplest possible custom function — one that doubles a number:

function DOUBLE(value) {
  return value * 2;
}

Save the script (Ctrl+S or Cmd+S), then switch back to your spreadsheet. In any cell, type =DOUBLE(5) and press Enter. The cell will display 10.

Custom function names are conventionally written in uppercase (like DOUBLE, GST_AMOUNT, FORMAT_AU_DATE) so they read naturally alongside built-in formula names. Lowercase and mixed-case names work, but the convention keeps your spreadsheet consistent.

A few important rules:

• The function name cannot match an existing built-in formula name
• The function must return a value; if it returns nothing, the cell displays an empty result
• The function runs under the identity of the person who has authorised the script, but with the restricted permissions described above

JSDoc Annotations for Autocomplete

One of the most useful features of custom functions is that Google Sheets displays tooltip hints and autocomplete suggestions when you type the function name — but only if you document the function with JSDoc comments.

JSDoc is a comment format that sits directly above the function definition. Here is the DOUBLE function with proper documentation:

/**
 * Doubles the input value.
 *
 * @param {number} value The number to double.
 * @return {number} The doubled value.
 * @customfunction
 */
function DOUBLE(value) {
  return value * 2;
}

The @customfunction tag is required. Without it, Sheets will not surface the function in autocomplete. The @param tags describe each argument (name and type), and @return describes what the function produces. The descriptive sentence at the top becomes the tooltip that appears as someone types the formula.

When done well, a colleague typing =GST_ in a cell will see your function appear in the dropdown with a description that reads "Calculates the GST component of a GST-inclusive amount" — exactly like a built-in formula.

Accepting Ranges as Input

Custom functions can accept a range of cells as an argument. When a user passes a range like A2:A50, Apps Script receives it as a two-dimensional array — an array of rows, where each row is itself an array of column values.

Here is how you handle that correctly:

/**
 * Returns the sum of all positive numbers in a range, ignoring negatives and blanks.
 *
 * @param {number[][]} values A range of numeric values.
 * @return {number} The sum of all positive values in the range.
 * @customfunction
 */
function SUM_POSITIVE(values) {
  var total = 0;

  // values is a 2D array: [[row1col1, row1col2], [row2col1, ...], ...]
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      var cell = values[i][j];
      if (typeof cell === 'number' && cell > 0) {
        total += cell;
      }
    }
  }

  return total;
}

The nested loop iterates over every row and every column within the range. The typeof cell === 'number' check ensures empty cells and text values are safely skipped rather than causing errors.

In the spreadsheet, the function is called as:

=SUM_POSITIVE(B2:D50)

Returning Arrays

Custom functions can return an array instead of a single value, causing the result to spill across multiple cells — exactly like =SPLIT() or modern dynamic array formulas.

To return an array, simply return a JavaScript array from your function. A one-dimensional array fills across a row; a two-dimensional array (array of arrays) fills a grid.

Here is a function that returns a range's values with a running total in a second column:

/**
 * Takes a column of numbers and returns two columns: the original values
 * and a running cumulative total beside each.
 *
 * @param {number[][]} values A single-column range of numbers.
 * @return {number[][]} A two-column array: original value and running total.
 * @customfunction
 */
function WITH_RUNNING_TOTAL(values) {
  var result = [];
  var runningTotal = 0;

  for (var i = 0; i < values.length; i++) {
    var val = values[i][0];
    if (typeof val === 'number') {
      runningTotal += val;
    }
    result.push([val, runningTotal]);
  }

  return result;
}

Place =WITH_RUNNING_TOTAL(A2:A20) in cell C2 and it will populate columns C and D with the original values and running totals respectively. Make sure there are at least two empty columns available to the right — Apps Script will overwrite cells in that area with the returned values.

Limitations You Must Know

Custom functions are powerful, but they run in a restricted sandbox. Understanding the constraints upfront will save you from hitting confusing errors in production.

No Gmail, Drive, or UrlFetch calls

Custom functions cannot call MailApp, GmailApp, DriveApp, UrlFetchApp, or most other Workspace services. This is a deliberate security boundary. Because custom functions can be called on every spreadsheet recalculation, allowing them to send emails or make HTTP requests would create obvious abuse vectors. If your formula logic needs to call an external API or send a notification, the right tool is a time-based trigger or an onEdit trigger, not a custom function.

30-second execution timeout

Each custom function call must complete within 30 seconds. For functions that process small ranges or perform simple calculations, this is never an issue. For functions that process large ranges with complex logic, it can be. If you are building a function that will be called on thousands of rows, benchmark it on a realistic data size before deploying to production.

No access to the calling cell's location

A custom function receives only the values passed to it as arguments. It cannot ask "which cell called me?" and it cannot read other cells beyond what it has been explicitly given. This is consistent with how built-in formulas work and keeps the execution model predictable.

Re-execution on recalculation

Custom functions re-execute every time any of their input cells change, just like built-in formulas. If a function performs heavy processing, this can cause noticeable lag as the spreadsheet recalculates. The mitigation for expensive functions is caching, covered in the next section.

Caching Results with CacheService

When a custom function performs a calculation that is expensive (processing a large range, doing complex string operations) and the inputs do not change frequently, you can use CacheService to store the result for a period of time rather than recomputing it on every recalculation.

CacheService is one of the few non-spreadsheet services available to custom functions. It provides a simple key-value store with a maximum TTL (time to live) of six hours.

Here is a pattern for a function that caches its output:

/**
 * Returns a formatted display name for an Australian state code,
 * with results cached for 10 minutes to reduce recalculation overhead.
 *
 * @param {string} stateCode A two-letter state code (e.g. "NSW", "VIC").
 * @return {string} The full state name, or the original code if unrecognised.
 * @customfunction
 */
function AU_STATE_NAME(stateCode) {
  if (!stateCode || typeof stateCode !== 'string') return '';

  var normalised = stateCode.toString().trim().toUpperCase();
  var cacheKey = 'AU_STATE_' + normalised;

  // Attempt to read from cache first
  var cache = CacheService.getScriptCache();
  var cached = cache.get(cacheKey);
  if (cached !== null) {
    return cached;
  }

  // State lookup map
  var states = {
    'NSW': 'New South Wales',
    'VIC': 'Victoria',
    'QLD': 'Queensland',
    'SA':  'South Australia',
    'WA':  'Western Australia',
    'TAS': 'Tasmania',
    'ACT': 'Australian Capital Territory',
    'NT':  'Northern Territory'
  };

  var result = states[normalised] || stateCode;

  // Store in cache for 600 seconds (10 minutes)
  cache.put(cacheKey, result, 600);

  return result;
}

The cache key must uniquely identify the input. For functions that process multiple inputs, concatenate all parameters into the key. The second argument to cache.put() is the TTL in seconds; the maximum is 21,600 (six hours).

For most custom functions, caching is unnecessary. Use it when you have measured a real performance problem and the function's inputs are stable enough that a cached result will remain valid.

Practical Example 1: GST Calculator

Australian businesses add or remove GST constantly. Rather than writing =B2*0.1 or =B2/1.1 every time, these two custom functions encapsulate the logic with clear names:

/**
 * Calculates the GST component included in a GST-inclusive price.
 * Australia applies GST at 10%.
 *
 * @param {number} inclusiveAmount The GST-inclusive dollar amount (AUD).
 * @return {number} The GST component, rounded to two decimal places.
 * @customfunction
 */
function GST_COMPONENT(inclusiveAmount) {
  if (typeof inclusiveAmount !== 'number' || inclusiveAmount <= 0) return 0;
  return Math.round((inclusiveAmount / 11) * 100) / 100;
}

/**
 * Returns the GST-inclusive price from a GST-exclusive (ex-GST) amount.
 * Australia applies GST at 10%.
 *
 * @param {number} exclusiveAmount The ex-GST dollar amount (AUD).
 * @return {number} The GST-inclusive price, rounded to two decimal places.
 * @customfunction
 */
function GST_INCLUSIVE(exclusiveAmount) {
  if (typeof exclusiveAmount !== 'number' || exclusiveAmount < 0) return 0;
  return Math.round(exclusiveAmount * 1.1 * 100) / 100;
}

In a spreadsheet with a column of GST-inclusive invoice amounts in column B, you can now add:

=GST_COMPONENT(B2)       → Returns the GST portion
=B2 - GST_COMPONENT(B2)  → Returns the ex-GST amount
=GST_INCLUSIVE(C2)       → Converts an ex-GST price to GST-inclusive

The Math.round(...* 100) / 100 pattern ensures results are always rounded to two decimal places — no more cells showing $45.999999999 due to floating-point arithmetic.

Practical Example 2: ABN Validator

The Australian Business Number (ABN) has a checksum algorithm defined by the Australian Business Register. Validating an ABN before it hits your CRM or invoice system prevents downstream data quality problems.

The ABN algorithm works as follows: subtract 1 from the first digit, multiply each digit by a weighting factor, sum the products, and check whether the total is divisible by 89.

/**
 * Validates an Australian Business Number (ABN) using the
 * official checksum algorithm from the Australian Business Register.
 *
 * @param {string|number} abn The ABN to validate (11 digits, spaces and hyphens are stripped).
 * @return {string} "Valid" if the ABN passes the checksum, "Invalid" otherwise.
 * @customfunction
 */
function VALIDATE_ABN(abn) {
  if (abn === '' || abn === null || abn === undefined) return 'Invalid';

  // Strip whitespace and hyphens, convert to string
  var cleaned = abn.toString().replace(/[\s\-]/g, '');

  // ABN must be exactly 11 digits
  if (!/^\d{11}$/.test(cleaned)) return 'Invalid';

  // ABN weighting factors (defined by the Australian Business Register)
  var weights = [10, 1, 3, 5, 7, 9, 11, 13, 15, 17, 19];

  // Subtract 1 from the first digit
  var digits = cleaned.split('').map(Number);
  digits[0] -= 1;

  // Calculate weighted sum
  var sum = 0;
  for (var i = 0; i < weights.length; i++) {
    sum += digits[i] * weights[i];
  }

  // Valid ABN: sum is divisible by 89
  return (sum % 89 === 0) ? 'Valid' : 'Invalid';
}

Use it in a spreadsheet column of ABNs:

=VALIDATE_ABN(A2)

You can combine this with conditional formatting: highlight cells red where =VALIDATE_ABN(A2) = "Invalid" to make invalid entries immediately visible to data entry staff.

You can extend the function to also return TRUE/FALSE instead of a string, making it easy to use inside =IF() or =FILTER():

// Usage in a formula:
// =IF(VALIDATE_ABN(A2) = "Valid", "Process invoice", "Check ABN before proceeding")

Practical Example 3: Custom Date Formatter for Australian Format

Australian date conventions use DD/MM/YYYY, but Google Sheets internally stores dates as serial numbers and often displays or exports them in a variety of formats depending on locale settings, import source, or formula context. A custom function gives you a single, reliable way to format any date as a proper Australian date string.

/**
 * Formats a date value in Australian DD/MM/YYYY format.
 * Handles both Date objects passed by Sheets and date serial numbers.
 *
 * @param {Date|number|string} dateValue The date to format. Can be a cell reference
 *     containing a date, a date serial number, or a date string.
 * @return {string} The date formatted as DD/MM/YYYY, or an empty string if invalid.
 * @customfunction
 */
function FORMAT_AU_DATE(dateValue) {
  if (!dateValue) return '';

  var date;

  // Apps Script passes Date objects when a Sheets date cell is referenced
  if (dateValue instanceof Date) {
    date = dateValue;
  } else if (typeof dateValue === 'number') {
    // Sheets date serial: convert via the Apps Script epoch (30 Dec 1899)
    date = new Date((dateValue - 25569) * 86400000);
  } else {
    date = new Date(dateValue);
  }

  // Validate the resulting date
  if (isNaN(date.getTime())) return '';

  var day   = String(date.getDate()).padStart(2, '0');
  var month = String(date.getMonth() + 1).padStart(2, '0');
  var year  = date.getFullYear();

  return day + '/' + month + '/' + year;
}

In the spreadsheet:

=FORMAT_AU_DATE(B2)                → Returns "19/02/2026" from a date value in B2
=FORMAT_AU_DATE(TODAY())           → Returns today's date in AU format
="Invoice date: " & FORMAT_AU_DATE(C2)  → Concatenates into a readable label

The padStart(2, '0') call ensures single-digit days and months are zero-padded correctly (08/03/2026 rather than 8/3/2026), which matters for consistent sorting and display in reports.

Deploying and Sharing Your Custom Functions

Custom functions written in a bound script (accessed via Extensions > Apps Script from within the spreadsheet) are available only within that specific spreadsheet. Anyone the spreadsheet is shared with can use the formulas — they do not need to authorise the script themselves for custom functions, as they only return values rather than making privileged API calls.

If you want the same custom functions available across many spreadsheets, there are two approaches:

Copy the script to each spreadsheet. This is the simplest approach. Open the new spreadsheet's script editor and paste the functions in. They are immediately available. The downside is that updates need to be applied to each copy separately.

Publish as a Workspace Add-on. If your organisation needs the same functions across hundreds of spreadsheets, you can package the functions as an internal Google Workspace Add-on and deploy it organisation-wide through the Admin console. All users in your domain then have the functions available in every spreadsheet they open, and updates are applied centrally. This requires Workspace admin access and takes more setup, but it is the right approach for team-wide tooling.

For most Australian SMBs, the copy-and-paste approach is sufficient. Reserve the add-on deployment path for functions that your entire organisation depends on and that you will need to maintain and update over time.

Affiliate & Partner Programs

If your team is ready to make the most of Google Sheets, Apps Script, and the broader Google Workspace productivity suite, here are the relevant resources and referral programmes:

• Google Workspace — Apps Script and custom functions are available on every Google Workspace plan at no additional cost. Business Starter plans start from approximately AUD $10.80 per user per month, with Business Standard (including Gemini AI features) from approximately AUD $16.80 per user per month. Explore current Australian pricing and start a trial via the official referral link: Google Workspace — Cloud Geeks Referral
• Google Cloud Platform — If you plan to extend your custom functions into more advanced Apps Script projects linked to a Google Cloud project (for Secret Manager, Cloud Logging, or external API access), GCP offers a generous free tier. The first 90 days include AUD $450 in free credits: Google Cloud Free Trial

Using the referral link above supports the production of practical, independent content at insights.cloudgeeks.com.au at no additional cost to you.

Conclusion

Custom functions in Google Sheets are one of the highest-value things you can add to a spreadsheet used by a real team. They take logic that would otherwise live in messy nested formulas or require manual calculation and turn it into a clean, named, reusable function that anyone on the team can call without understanding the code behind it.

The GST calculator, ABN validator, and AU date formatter in this tutorial are immediately useful for most Australian businesses. They are also templates for the pattern: identify a calculation your team performs repeatedly, encode it once in Apps Script, document it with JSDoc so it behaves like a built-in formula, and deploy it to the spreadsheets that need it.

The limitations — no external API calls, a 30-second timeout, restricted service access — are real but rarely get in the way of practical custom functions. Most business calculations are pure logic: input in, result out, no network calls required.

Start with one function that solves a specific problem in your workflow. Write it, test it on a small range, document it with JSDoc annotations, and put it into use. Once your team is calling a formula you wrote as if it were built into Sheets, you will wonder why you did not start sooner.

Need help with your Google Workspace setup? Contact our team for a free consultation.