Comment on page
Real-time data
Display real-time data, by subscribing to Websockets to 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.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();
};
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.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`
}
Last modified 7mo ago