How to send data to a Google spreadsheet?

IMPORTJSON for Google Sheets

FAQ

What is this for?

This is 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 API usually provide URLs with JSON data.

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

That's where this tool becomes handy.

How it works?

This tool allows you to convert an API endpoint into a formula that you can put as is to your Google Sheet and then magic will happen: the JSON data will be converted to the table on the fly.

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