How to Import JSON Data from Any API into Google Sheets Using SQL

If you’ve ever wanted to bring live JSON data from an API into Google Sheets for analysis, automation, or visualization, this guide is for you. By using SQL queries in combination with Google Sheets, you can extract, process, and display data effortlessly. Let’s dive into the step-by-step process.

Step 1: Install the SheetQuery Add-On

Google Sheets doesn’t natively support SQL queries, but you can enable this functionality by installing the SheetQuery add-on.

Step 2: Choose Your API and Understand Its JSON Response

In this example, we’ll use the REST Countries API, which provides detailed data about countries worldwide. Visit the API’s endpoint:

https://restcountries.com/v3.1/all

The response is a JSON array containing data such as country names, flags, populations, and more.

Step 3: Write Your SQL Query for the JSON Data

The SQL syntax used here follows SQLite, which is lightweight and supports a robust set of JSON functions. These functions make it easy to parse, extract, and manipulate JSON data directly within your SQL queries. For a complete list of SQLite’s JSON functions, visit the official documentation: SQLite JSON1 Extension.

Here’s the SQL query we’ll use to import and extract data:

INSERT INTO
    "A2:D2"
    SELECT
      json_extract(value, '$.flag') flag,
      json_extract(value, '$.cca3') cca3,
      json_extract(value, '$.population') population,
      json_extract(value, '$.name.common') name
    FROM
      json_each(
        fetch (
          'https://restcountries.com/v3.1/all'
        )
      )

Explanation:

• INSERT INTO "A2:D2": Specifies the range in your active sheet where the data will be inserted, starting at cell A2. The number of columns in the range must match the number of columns specified in the SELECT statement.
• json_extract: Extracts specific fields or values from the JSON data using a JSON path.
• fetch: Retrieves the JSON data from the specified API endpoint.
• json_each: Iterates over each JSON object in the API response, treating each object as a separate row of data.

Step 4: Execute the Query

1. Open the Extensions menu in Google Sheets.
2. Select SheetQuery, then click Open.
3. In the sidebar that appears, click SQL Pad and paste your SQL query.
4. Click Run or press F9 to execute the query.

Conclusion

With this guide and the SheetQuery add-on, you can easily import and manipulate JSON data from any API directly in Google Sheets using SQL. SheetQuery’s support for SQLite syntax and JSON functions makes it a powerful tool for transforming and automating data workflows.

Start using SheetQuery today to make your spreadsheets more dynamic and efficient!