How to import JSON data into a Google Sheet

IN THIS ARTICLE

• Preface
• Solution
  • Installation
  • Usage
• Limitations
  • Authentication
  • User Agent
  • Data Structure
  • Data Volume
  • Sync Frequency
  • Maintainability

• Alternative Solution
  

Preface

JSON (JavaScript Object Notation) is a lightweight data interchange format that is easy for both humans and machines to use and understand. It is also the standard used by the APIs (Application Programming Interfaces) of many software and data providers to communicate and export data on demand.

However, Google Sheets do not have native support for importing JSON content into a file. The import options available out of the box are:

• IMPORTXML: Imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
• IMPORTRANGE: Imports a range of cells from a specified spreadsheet.
• IMPORTHTML: Imports data from a table or list within an HTML page.
• IMPORTFEED: Imports a RSS or ATOM feed.
  

More information about the various import functions can be found here. 

With that said, the only way this can be achieved is via a custom solution, outlined below.

Solution

The most popular solution online is an Apps Script -- a piece of code that is added to the Google Sheet as an extension to perform automated tasks and expand its functionality. In this particular example, the code will create a new function, ImportJSON, that will attempt to fetch the contents of the specified URL and transform the JSON content into the two-dimensional table format supported by Google Sheets.

Installation

1. Copy or download the script available here
2. With the Google Sheet open, navigate to Extensions > Apps Script
3. An Apps Script tab will open with the Code.gs file contents. Replace its contents with what you downloaded in item 1
4. Click on the Save project icon to save the file (you can use shortcuts like CTRL+S or ⌘-S)

Usage

1. Select an empty cell with no content to the right or bottom of it
2. Enter the following: =ImportJSON("https://api.usaspending.gov/api/v2/references/toptier_agencies/")
3. Replace the URL with your target API endpoint

Other import functions and parameters are available but only apply in specific conditions. More information can be found in the script itself (see text in green color).

Limitations

While the solution above may work with some providers/ APIs, it is not a one-size-fits-all option for extracting data given the variability of the elements listed below.

Authentication

Most APIs are protected and require some kind of authentication to be sent when accessing a URL. There are also many types of authentication, some unique or exclusive to specific providers. The solution provided only works with APIs with Basic authentication, or no authentication at all (i.e. open/ public APIs).

User Agent

Numerous service providers attempt to validate if a URL is accessed by a human or a script /bot, often restricting access if they detect automated activity. Various strategies are utilized to imitate human behavior, but they necessitate thorough testing to determine their effectiveness. The aforementioned solution doesn't consider these options unless the source code is modified.

Data Structure

JSON is not a two-dimensional data structure, thus the transformation may not work properly or at all depending on how the data is organized. This is more noticeable when working with nested data (e.g. one folder can have multiple folders, each with multiple folders of their own, etc.).

Data Volume

There's a limit on how much data you can bring in at once using this solution. If you go over this limit, you might encounter errors or end up seeing no data at all. To avoid or fix these errors, you might need to add some settings when using the function. But whether this is possible or not depends on the provider's system/ API and might not always be an option.

Sync Frequency

The ImportJSON function in Google Sheets operates exclusively when the file is open in a web browser. Upon closure, the data remains static without further updates. Therefore, this solution is suitable only for scenarios where the data remains unchanged or if occasional manual updates by reopening the file are acceptable.

Maintainability

The creators of the solution have stopped maintaining the code responsible for extracting and modifying the data. Consequently, there's no assurance that it will remain functional as Google updates its Apps Script libraries.

Alternative Solution

If you're facing these limitations and want to make reports or design custom dashboards with your data, get in touch with us. Our team of experts has experience with hundreds of APIs. They can analyze your situation and provide you with next steps.

We can build a custom solution that brings data directly into Databox, without the need to use a Google Sheet as an intermediary step to build your reports.