Formula System

GridSheet supports a comprehensive formula system with both synchronous and asynchronous function execution. This guide covers how to create, use, and manage custom formulas.

Overview

The formula system in GridSheet is built on a flexible architecture that allows developers to:

Create custom formula functions
Execute synchronous and asynchronous operations
Integrate external APIs and data sources
Define custom validation and error handling
Provide help text and documentation for users

Creating Custom Formulas

All custom formulas extend the BaseFunction class. Here's the basic structure:

import { BaseFunction, FunctionArgumentDefinition } from '@gridsheet/react-core';
 
class MyCustomFunction extends BaseFunction {
  // Display name and example usage
  example = 'MY_FUNC(arg1, arg2)';
  
  // Optional: description of what this function does
  description = 'Description of what this function does';
  
  // Argument documentation (also drives arg-count validation)
  defs: FunctionArgumentDefinition[] = [
    { name: 'arg1', description: 'Description of first argument', acceptedTypes: ['string'] },
    { name: 'arg2', description: 'Description of second argument', acceptedTypes: ['number'] }
  ];
 
  // The main function logic
  protected main(stringArg: string, numberArg: number) {
    // Your logic here
    return result;
  }
}

Synchronous Formulas

Synchronous formulas execute immediately and return a result synchronously.

Example: Simple Calculator

class Add extends BaseFunction {
  example = 'ADD(a, b)';
  description = 'Adds two numbers';
  defs = [
    { name: 'a', description: 'First number', acceptedTypes: ['number'] },
    { name: 'b', description: 'Second number', acceptedTypes: ['number'] }
  ];
 
  protected main(a: number, b: number) {
    return a + b;
  }
}

Example: String Manipulation

class Concatenate extends BaseFunction {
  example = 'CONCAT(str1, str2)';
  description = 'Concatenates two strings';
  defs: FunctionArgumentDefinition[] = [
    { name: 'str1', description: 'First string', acceptedTypes: ['string'] },
    { name: 'str2', description: 'Second string', acceptedTypes: ['string'] }
  ];
 
  protected main(str1: string, str2: string) {
    return `${str1}${str2}`;
  }
}

Asynchronous Formulas

Asynchronous formulas allow you to fetch data from external APIs, perform database queries, or execute any async operation. The framework automatically handles Promise resolution.

Creating an Async Formula

Extend BaseFunctionAsync and make the main() method async:

class FetchWeather extends BaseFunctionAsync {
  example = 'FETCH_WEATHER("Tokyo")';
  description = 'Fetches current weather data for a city';
  defs: FunctionArgumentDefinition[] = [
    { name: 'city', description: 'City name to fetch weather for' }
  ];
 
  async main(city: string) {
    const response = await fetch(`https://api.weather.example.com/city=${city}`);
    const data = await response.json();
    return data.temperature;
  }
}

Example: API Integration

class GetUserData extends BaseFunctionAsync {
  example = 'GET_USER("user123")';
  description = 'Fetches user information from API';
  defs: FunctionArgumentDefinition[] = [
    { name: 'userId', description: 'The user ID to fetch', acceptedTypes: ['string'] }
  ];
 
  async main(userId: string) {
    try {
      const response = await fetch(`https://api.example.com/users/${userId}`);
      if (!response.ok) {
        throw new Error(`HTTP error! status: ${response.status}`);
      }
      const userData = await response.json();
      return userData.name || 'Unknown';
    } catch (error) {
      console.error('Error fetching user:', error);
      return 'Error';
    }
  }
}

Async Function Behavior

When async formulas are used in the spreadsheet:

Evaluation

Cells containing async formulas display a loading indicator during evaluation
The framework automatically awaits the Promise and updates the cell value when complete
If an error occurs, the error is caught and displayed appropriately

Filtering and Sorting

When filtering or sorting data containing async formulas, the framework waits for all async computations to complete
This ensures consistent and predictable filtering/sorting results
The UI remains responsive during the wait period

Performance Considerations

Use async formulas judiciously, as each async operation adds latency
Consider caching results when appropriate to reduce unnecessary API calls
Implement timeout logic for long-running operations
Handle errors gracefully to provide better user experience

Error Handling

Proper error handling is important for both sync and async formulas:

class SafeAsyncFunction extends BaseFunctionAsync {
  example = 'SAFE_FUNC(url)';
  description = 'Safely fetches data with error handling';
  defs = [
    { name: 'url', description: 'URL to fetch', acceptedTypes: ['string'] }
  ];
 
  async main(url: string) {
    try {
      const response = await fetch(url);
      if (!response.ok) {
        throw new Error(`HTTP Error: ${response.status}`);
      }
      const data = await response.json();
      return data.value;
    } catch (error) {
      console.error('Fetch error:', error);
      return '#ERROR'; // Indicate error state
    }
  }
}

Registering Custom Formulas

Custom formulas are registered through the useBook hook via the additionalFunctions option. Keys are the formula names (case-insensitive in formulas):

import { useBook } from '@gridsheet/react-core';
import MyCustomFunction from './MyCustomFunction';
import FetchWeather from './FetchWeather';
 
const book = useBook({
  additionalFunctions: {
    my_func: MyCustomFunction,
    fetch_weather: FetchWeather,
  },
});

If you also want the full set of built-in extended functions, use useSpellbook instead — it wraps useBook with all extended functions pre-loaded.

Note: useSpellbook requires @gridsheet/functions to be installed. It is an optional peer dependency of @gridsheet/react-core.

import { useSpellbook } from '@gridsheet/react-core/spellbook';
 
const book = useSpellbook({
  additionalFunctions: {
    my_func: MyCustomFunction,
    fetch_weather: FetchWeather,
  },
});

Best Practices

Always Provide Help Documentation
- Use description and defs to clearly describe your function
- This helps users understand when and how to use your formulas
Handle Errors Gracefully
- Wrap async operations in try-catch blocks
- Return meaningful error indicators
- Log errors for debugging
Consider Performance
- Avoid unnecessary API calls
- Implement caching where appropriate
- Set reasonable timeouts for async operations
Type Your Arguments
- Use TypeScript types in your main() method signature
- This provides better IDE support and error catching
Test Thoroughly
- Test both success and error scenarios
- Verify async functions work correctly in filtering/sorting contexts
- Ensure error messages are user-friendly
Document Examples
- Provide clear examples in the formula example property
- Help users understand the expected input/output format

Type Definitions

BaseFunction

class BaseFunction {
  // Formula example shown in autocomplete
  example?: string;
 
  // Short description of the function
  description?: string;
 
  // Function category for grouping in autocomplete
  category: FunctionCategory = 'other';
 
  // Argument definitions (drives arg-count and type validation)
  defs: FunctionArgumentDefinition[] = [];
 
  // Cache TTL in milliseconds (async only). undefined = never expires.
  protected ttlMilliseconds?: number;
 
  // Hash segments in cache key. Higher = lower collision risk. Default: 1
  protected hashPrecision: number;
 
  // If true, reuse the same in-flight Promise for matching cache keys. Default: true
  protected useInflight: boolean;
 
  // If true, broadcasting is unconditionally disabled for this function. Default: false
  protected broadcastDisabled: boolean;
 
  // If true, wraps return value in a Spilling sentinel for spill behaviour. Default: false
  protected autoSpilling: boolean;
 
  // Main function implementation
  protected main(...args: any[]): any;
 
  // Optional: override to customise argument coercion before main() is called
  protected validate(args: any[]): any[];
}
 
type FunctionCategory =
  | 'math' | 'statistics' | 'text' | 'time' | 'lookup'
  | 'information' | 'finance' | 'engineering' | 'logical' | 'other';
 
type FunctionArgumentDefinition = {
  name: string;
  description: string;
  optional?: boolean;      // allows fewer args than defs.length
  variadic?: boolean;      // last matching def receives all remaining args
  nullable?: boolean;      // blank treated as 0 / "" etc. (default: true)
  errorTolerant?: boolean; // forward FormulaError values instead of throwing
  acceptedTypes?: FunctionArgumentType[]; // runtime type check
  takesMatrix?: boolean;   // suppresses broadcasting for this arg
};
 
type FunctionArgumentType =
  | 'number' | 'string' | 'boolean'
  | 'date' | 'time' | 'matrix' | 'reference' | 'any';

`ttlMilliseconds`

Controls how long the async formula result is cached per cell.

Value	Behavior
`undefined` (default)	Cache never expires — result is reused until inputs change
`number`	Cache expires after the specified milliseconds

class MyApiFunction extends BaseFunctionAsync {
  ttlMilliseconds = 60 * 1000; // Cache for 1 minute
 
  async main(id: string) {
    const res = await fetch(`/api/data/${id}`);
    return res.json();
  }
}

`hashPrecision`

Controls the number of hash segments included in the cache key. Each segment is a cyrb53 hash computed with a different seed, joined by :.

Value	Cache key format	Collision probability
`1` (default)	`FUNC:length:h0`	~1 in 9,000 trillion — sufficient for spreadsheet use
`2`	`FUNC:length:h0-h1`	~1 in 81,000 trillion trillion — virtually zero

Hash segments are base-36 encoded (digits 0-9 + letters a-z), up to 11 characters each.

In practice, 1 is sufficient since collisions only cause a stale cache hit (not data corruption), and the cache key also includes the function name and argument length as additional discrimination.

class MyApiFunction extends BaseFunctionAsync {
  hashPrecision = 2; // Use 2 hash segments for extra safety
 
  async main(data: string) { /* ... */ }
}

Limitations and Considerations

Async formulas are evaluated on-demand (when viewed or during filter/sort operations)
Very large datasets with many async formulas may impact performance
Consider implementing caching mechanisms for frequently accessed data
Browser security restrictions apply to API calls (CORS, etc.)
Network errors should be handled gracefully

Related Resources

Migration Guide: v2 to v3 - Information about async formula introduction
Table API Reference - Methods for reading and writing cell data
Example: case11 - Practical async formula examples

Sheet Class Policy System