“Can I import JSON to Google Sheets?”Analyst, marketer, or developer
“How do I convert data from JSON to Google Sheets?”
“How do I import data from API to Google Sheets?”
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.
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.
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.
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)
Setting Up ImportJSON Function
1 – At the top, click on Tools. Then click on Script editor.
2 – Once inside, name your project. This is arbitrary.
3 – Delete everything that’s inside the text editor.
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.
6 – Go back to the script editor and paste everything. Paste all the code.
7 – Click on the Save icon.
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.
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.
Congratulations! This is a glimpse of what importing JSON data into Google Sheets can do.
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.
2 – Search for Two Minute Reports. Then install it.
3 – You now have the add-on installed on your Google Sheets file.
Import JSON Data Source
1 – Launch the add-on. A navigation pane on the right side appears.
2 – Click on Add + under Data Sources.
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
5 – For Authorization, select No Auth. The API doesn’t need any authentication.
6 – For Headers, keep them empty.
7 – Click on Save.
Optional: You may choose Test before saving to verify that the API base URL is valid.
Select GET for Method.
/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.
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 +.
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.
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).
10 – Click on Run query.
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 +.
2 – Once inside, you can set the terms of the report schedule.
-Repeats: This is where you set how often the data refresh happens. Two Minute Reports can do hourly refreshes.
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.
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.
In the end, you’ll build something like this:
1 – Click on Templates. The tool already has a template since this scenario is a standard case.
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.
3 – Click on the Create new scenario from template button from the lower left of the page.
Setting Up HTTP and JSON Connection
1 – Under URL, add the entire API URL. Use
. 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.
2 – Under Data structure, select My data structure.
3 – Under JSON string, select
4 – Click on Continue.
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.
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.
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.
2 – Search and select Google Sheets. Then select Add a Row.
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.
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.
7 – Click on Allow. Integromat asks permission to access your Google account. This is standard procedure. The window closes after clicking it.
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.
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.
Under (A), select
Under (B), select
Under (C), select
Under (D), select
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.
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
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.
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.
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.