How to send data to a Google spreadsheet?

The easiest way to import API into your Google Sheet

There are a lot of APIs that expose useful data that users want to use in their spreadsheets. These APIs usually provide URLs with JSON data.

Unfortunately, Google Sheets doesn't have a built-in way to import JSON data.

With this IMPORTJSON tool, you can import JSON data into a Google spreadsheet in a simple and convenient way. Сonvert an API endpoint into a formula and put it as is to your Google Sheet and then magic will happen: the JSON data will be converted to the table on the fly.

IMPORTJSON for Google Sheets

How it works

Here is a step-by-step guide on how to use IMPORTJSON.

1. Paste your JSON URL into the JSON URL field above. If your JSON object contains nested data, you can flatten it and specify the JSON Path as needed. Once you've done that, the tool will automatically generate the appropriate formula for you. For your convenience, our tool allows preview the data that will be returned by your formula before copying it to your Google Sheet.

2. Copy the generated formula.

3. Open a Google Sheet where you want to import JSON data and paste the formula in a cell.

4. Press Enter. The formula will fetch the JSON data from the API endpoint and convert it into a table in the cell.

By following these steps, you can easily import JSON data from API endpoints into Google Sheets and begin analyzing it in a readable format.

FAQ

Is it free?

Yes. Absolutely.

Do you store the data?

No. This tool converts the JSON data into the table data on the fly and doesn't store any information on the server.

What about nested JSON objects?

To turn all nested data to a flat single level table use the 'Flatten nested objects' checkbox.

The data I want to import is located deep inside the JSON. How do I import only a nested object or array?

Great question. It's possible by turning on the 'Specify JSONPath' checkbox.

Let's assume you want to import the up-to-date COVID summary data from this endpoint: https://api.covid19api.com/summary

It has the following JSON structure:

{
  "Global": {
    "NewConfirmed": 199615,
    ...
  },
  "Countries": [
    {
      "Country": "Afghanistan",
      "CountryCode": "AF",
      ...
    },
    ...
    {
    "Country": "Zimbabwe",
    "CountryCode": "ZW",
    ...
    }
}

What we want is to access only the items (array) under the Countries property. To achieve this we need to use $.Countries.[:] JSONPath expression.

Try this example now! and see how it looks at the Google Sheet (opens new window).

You can do much more, see jsonpath.com (opens new window) for the details.

I have a question or a suggestion

Feel free to drop us a line: support@spreadsimple.com