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.
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.
Yes. Absolutely.
No. This tool converts the JSON data into the table data on the fly and doesn't store any information on the server.
To turn all nested data to a flat single level table use the 'Flatten nested objects' checkbox.
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.
Feel free to drop us a line: support@spreadsimple.com
Use SpreadSimple and Google Sheets to manage your content and create an attractive and functional website.
You start with Free plan.
You can test drive Pro plan free for full 7 days (no credit card required).
Try it out