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

Virtualization and Off-Screen Cells

GridSheet is virtualized: only the cells in the visible range are solved. This keeps large sheets fast, but it means an off-screen async cell does not fire until you scroll it into view. When you need every row resolved (for example to serialize the whole sheet, or to drive an export), use eager resolution:

• Set options.eager to true on a <GridSheet> to fire every async cell after each update, regardless of the visible range.
• Or call sheet.resolveAll() on demand (e.g. right before saving), then await sheet.waitForPending().

Only cells that call an async function are fired — sync formulas resolve on read — and already-resolved cells are not re-fired, so it stays cheap. Eager resolution is opt-in because firing every off-screen async cell can be expensive (side effects, API cost).

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

1. Always Provide Help Documentation

   • Use description and defs to clearly describe your function
   • This helps users understand when and how to use your formulas

2. Handle Errors Gracefully

   • Wrap async operations in try-catch blocks
   • Return meaningful error indicators
   • Log errors for debugging

3. Consider Performance

   • Avoid unnecessary API calls
   • Implement caching where appropriate
   • Set reasonable timeouts for async operations

4. Type Your Arguments

   • Use TypeScript types in your main() method signature
   • This provides better IDE support and error catching

5. Test Thoroughly

   • Test both success and error scenarios
   • Verify async functions work correctly in filtering/sorting contexts
   • Ensure error messages are user-friendly

6. 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