How do i integrate an api into google sheets?

Import data directly to your spreadsheet

⚡Trusted by over a million users
⚡ Free, with paid upgrade available for advanced features
⚡ Designed for spreadsheet users, no coding required
⚡ Built-in library filled with hundreds of ready-to-use APIs

I use API Connector to create automated monitoring tools for SEO. It opens a universe of possibilities with a very limited and efficient toolchain. API Connector is reliable, glitch-free, and production-ready.

⭐⭐⭐⭐⭐
EVGENIY ORLOV
SEO Team Lead, Mediaworx

API Connector is an amazing product that enables a non-coder to leverage the power of APIs and integrate seamlessly with Google Sheets. It opens up a great deal of opportunities without the need to invest in expensive BI or ETL tools.

⭐⭐⭐⭐⭐
ERIC ANG
Director, One Search Pro Marketing

API Connector is the easiest and fastest way to create an API-based website on WordPress. By pairing it with WP All Import to import the data, I launched a course discovery platform with no coding needed.

⭐⭐⭐⭐⭐
ADRIAN NUTIU
Founder, MadBright

API Connector is awesome. It allows us to integrate and automate Shopify, Xero, Facebook, Cin7 and so much more.

⭐⭐⭐⭐⭐
BEN MULLER
Founder, Dock & Bay

We are using API Connector for a variety of reports, mainly with Shopify. The documentation and examples on the Mixed Analytics website are extremely helpful. Highly recommended for anyone who wants to automate reporting.

⭐⭐⭐⭐⭐
SIMON KÖSTLER
Co-Founder, STUR Cookware

API Connector allows us to compile data from all of our marketing channels into a single location effortlessly. The documentation is very clear and Ana provides extremely good customer support.

⭐⭐⭐⭐⭐
BRANDON WILKES
Digital Marketing Manager, The Big Phone Store

100+ integrated applications

Start quickly with a built-in directory of popular and useful applications.
More info >

Create custom requests

Connect to almost any API using a no-code interface.
More info >

Combine metrics

Easily view and manage data from multiple data sources in a single place. No manual exports, custom scripts, or expensive data warehouse required.

Schedule requests

Automate your reporting. Set triggers to pull from APIs at hourly, daily, weekly, or monthly intervals. More info >

Use cell values in requests

Turn spreadsheet cells into variables. Achieve dynamic, code-like capabilities by basing request URLs on the cells in your sheet.
More info >

Connect to (almost) anything

Pulls data into Google Sheets from any platform that uses API keys or OAuth2 for authorization and returns data in JSON, XML, or CSV format. This opens up access to nearly any platform, e.g. Binance, CoinMarketCap, Facebook, Shopify, etc.

Spreadsheets made simple

Made specifically for reporting and spreadsheets. Includes spreadsheets-first features like append vs. overwrite modes and building API requests based on the value in a cell. Use the built-in API directory for even faster setup.

Free and premium plans

The free version of API Connector includes 250 monthly API requests and many powerful features for working with APIs. The paid plans allow more requests, scheduling, pagination handling, and more. Please view the pricing page for details.

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.

How to connect Google API to any JSON API

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!

Demo with a real use-case

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.

Limitations

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!