Import JSON to Google Sheets (Low-Code Guide)

Content of the Article

Summary

– Use the ImportJSON script to import JSON data in Google Sheets.

– Connect APIs by using 3rd-party tools and create reports in Google Sheets.

– Allow to convert JSON data from any API to Google Sheets by using extensions.

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.

https://hp-api.onrender.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.

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 extension. 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)

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.

json sheets 01b appscript — Google Apps Script

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 Extension (Paid)

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

Installing a Google Sheets Extension

1 – Go to your Google Drive workspace.

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

3 – At the top of your file, click on Extensions. Then click on Add-ons. Then click on Get add-ons.

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

5 – You now have the extension installed on your Google Sheets file.

Import JSON Data Source

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

2 – Click on MENU then click on Connections then clickc on Add +.

3 – Select API Bridge.

7 – Enter the fields below:
– Under Base URL, add the Harry Potter API base URL. In this example, it’s https://hp-api.onrender.com. Note that this could be your preferred API endpoint.
– For Authorization, select No Auth. This API doesn’t need any authentication.
– For Headers, keep them empty.
Optional: You may choose Test before saving to verify that the API base URL is valid.

8 – Click on Save.

9 – Select the data or endpoint. Switch it to Enabled. Click on Done.

Import JSON Data Query

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

1 – Click on Menu. Then click on Data Queries.

2 – Click on Add +.

3 – Name your data query. Then find the API endpoint you imported earlier.

4 – For the Query section:

Under Method, select GET.

Under URL Path/Suffix, add /api/characters. as a review, this is the list of characters from the Harry Potter API.

Under Json Processor, select None.

Under Pagination, select None.

Under Output Format, select Grid (default).

5 – Under Result, select the spreadsheet tab you want to imported the data into. Add A1 under Cell.

6 – Click on Run query.

Now you’ve 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 MENU. Then click on Schedule Refresh.

2 – Click on New +.

3 – 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

4 – 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.

5 – Click on Save.

Scheduling helps when you need to report on overtime. No more copy and paste manual reports every day, or every week, or every month.

Option 3: Third-Party Integration Tool (Paid)

You’ll use Make for this option. Make 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 a Make account and get started.

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

Getting Started

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 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.

2 – Under Data structure, select My data structure.

3 – Under JSON string, select Data.

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 name.
Under (B), select gender.
Under (C), select house.
Under (D), select dateOfBirth.

The parameters that appear here are from the connection you added earlier. 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 Ok. Now it’s time to run and process the integration.

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

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

Make 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 Make 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 Ok. 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!

Import Facebook Ads to Looker Studio
Import GA4 data to Looker Studio

Conclusion: Connect JSON API to Google Sheets

Send JSON API to Google Sheets. These are the methods that integrate third-party tools. 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.