How to consume any JSON API using Google Sheets, and keep it up-to-date automagically (2024)

Google Sheets are very powerful when you know how to use them. You can do so much with those, and at Unly, we rely on them to automate many things, without relying on developers to build custom tools. #productivity

Most of today’s online services provide ways of extracting data into a spreadsheet, whether it’s CSV, Google Sheets, Excel, etc.

But we often run into limitations when using such services, because they do not always export data the way we’d like them to.

Now, imagine if you could just plug your spreadsheet to any API, extract exactly what you want, the way you want, and use the data however you want with any other service of your liking. Sounds nice right?

We recently encountered such limitations with online services such as PipeDrive and TypeForm

PipeDrive limitations:

We wanted to export our PipeDrive contacts to a spreadsheet.
But the online export feature would not allow us to have two distinct columns for first name and last name, it would just merge both together and that was an issue for our sales team.

Such limitation sounds silly, but it actually impacted the productivity of our sales team. To get around this, we connected Google Sheet to PipeDrive API directly, to get back control on our data.

Now, let’s see how we do that! We’ve put together a very straightforward example. If you want to play around with it we encourage you to make a copy! (File > Make copy)

Spreadsheet example:

https://docs.google.com/spreadsheets/d/1qQg3p_JG7Tirv14z8JXcs4sehsOafBW9FmAkTVVolIc

Spreadsheet explanation:

In this Google Sheet, look at the A1 cell, that’s where the magic is hidden!

=ImportJSON("https://jsonplaceholder.typicode.com/todos", "/", "noInherit,noTruncate,noPrefixHeaders", Settings!$B$1)

This single line basically fetch the jsonplaceholder.typecode.com API.
(which doesn’t do anything meaningful besides displaying “todos”).
Then, it parses the JSON data returned by the API and display them below. Boom!

How does it work?

Make a copy using File > Make copy if you haven’t done that already. Making a copy will also copy the scripts that are behind the function.

In the Script Editor, you will see those 2 scripts (Tools > Script Editor):

1. ImportJSON.gs is the script that does most of the magic, basically fetch the data, parse them and display them. It’s open source (GNU v3)
2. triggerAutoRefresh.gs is a custom script we wrote which updates the cell B1 in the “Settings” sheet, it’s a simple trick to force the sheet to refresh when the function is triggered. It is configured to refresh upon page loading for this example, and not on a regular interval (in order not to spam the API), but refreshing on a regular interval is easily doable.

I won’t explain here what the ImportJSON does, if you want to deep-dive then take a peak at https://blog.fastfedora.com/projects/import-json which gives a good overview of the features (even if it’s marked as outdated, it’s the best documentation I’ve found)

Keeping the data up-to-date automatically

If you made a copy you probably noticed it doesn’t refresh on its own. That’s because even though the scripts were copied, the triggers were not.

You have to add them yourselves, it’s pretty simple:

1. Go to https://script.google.com/home/all
2. Select your project (should be “TEMPLATE — Google Sheet to JSON API”)
3. Go to Project details > Triggers (see below)
4. Add trigger (by default it adds a trigger on the “Spreadsheet open” event)
5. Select the triggerAutoRefresh function
6. Select “Event source: Time driven”, select a 5 minutes interval and voilà!

That’s it! Your spreadsheet will now be updated every 5 min due to the trigger, which will write a new random value in the cell Settings$B$1 which in turn will trigger a refresh of the ImportJSON API calls, that will refresh the displayed data!

We wrote another article which showcases Typeform.
In this demo you’ll see how Google Sheets can be used to fetch real-time data from an API, as those data are created by yourself.

There are a few limitations with this kind of usage. It’s very simple to consume an API that doesn’t require an authentication, such as the one used in the example. If you need authentication, then things can get harder.

For PipeDrive for instance, since they allow authentication through a api_token to send as GET parameter (query string), it’s pretty simple to configure:

=ImportJSON(“https://api.pipedrive.com/v1/persons?start=0&api_token=your_token&limit=10000&filter_id=1", “/data/first_name”, “noInherit, noTruncate”)

Very simple. Beware of whom you share that sheet with though, they’ll be able to read your API token.

Word of caution, if you have the ability to decide what permissions that token grants, then only allow what you use. For instance, it doesn’t make sense to use a token with write permissions if you only use it to visualize your data.

If you get stuck with authentication, there are other workarounds, such as UrlFetch (OAuth). But it won’t cover all use-cases either.

The ability to consume JSON API through a Spreadsheet is very powerful.
At Unly, it gives our sales and marketing teams the ability to consume online services, without needing our developers’ intervention.

We’ve used this trick for a few months and it hasn’t let us down, we hope it will help you as much as it has helped us!