Import JSON Data to Google Sheets

“Can I import JSON to Google Sheets?”
“How do I convert data from JSON to Google Sheets?”
“How do I import data from API to Google Sheets?”

Analyst, marketer, or developer

It’s no secret that data is the new currency. And it’s also no secret that technology has become more advanced and cheaper. These major strides made deploying apps and building databases easier. In this article, you’ll learn what APIs are and it’s file format JSON. Then use these data and import them to a spreadsheet using simple resources.

Requirement: API Data in JSON Format

You need an API first. API means Application Programming Interface. In simple terms, APIs allow different software and databases to connect with each other. They are great to build applications and pulling in data for testing. There are many APIs in the tech industry. But in this guide, you’ll focus on public and open APIs.

In this guide, you’ll use the open and free Harry Potter API. Simply put, you’ll use an API endpoint to get information about the pop culture franchise. See the example below, the information that’s from the API is a list of characters from the Harry Potter series. Click on the link below.

http://hp-api.herokuapp.com/api/characters

What you see inside is the JSON format or the JavaScript Object Notation format. JSON is the format of most open APIs when getting requests, and it’s what you’ll use in this guide. Knowing a little bit of JavaScript helps here, but it’s not a requirement.

In addition, an open API is when you don’t need login details or credentials. It means it’s doesn’t need to ask permission to your email address. It also means the API doesn’t require you to sign up. The Harry Potter API above doesn’t need user permissions from anyone.

Why Pay for Automation at All?

Automations solve huge problems in organizations. So, most automation tools are paid products. Yes, you can do everything manually for free to save money. But in perspective, instead, you might want to spend a little to save time. The number of hours that you take back to avoid hellish and repetitive work is worth the price.

busy desktop

I hate a super cluttered desktop. Ever experienced a desktop (or Downloads folder) full of spreadsheet files? I hate them. It increases stress and decreases productivity. The amount of time I clicked the Export CSV button is unbearable. Automation avoids all this.

How to Import JSON to Google Sheets

This article shows three options on how to import JSON API to Google Sheets. Option 1 is using Google Apps Script. Option 2 is using a Google Sheets add-on. Option 3 is using a third-party party integration tool. In any option, you need a spreadsheet to start.

1 – Go to your Google Drive workspace.

2 – Click on + New then create a new Google Sheets file.

tmr 0
tmr 00

3 – Name the Google Sheets file. The name is arbitrary so it’s up to you. I suggest writing a descriptive name for later use.

Option 1: ImportJSON Function (Free)

First option is to use the ImportJSON function in Google Sheets. This is completely free and is the only free option in this article. Also, this method is easier to implement if you know a bit of JavaScript knowledge. However, the guide is easy enough for anyone to follow.

Setting Up ImportJSON Function

1 – At the top, click on Tools. Then click on Script editor.

json sheets 10

2 – Once inside, name your project. This is arbitrary.

3 – Delete everything that’s inside the text editor.

json sheets 11
Google Apps Script

4 – To create the ImportJSON function, we need to go to Brad Jasper’s GitHub page.

5 – Once inside, copy the entire text code. Highlight from line code 1 until line code 634. Then copy it.

pse 4
GitHub

6 – Go back to the script editor and paste everything. Paste all the code.

json sheets 12b
Google Apps Script

7 – Click on the Save icon.

json sheets 02

Setup done. At this point, the code initializes in the Google Sheets file you’re working on.

Using ImportJSON Function

1 – Go back to your Google Sheet file.

2 – On a cell, type in =ImportJSON(). The function appears. This means the script is working.

json sheets 13

Inside the parenthesis is where you add the API URL. In this example, we add the Harry Potter API.

3 – Type in =ImportJSON("http://hp-api.herokuapp.com/api/characters"). Your Google Sheet populates the cells with the data.

json sheets 03

Congratulations! This is a glimpse of what importing JSON data into Google Sheets can do.

json sheets 04
Results

However, there are downsides to this method:
Reports are not automated that you can leave the API call and get the data automatically. Manual labor is still required when there are other data endpoints you want to track and monitor.

The ImportJSON function may not be robust enough to help. Also, there are no auto-schedule reports. It won’t be enough if you want to track a more active API on an hourly basis and keep records of them.

Option 2: Google Sheets Add-On (Paid)

Second option is to use a Google Sheets Add-On. You’ll use Two Minute Reports for this example. TMR imports data from the API directly to Google Sheets in minutes.

Installing a Google Sheets Add-On

1 – At the top of your Google Sheet, click on Add-ons. Then click on Get add-ons.

tmr 2

2 – Search for Two Minute Reports. Then install it.

tmr 1

3 – You now have the add-on installed on your Google Sheets file.

tmr 3

Import JSON Data Source

1 – Launch the add-on. A navigation pane on the right side appears.

tmr 15

2 – Click on Add + under Data Sources.

tmr 4

3 – Name your data source. Then select API Bridge under Type.

4 – For Base URL, add the Harry Potter API base URL. In this example, it’s http://hp-api.herokuapp.com.

5 – For Authorization, select No Auth. The API doesn’t need any authentication.

6 – For Headers, keep them empty.

7 – Click on Save.

json sheets 05

Optional: You may choose Test before saving to verify that the API base URL is valid.

Select GET for Method.

Add /api/characters under Test URL Suffix. If not valid, the tool gives instructions on how to fix it.

Then click on Test and Save.

If valid, the tool prompts Successfully authenticated.

json sheets 05b

8 – Once everything is done correctly, click on Yes. And you’re done with this section.

Import JSON Data Query

This point in the process is importing data to the spreadsheet.

1 – Click on the burger icon. Then click on Data Queries. Then click on Add +.

tmr 11

2 – Name your data query. Then find the API Bridge data source you imported earlier.

3 – Save on Sheet is the location of the sheet where the data is imported to. The location might be Sheet 1 but it depends if you named your Sheet. Then add A1 under Cell.

4 – Don’t select Append new data. This means the data overwrites the current spreadsheet. This doesn’t matter now since this is your first API call.

json sheets 06

For the Query section:

5 – Under Method, select GET.

6 – Under URL Path/Suffix, add /api/characters. To recap, this is the list of characters from the Harry Potter API.

7 – Under JMES Path, leave it empty.

8 – Under Pagination, select None.

9 – Under Output Format, select Grid (default).

json sheets 07

10 – Click on Run query.

json sheets 08
Results

Viola! You imported the list of characters from the Harry Potter API on the spreadsheet. These are the important steps in importing JSON data to Google Sheets. At this point, you may take the steps on accessing other needed data.

Schedule JSON Reports

Two Minute Reports has a scheduling function. This means the spreadsheet refreshes the acquired data from the API. Reports are automated even more.

1 – Click on the burger icon. Then click on Schedule Refresh. Then click on New +.

tmr fb5

2 – Once inside, you can set the terms of the report schedule.

-Name
-Repeats: This is where you set how often the data refresh happens. Two Minute Reports can do hourly refreshes.
-Timezone

tmr li3

3 – You can also set the notification terms. Two Minute Reports can email you a PDF or Excel of the data. You can also decide to have the email contain the sheet content in the email body.

tmr fb7
tmr fb8

4 – Click on Save.

Scheduling helps when you need to report on API JSON data over time. No more copy and paste every day, or every week, or every month.

Option 3: Third-Party Integration Tool (Paid)

Third option is to use a third-party integration tool. You’ll use Integromat for this method. Integromat is a powerful integration platform that allows you to visualize, design, and automate your work in minutes. In other words, it lets apps talk to each other so that you can remove repetitive tasks.

Create an Integromat account and get started.

This image has an empty alt attribute; its file name is integromat-01.jpg
Integromat

In the end, you’ll build something like this:

json sheets 09 integromat 07

Getting Started

1 – Click on Templates. The tool already has a template since this scenario is a standard case.

integromat 02b

2 – Search and click on the Parse JSON from HTTP request ( Basic) template.

Later on, you’ll add additional settings to parse JSON data and import it to Google Sheets.

json sheets 09 integromat 01b

3 – Click on the Create new scenario from template button from the lower left of the page.

integromat 03

Setting Up HTTP and JSON Connection

1 – Under URL, add the entire API URL. Use http://hp-api.herokuapp.com/api/characters. To recap, this is the list of characters from the Harry Potter API.

The HTTP connection is finished at this point. The next is the JSON connection.

json sheets 09 integromat 02

2 – Under Data structure, select My data structure.

3 – Under JSON string, select Data.

4 – Click on Continue.

json sheets 09 integromat 03

You’re done with this section.

Setting Up Tools Integration

This section allows the parsing of JSON data. The next steps are to have a more complete connection between the JSON data from the API and Google Sheets. This allows importing JSON to Google Sheets as the end goal.

1 – On the bottom of the page there’s a Tools section. Click on the icon with a screwdriver and wrench.

2 – Find a select Set multiple variables.

json sheets 09 integromat 04

3 – Click on the huge round icon.

4 – Under Variables, leave it untouched.

5 – Under Variable lifetime, select One cycle.

There’s not much to do in this section.

json sheets 09 integromat 05

Setting Up Google Sheets Connection

1 – Beside the Tools connection icon, click on Add another module. Or you can right-click, then click on + Add a module.

json sheets 09 integromat 06

2 – Search and select Google Sheets. Then select Add a Row.

mailchimp sheets integromat 06

3 – Under Connection, click on Add.

4 – Name the connection. The name is arbitrary so it’s up to you. I suggest writing a descriptive name for later use.

gmail to sheets integromat 12b

5 – Click on Continue. A pop-up window appears.

6 – Inside the pop-up window, select the Gmail account that stores the Google Sheets file.

integromat 04

7 – Click on Allow. Integromat asks permission to access your Google account. This is standard procedure. The window closes after clicking it.

gmail to sheets integromat 13

8 – Back in Integromat, under Mode, choose Select spreadsheet and sheet. This is to make the integration simpler.

9 – Under Spreadsheet, select the Google Sheets file you created earlier. Find the name you gave it.

10 – Under Sheet, select the Google Sheets tab from earlier. Find the name you gave it. After which, the Values are created.

integromat 07 sheets5

11 – Under Values is where it gets tricky. The headings from the Google Sheets file are the parameters where you decide the values. Once you click on the field, you’re presented with different parameters. Don’t be overwhelmed. Read the context of the parameters to know which one is mapped to which.

integromat 07 sheets7

Under (A), select name.
Under (B), select gender.
Under (C), select house.
Under (D), select dateOfBirth.

The parameters that appear here are from the Harry Potter connection. You can manually select which data points you want to import. Other parameters are also available. Make sure to add an extra column and name it on the Google Sheets spreadsheet so on and so forth.

12 – Click on Continue. Now it’s time to run and process the integration.

13 – Click on Run once with a play button icon.

integromat 06

A spinning wheel and a Log on the bottom right appear. Wait for the integration to finish. How long the integration takes depends on the Maximum number of results you select. It’s finished when a) green checkmarks appear, and when b) The scenario run was completed appears in the Log.

json sheets 14c

Go to your Google Sheets file and you now see the data successfully exported from the API. Viola! Congratulations! You imported JSON data and report them in Google Sheets with complete information! You may also add other parameters or fields if you need them.

Schedule Automation and Reports

Integromat can schedule the automation based on cycles. The schedule is up to you.

1 – On the screen, click on the Scheduling button and turn it on.

integromat 06

2 – Under Run scenario, select the schedule you need. What you select on this parameter dictates the next ones.

If you select At regular intervals, you need to select the minutes that Integromat processes the automation.

If you select Once, you need to select the date.

If you select Every day, you need to select the time.

If you select Days of the week or Days of the week, you need to select the days.

gmail to sheets integromat 18b

3 – Click on Activate. Ignore any advanced settings for now.

Other APIs and other JSON data sources work differently in regard to scheduling intervals. Some APIs are more fast-paced than others depending on your projects. Viola! Congratulations you’ve automated importing JSON to Google Sheets!

More articles on automation:
Import Shopify Data to Google Sheets
Import Google Ads Data to Google Sheets
Import WooCommerce Data to Google Sheets
Import Facebook Page Insights to Google Sheets
Import LinkedIn Ads API Data to Google Sheets
Import Facebook Ads Data to Google Sheets
Import Mailchimp Data To Google Sheets
Export Gmail to Google Sheets
Import JSON to Google Sheets

Conclusion: Connect JSON API to Google Sheets

Send JSON API to Google Sheets. These are the methods that integrate third-party tools. These add-ons cost money, but the benefits outweigh more. Avoid import and export hell. Invest in tool automation and stop wasting time on repetitive tasks and start taking action.

Running a business is a never-ending cycle of work. This no-code solution doesn’t need you to learn coding or Python. It’s frustrating to juggle all your responsibilities. Integrate apps that you use every day faster. Time is precious for you so get back to doing what matters most: growing your business.


About The Author

Francis Angelo Reyes

Francis is the owner and runs Lupage Digital. With 5 years of experience, Francis writes everything he knows about digital marketing. There’s a large knowledge gap between developers and digital marketers. Francis is the middleman between where the non-technical and technical meet.