Live data from a data source

Updated 2026-11-13

How a service calculates with data from your own system: a price list, tariffs, exchange rates. The logic stays in the spreadsheet, the data comes from a web address you name, and every calculation uses the current records.

What a data source is

A data source is a JSON list behind a public https address, for example the price list your shop or ERP system publishes. You give it a name. The workbook then has a table of that name, with one column per field of the records, and a tab of the same name that shows the records.

Formulas read the table like an Excel table:

=XLOOKUP(B1, Prices[tier], Prices[price])
=SUM(Prices[price])
=FILTER(Prices[tier], Prices[price]<50)

When the list changes in your system, the service calculates with the new records. You do not import anything again and you do not publish again.

What the address must return

A JSON array of records, each record an object with one value per column:

[
  { "tier": "Starter", "price": 19 },
  { "tier": "Business", "price": 45 },
  { "tier": "Enterprise", "price": 82 }
]

An object that holds the list in data, items or value is read as well, for example { "data": [ … ] } or an OData answer. A value that is itself an object or a list arrives in the cell as its JSON text.

Add a data source

  1. Open the service and click the cloud icon Data sources (Datenquellen) in the toolbar above the workbook. The same list is under the gear icon Settings (Einstellungen) → Data sources.
  2. Click Add data source (Datenquelle hinzufügen).
  3. Enter a Name, for example Prices. It starts with a letter and has only letters, digits and underscores. This is the name your formulas use.
  4. Enter the Address (Adresse), for example https://example.com/api/prices.json.
  5. Click Load (Laden). You see the number of records, the columns and the first records, read the same way a published service reads them.
  6. Click Add to workbook (Zur Arbeitsmappe hinzufügen). The workbook gets the table and a tab with its name. You stay on the sheet you were working on.
  7. Click Done (Fertig) and write your formulas, for example =XLOOKUP(B1,Prices[tier],Prices[price]). While you type, the list of names shows the data source as Data source Prices (Datenquelle Prices).
  8. Click Save (Speichern), then publish. See Publish, republish and unpublish.

A workbook that already contains such a data connection, for example a file saved with a remote table, keeps it when you save and publish.

Refresh, rename, remove

Each data source in the list has three buttons:

  • Refresh (Aktualisieren) reads the address again, recalculates the open workbook and fits the columns of the tab to the new records.
  • Rename (Umbenennen) changes the name. Formulas that use it are adjusted.
  • Remove (Entfernen) deletes the table and its tab. Formulas that read it show an error afterwards.

Click Save after a change, and publish again so callers get it.

How fresh the data is

A published service reads the address when a calculation needs it and reuses the records for the Cache duration: (Cache-Dauer) under Settings → Performance: 1, 5, 15, 30 minutes or 1 hour, 5 minutes by default. 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 address.
  • A test under API → Test always reads the address, because the tester sends nocache=true with its own calls. See The API tester.
  • With Cache data from data sources (Daten aus Datenquellen cachen) switched off, every calculation reads the address.
  • An identical call is answered from a stored result only while the records it was calculated with are unchanged. A stored result never outlives the cache duration.

Changes to these settings reach callers after Save and Republish (Neu veröffentlichen). See Service settings.

In the editor

The tab of a data source shows its records and is read-only: you cannot type into it, add a record or delete one. A lock in each column header marks this, and the toolbar above says read-only, the records come from its address (nur lesbar, die Datensätze kommen von ihrer Adresse). The formatting buttons are greyed out while this tab is open. Change the records in your own system, then click Refresh.

The editor reads the address from your browser. For that, your data source must allow requests from other websites (CORS). If it does not, the editor shows no records and the formulas show an error there, and a notice says so. The published service is not affected: it reads the address from SpreadAPI's servers.

Limits

  • At most 5 data sources per workbook.
  • Per data source: an answer of at most 2 MB, at most 10,000 records and 100 columns.
  • The address must answer within 5 seconds. At most three redirects are followed.
  • Only public https addresses. Addresses in private or internal networks are refused.
  • Reading only, with a plain request. Request headers, sign-ins and writing back are not available.
  • The columns are the fields the list had when you added the data source.
  • A calculation with a data source counts as one call, like any other.

The address is stored in the workbook. Whoever has the workbook file can read it, so use an address you would hand to a colleague, and keep a key in the address only if that is acceptable for you. Callers of the service do not see the address: answers name the data source only.

When the data source cannot be read

The call fails instead of calculating with missing data. The answer has HTTP status 400, "error": "Live data could not be loaded" and a message that names the data source and the reason, for example Data source "Prices" could not be read: the address answered with status 503. While records are still within the cache duration, calls keep working with those.

For developers and agents

  • The answer of a call lists the names of the data sources it used in metadata.dataSources, for example ["Prices"].
  • nocache=true reads every data source of the service for that call and stores the new records for the calls that follow.
  • With Allow Excel export on, the exported file contains the records as a table. It does not contain the address.
  • An agent connected to the account adds data sources with create_service (data_sources, together with sheets) and changes them with update_service (add_data_source, remove_data_source). A data source that cannot be read answers data_source_unavailable. See Agent door overview.
  • A record is a flat JSON object. Field names become column names as they are; use names without spaces so formulas stay readable.

FAQ

Can my spreadsheet calculate with data from my own system?

Yes. Add a data source with the cloud icon in the workbook's toolbar (or under Settings → Data sources): a name and a public https address that returns a JSON list. Formulas then read it by that name, for example =XLOOKUP(B1,Prices[tier],Prices[price]).

What format must my data have?

A JSON array of records, each an object with one value per column. An object with the list in data, items or value works too.

How do I use the data in a formula?

Like an Excel table: the name of the data source, then the column in square brackets. Prices[price] is the column price of the data source Prices.

How current is the data my service calculates with?

At most as old as the Cache duration: under Settings → Performance, 5 minutes by default. A call with nocache=true reads the data source at once.

Do I have to publish again when my data changes?

No. The published service reads the address itself. You publish again only when you change the workbook or the settings.

What happens when my data source is down?

Calls work with the stored records until the cache duration ends. After that a call answers Live data could not be loaded with a message that names the data source, until the address answers again.

Why does the editor show no records although "Load" worked?

Load reads the address from SpreadAPI's servers, the editor reads it from your browser. Your data source must allow requests from other websites (CORS) for the editor to show records. The published service works either way.

Can I send an API key or a sign-in to my data source?

Request headers and sign-ins are not available. The address is requested as it is, and it is stored in the workbook.

Can callers of my service see the address of my data source?

No. Answers name the data source only. The address is in the workbook, so people who get the workbook file from you can read it.

How many data sources can a service have?

Up to 5 per workbook, each with at most 2 MB, 10,000 records and 100 columns.

Why is my address refused?

Only public https addresses are read. An address without https, or one that leads into a private or internal network, is refused, also when a redirect leads there.

As an agent, how do I give a service live data?

Send data_sources with create_service, for example [{"name": "Prices", "url": "https://example.com/prices.json"}], together with sheets whose formulas use Prices[…]. For an existing draft use add_data_source and remove_data_source of update_service.