Custom Functions

Getting Started

Custom functions allow you to write JavaScript code that can be called from spreadsheet cells, just like built-in functions. Use JSDoc comments to document your functions - the name, description, and parameters will be automatically extracted.

JSDoc Syntax

Your functions must include JSDoc comments with specific tags for proper recognition:

• @param {type} name Description - Document each parameter
• @return Description - Document what the function returns
• Function names must be UPPERCASE and contain only letters, numbers, and underscores

Examples

Basic Function

A simple function that adds two numbers together:

/**
 * Adds two numbers together.
 * @param {number} a The first number.
 * @param {number} b The second number.
 * @return The sum of a and b.
 */
function ADD_CUSTOM(a, b) {
  return a + b;
}

Function with Optional Parameter

A function that formats a number with optional decimal places:

/**
 * Formats a number with decimal places.
 * @param {number} value The number to format.
 * @param {number} decimals Decimal places (optional).
 * @return The formatted number as string.
 */
function FORMAT_NUMBER(value, decimals = 2) {
  return value.toFixed(decimals);
}

String Processing Function

A function that converts text to title case:

/**
 * Converts text to title case.
 * @param {string} text The text to convert.
 * @return The text in title case.
 */
function TITLE_CASE(text) {
  return text.toLowerCase().replace(/\b\w/g, l => l.toUpperCase());
}

Array Processing Function

A function that calculates the average of an array of numbers:

/**
 * Calculates the average of an array of numbers.
 * @param {number[]} numbers Array of numbers to average.
 * @return The average of the numbers.
 */
function AVERAGE_CUSTOM(numbers) {
  if (!Array.isArray(numbers) || numbers.length === 0) {
    return 0;
  }
  const sum = numbers.reduce((acc, num) => acc + num, 0);
  return sum / numbers.length;
}

Date Processing Function

A function that formats dates in a custom way:

/**
 * Formats a date as YYYY-MM-DD.
 * @param {Date} date The date to format.
 * @return The formatted date string.
 */
function FORMAT_DATE(date) {
  const d = new Date(date);
  const year = d.getFullYear();
  const month = String(d.getMonth() + 1).padStart(2, '0');
  const day = String(d.getDate()).padStart(2, '0');
  return `${year}-${month}-${day}`;
}

Best Practices

• Use descriptive names: Function names should clearly indicate what they do
• Document thoroughly: Include clear descriptions for the function and all parameters
• Handle edge cases: Check for null, undefined, or invalid inputs
• Keep it simple: Functions should do one thing well
• Test your functions: Use the Test Function button to verify behavior

Limitations

• Functions run in a sandboxed environment for security
• No access to external APIs or file system
• Limited to JavaScript standard library functions
• Function execution has timeout limits

Using Custom Functions

Once created, custom functions can be used in spreadsheet cells just like built-in functions:

=ADD_CUSTOM(5, 3)
=FORMAT_NUMBER(3.14159, 2)
=TITLE_CASE("hello world")