The logic stays in the spreadsheet. The data comes from your system.
Live data turns a spreadsheet with external data into an API: you name a web address that returns your price list, tariffs or rates as JSON, and the formulas of your workbook read it like an Excel table. Every calculation uses current records, so an Excel API with live data needs no one to copy prices into the file.
The ERP writes the price list. The calculator looks it up.
Your ERP drops the current price list as a JSON file every night, or on every change. Every time the quote calculator runs, it looks up the price in that list. Nobody maintains prices inside the Excel file.
The ERP writes JSON
A list of records behind a public https address.
[
{ "tier": "Starter", "price": 19 },
{ "tier": "Business", "price": 45 },
{ "tier": "Enterprise", "price": 82 }
]A formula looks it up
The data source has a name; formulas read its columns like those of an Excel table.
=XLOOKUP(B1,Prices[tier],Prices[price])
The answer has today's price
The answer names the data sources it used. Callers never see the address.
"outputs": [
{ "name": "price", "value": 45 }
],
"metadata": {
"dataSources": ["Prices"]
}A drawn example: a price list with three records, a lookup, and the part of the answer that carries the result.
Set up in three steps.
Name the address
Click the cloud icon in the workbook's toolbar and add a data source: a name such as Prices and the address. Load shows the number of records, the columns and the first records.
Write the formulas
The workbook gets a table of that name with one column per field. XLOOKUP, SUM or FILTER read it like any Excel table.
Save and publish
From then on the published service reads the address itself. When the data changes, you do not import anything and you do not publish again.
Three examples of data that lives elsewhere.
A price list from the ERP
The ERP publishes article prices as JSON. The quote workbook looks up the price and applies its own discount tiers and rounding.
Tariffs from a business application
The system that owns the tariffs returns them as a list. The rating formulas stay in the spreadsheet and read the current tariff.
Exchange rates
An address returns today's rates. The workbook converts an offer into the customer's currency with the rate it finds there.
How fresh the data is.
A published service reads the address when a calculation needs it and reuses the records for the cache duration you choose. After that time the next calculation reads the address again.
- A caller who needs the current records at once sends nocache=true: that call reads the source.
- No republish is needed when the data changes, only when you change the workbook or its settings.
Limits, said plainly.
Limits
- 5 data sources per workbook
- Up to 2 MB per data source
- Up to 10,000 records and 100 columns
- The address must answer within 5 seconds
- A public https address that returns a JSON array of records, or an object with the list in data, items or value
Not included
- Sign-ins or request headers for the source
- Writing back to your system
- CSV or XML
- Addresses in private networks
- Automatic pick-up of new fields: the columns are fixed when the source is added
- In the editor your browser reads the address, so the source must allow that (CORS). The published service does not need it.
When a source cannot be read
The call fails with a message that names the data source, instead of returning wrong numbers. Callers never see the address of the source.
The price list comes from your system
Your service reads the current price list from a web address. When a price changes, the next call calculates with it – no republish needed.
Keep the prices where they are maintained.
Publish the workbook, name the address, and every call calculates with current records.