Real-time data

Display real-time data, by subscribing to Websockets to REST API

Learn more about Custom formulas

Display static data from REST API

Formula functions are asynchronous by default. An example would be to get Crypto prices from Gemini

cryptoprice.ts

import { FunctionArgument } from "@rowsncolumns/calculator";
import type FormulaParser from "@rowsncolumns/fast-formula-parser";
import FormulaError from "@rowsncolumns/fast-formula-parser/formulas/error";

// Usage: 
// =CRYPTOPRICE("btcusd")
export const CRYPTOPRICE = async (
  parser: FormulaParser,
  arg: FunctionArgument
) => {
  if (!arg || !arg.value) {
    throw new FormulaError("#VALUE!", "Symbol pair is required");
  }
  
  // Get data from GEMINI
  const fetchPrices = async () => {
    try {
      const results = await fetch(
        `https://api.gemini.com/v2/ticker/${String(arg.value).toLowerCase()}`,
        {
          method: "GET",
        }
      );
      const values = await results.json();
      return [[Number(values.ask), Number(values.bid)]];
    } catch (err) {}
  };  

  return await fetchPrices();
};

As crypto prices change every millisecond, we need to ability to update this data. But currently there is no way to do that, as formula functions are stateless.

To achieve this, you can use calculationPipeline hook.

Subscribing to REST API

calculationPipeline hook contains a callback and it should return an unsubscribe function.

Above REST API can be written as below, so that we can poll the API every 5 seconds.

import { FunctionArgument, calculationPipeline } from "@rowsncolumns/calculator";
import type FormulaParser from "@rowsncolumns/fast-formula-parser";
import FormulaError from "@rowsncolumns/fast-formula-parser/formulas/error";

export const CRYPTOPRICE = async (
  parser: FormulaParser,
  arg: FunctionArgument
) => {
  if (!arg || !arg.value) {
    throw new FormulaError("#VALUE!", "Symbol pair is required");
  }

  const fetchPrices = async () => {
    try {
      const results = await fetch(
        `https://api.gemini.com/v2/ticker/${String(arg.value).toLowerCase()}`,
        {
          method: "GET",
        }
      );
      const values = await results.json();
      return [[Number(values.ask), Number(values.bid)]];
    } catch (err) {}
  };

  
  // Execute in isolated environment
  calculationPipeline(parser, (onUpdate) => {
    const timeout = setInterval(async () => {
      const values = await fetchPrices();
      if (values !== undefined) {
        onUpdate(values);
      }
    }, 5000);
    
    // Cleanup function
    return () => {
      clearInterval(timeout);
    };
  });

  return await fetchPrices();
};

Subscribing to Websocket

Using calculationPipeline hook, we can connect to Websocket and subscribe to streaming data.

For performance reasons, use throttling to prevent unnecessary Spreadsheet update

export const CRYPTOPRICE = (parser: FormulaParser, arg: FunctionArgument) => {
  if (!arg || !arg.value) {
    throw new FormulaError("#VALUE!", "Websocket URL is required");
  }
  
  calculationPipeline(parser, (onUpdate) => {
    let websocket = new WebSocket(arg.url)
    websocket.addEventListener('message', (event) => {
      onUpdate(event.data)
    })
    
    // Unsubscriber
    return () => {
      websocket.close()
    };
  });
  
  return `Connecting to websocket`
}

Formula lifecycle or calculationPipeline hook is called when selections/cells are moved or deleted, or copy pasted to another location.

It is advisable to use rxjs subscription for websocket updates, so that you can subscribe and unsubscribe to a subject upon disconnect.

Web workers

With calculationPipeline , you can choose to run your code in a web worker. Initialise a single web worker or multiple web workers (if user enters same formula, calculationPipeline will be invoked)

// Initialize a worker when Spreadsheet is loaded
// You can also initialize this in calculationPipeline, but you wouldnt
// want to create 1 worker per formula
const cryptoWorker = new Worker('./worker.js')

export const CRYPTOPRICE = (parser: FormulaParser, arg: FunctionArgument) => {
  if (!arg || !arg.value) {
    throw new FormulaError("#VALUE!", "Websocket URL is required");
  }
  
  calculationPipeline(parser, (onUpdate) => {
    cryptoWorker.postMessage({
      type: 'subscribe',
      symbol: arg.value
    })
    
    cryptoWorker.onmessage = (event) => {
      if (event.symbol === arg.value) {
        onUpdate(event.data)
      }
    }
    
    return () => {
      cryptoWorker.postMessage({
        type: 'unsubscribe',
        symbol: arg.value
      })
    }
  });
  
  return `Connecting to websocket`
}

Cancelling async formula evaluation when dependencies change

AbortController is used to cancel long running formula when dependencies change, and trigger a new formula evaluation

import type FormulaParser from "@rowsncolumns/fast-formula-parser";

export const BACK_END_API = (parser: FormulaParser) => {    
    const controller = new AbortController();
    const signal = controller.signal;

    // Listen to abort signals
    // When cell dependencies change and this formula is still running
    // Abort signal is emitted for users to abort this request
    parser.position?.signal.addEventListener('abort', () => {
        // Abort the fetch request
        controller.abort()
    })
    
    
    const request = await fetch('/url', { signal })
    const data = await request.json()
    
    return [ data.value ]
}