Examples
Copy a formula into a cell, press Enter and the result fills the cells below and to the right. All of these use public APIs that need no key. Public APIs change, so if one stops working, the error message says why.
Latest issues of a GitHub repository
API: GitHub REST API
=IMPORTJSON("https://api.github.com/repos/vercel/next.js/issues?per_page=30", "/number,/title,/user/login,/created_at")With Spanish or European settings (semicolon as separator):
=IMPORTJSON("https://api.github.com/repos/vercel/next.js/issues?per_page=30"; "/number,/title,/user/login,/created_at")One row per issue or pull request, with its number, title, author and creation date. The query keeps only those four fields out of the dozens the API returns. Paths match by prefix, so /state would also bring /state_reason: pick names that no other field starts with.
GitHub allows few anonymous requests per hour, and Google shares its servers' addresses between many users. If you hit the limit, add a personal access token as Bearer authentication in the request builder, and use a larger cache.
Current weather for a city
API: Open-Meteo
=IMPORTJSON("https://api.open-meteo.com/v1/forecast?latitude=41.39&longitude=2.17¤t=temperature_2m,wind_speed_10m", "/current/time,/current/temperature_2m,/current/wind_speed_10m")With Spanish or European settings (semicolon as separator):
=IMPORTJSON("https://api.open-meteo.com/v1/forecast?latitude=41.39&longitude=2.17¤t=temperature_2m,wind_speed_10m"; "/current/time,/current/temperature_2m,/current/wind_speed_10m")A single row with the time of the reading, the temperature (°C) and the wind speed (km/h) for the coordinates in the URL.
Change latitude and longitude for your city. The data refreshes every 15 minutes at the source, so a cache of a few minutes is more than enough.
Top cryptocurrencies by market cap
API: CoinGecko
=IMPORTJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=eur&order=market_cap_desc&per_page=10", "/market_cap_rank,/name,/current_price", "cache=600")With Spanish or European settings (semicolon as separator):
=IMPORTJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=eur&order=market_cap_desc&per_page=10"; "/market_cap_rank,/name,/current_price"; "cache=600")Ten rows: rank, name and current price in euros. The option cache=600 reuses the response for 10 minutes, which keeps you far from the API's free rate limit.
Free public endpoints change their limits from time to time. If you see HTTP 429, wait a minute or raise the cache value.
Filter records with JSONPath
API: JSONPlaceholder
=IMPORTJSONPATH("https://jsonplaceholder.typicode.com/todos", "$[?(@.completed==false)]")With Spanish or European settings (semicolon as separator):
=IMPORTJSONPATH("https://jsonplaceholder.typicode.com/todos"; "$[?(@.completed==false)]")Only the to-dos that are not completed, with one column per field (userId, id, title, completed). Use IMPORTJSONPATH when you need a condition, not just a list of fields.
Operators in filters: ==, !=, <, >, <= and >=. Strings go in single or double quotes, as in $[?(@.title=='x')].
Exchange rates in one row
API: Frankfurter (European Central Bank rates)
=IMPORTJSONPATH("https://api.frankfurter.dev/v1/latest?base=EUR&symbols=USD,GBP", "$.rates")With Spanish or European settings (semicolon as separator):
=IMPORTJSONPATH("https://api.frankfurter.dev/v1/latest?base=EUR&symbols=USD,GBP"; "$.rates")One column per currency with the rate against the euro, with the currency code as header. To get a single value, ask for it: $.rates.USD with the noHeaders option returns just the number.
The single-value version is `=IMPORTJSONPATH("https://api.frankfurter.dev/v1/latest?base=EUR&symbols=USD", "$.rates.USD", "noHeaders")`.
Lists inside a wrapper object
API: PokéAPI
=IMPORTJSON("https://pokeapi.co/api/v2/pokemon?limit=20", "/results/name,/results/url")With Spanish or European settings (semicolon as separator):
=IMPORTJSON("https://pokeapi.co/api/v2/pokemon?limit=20"; "/results/name,/results/url")The API wraps its list in an object with a count and a next-page link. Asking for /results/name and /results/url gives you just the list, one row per item.
Automatic pagination (following the next link) is one of the planned Pro features. For now, change limit and offset in the URL.
Import everything, nested fields included
API: JSONPlaceholder
=IMPORTJSON("https://jsonplaceholder.typicode.com/users", "")With Spanish or European settings (semicolon as separator):
=IMPORTJSON("https://jsonplaceholder.typicode.com/users"; "")With an empty query, every field is imported. Nested objects become columns named after their path (the address street, city and coordinates, the company name, and so on).
Handy for exploring an unknown API. Once you know which fields you want, pass them in the query so the sheet stays small.
Find a field anywhere in the document
API: JSONPlaceholder
=IMPORTJSONPATH("https://jsonplaceholder.typicode.com/users", "$..city")With Spanish or European settings (semicolon as separator):
=IMPORTJSONPATH("https://jsonplaceholder.typicode.com/users"; "$..city")The recursive descent .. finds every "city" key at any depth and returns the values in a single column.
Use it when you do not know, or do not want to write, the full path.
What about APIs that need a key?
Formulas cannot carry secrets, so keys are handled by the request builder in the sidebar: pick API key (header or query) or Bearer, paste the key once, preview, and insert. The key is stored only for you in your Google account, never in the sheet.
The Marketplace listing is going through review. The install link will appear here once it is published.