Importing JSON is a powerful way to pull data from public data sources to store, enrich or analyse. While Google Sheets offers a built-in =ImportData() function that can retrieve JSON data, we recommend using a trusted community script that adds an =ImportJSON() function to Google Sheets. With ImportJSON the JSON data gets transformed so that it’s correctly formatted in rows and columns in your spreadsheet.

Let’s use ImportJSON to retrieve data from an API endpoint. For this example we'll use CoinDesk's historical price of Bitcoin:

  1. To begin, create a new Google Sheet.
  2. Click on the Tools menu and select the option Script editor. This will open a new Google Apps Script tab.
  3. In the code window, delete the default placeholder function to leave a blank window.
  4. Head over to the Gist containing the ImportJSON script.
  5. Click the Raw button in the top right corner of the script window to select all and copy the entire code.
  6. Return to your empty script editor window and paste the script code.
  7. From the menu, select File > Save.
  8. Name your code "ImportJSON".
  9. Close the Script editor and navigate back to your Google Sheet tab.
  10. In cell A1, enter =ImportJSON(“https://api.coindesk.com/v1/bpi/historical/close.json”). Be sure to keep the quotes “” and parentheses ().
  11. Press Enter. Your Google Sheet should now populate with your JSON data.

Alongside “url”, there are two additional parameters you can use with =ImportJSON():

  • “query” – a comma-separated list of paths to import. Any path starting with one of these paths gets imported. (Example: “/feed/entry/title,/feed/entry/content”)
  • “parseOptions” – a comma-separated list of options that alter processing of the data. (Example: “noInherit,noTruncate,rawHeaders”)

If ImportJSON has whetted your appetite for more importing of API data in Google Sheets, you might want to consider some of the other advanced Google Sheets functions out there, an automated option like Apipheny or Supermetrics, or visualizing the data you've imported using Geckoboard.