Data source

One, or a list of:

  • Plain URLs
  • cURL Requests
  • Google Drive file ID
  • JSON in text

Plain URLs

It can be any URL that returns a JSON object, like

https://restcountries.eu/rest/v2

cURL requests

cURL is a command-line tool for getting or sending data including files using URL syntax

ImportJSON accepts POST and GET requests sent with cURL. Therefore you can use cURL to load any API.

Simple example:

=IMPORTJSON("curl 'https://api.thecatapi.com/v1/images/search' -H 'x-api-key: xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx'")

On a side note, although we take the best care to keep your data safe by communicating with our servers using a SSL certificate, beware that we don’t recommend using ImportJSON for highly sensitive data. In case you need to handle sensitive data, please contact us.

Google Drive file ID

ImportJSON allows to load JSON files stored in your Google Drive.

  • Upload a text file containing a JSON. Bear in mind that it cannot be a Google doc: you need to create a plain text file on your computer and upload it to your Google Drive.
  • Change the file permissions so that anyone who has the link can view the file
  • From the shareable link, only copy the id

Example:

https://drive.google.com/file/d/1KUPmjU4DQdZ8C2VhSHmyxnxyPe74IZhB/view?usp=sharing
  • Add the ID to the IMPORTJSON function

Example

=IMPORTJSON("1KUPmjU4DQdZ8C2VhSHmyxnxyPe74IZhB")

JSON in text

Any JSON can be parsed directly from Google Sheets.

Example

=IMPORTJSON("{'prefix':'roller','dict':'0','res':{'sug':['roller','roller quad','rollerblade','roller fender','rollers','rollerball','rollergames nes','roller enfant','roller fille','roller skating'],'categories':[[115180,'Articles de skate, de roller et de trottinette'],[7278,'Stylos haut de gamme']]},'savedser':{}}")

Note: Any text in a function has to be surrounded by double quotes. Therefore the JSON’s double quotes can be replaced either by single quotes, like the example, above or doubled double quotes:

You don’t need to modify the quotes if you place the JSON in another cell that you reference in the ImportJSON function

Note2: Google Sheets limits the size of a text in cell to 50,000 characters. To avoid this limitation, you can split your JSON into a range of cells like so: