Back to Blog

How to Import JSON to Google Sheets Without Coding

There are two common ways to import JSON data from RESTful APIs into Google Sheets. The first one is to write a Google Apps Script. The second way is to create a custom function in the Script Editor. In this blog post, we’ll introduce the third way for which you don’t need any coding. You can fetch your JSON data, place it in a spreadsheet and even schedule recurrent imports. Interested? Then read on and explore more about how you can do this. 

Why convert JSON to Google Sheets?

JavaScript Object Notation or JSON is a text-based format used to transmit structured data from the server to the client via HTTP. The format provides quick and easy parsing, and is deemed better than conventional XML. JSON APIs can be found in many versatile products, such as Recurly, Typeform, Salesforce, Slack and so on. So, you can use those APIs to get the data into a spreadsheet. And why would you do that?

Google Sheets may be one of the best solutions for reporting purposes. It’s free, cloud-based and doped with a bunch of useful features and functions. You can build dashboards and pivot tables, make complex calculations and visualize your data as you wish. All you need to do is to pull data from your app into Google Sheets. 

What is a JSON Client Importer and how can it help you fetch data? 

Coupler.io is a Google Sheets add-on that lets you integrate spreadsheets with different data sources, such as Airtable, Xero, Jira Cloud, and so on. Visit the Coupler.io homepage to learn more about the available integrations. Each integration is implemented through an importer to be set up. For importing JSON data from HTTP APIs, you’ll need to set up a JSON Client Importer. 

JSON Client Importer gives you the power over data. It allows you to integrate Google Sheets with different platforms, such as Salesforce, Typeform, HelpScout, and more. The only prerequisite is having a RESTful API available. Check out some blog posts, where we explain the JSON Client parameters to import data from certain apps:

Using APIs requires technical knowledge. To configure and master JSON Client Importer, you need to understand the principles of how your data source shares its data. You may need to dive deeper into the topic, since every API requires specific workarounds, such as handling authentication, pagination, rate limits, and so on. That’s why data analysts, engineers, and other tech-savvy specialists will see the true value of the JSON Client Importer. 

Can I still use the JSON Client Importer if I don’t have a technical background?

Sure you can! This article will explain the core points you need to take into account when working with the JSON importer. This will build the basis, but you will need to have a much wider understanding of HTTP APIs to maintain the integration, as well as add new ones. If you’re ready to invest your extra effort to try the JSON Client Importer, remember that our development team is here to support you in your journey. Feel free to email us (coupler@railsware.com) to share your success story or ask for help. 

Coupler.io is evolving every day, so it would be good to know which data source you connect to Google Sheets. Your use case can help us decide which dedicated importer will be created next.

How to import JSON data from an HTTP API into Google Sheets

Install Coupler.io

Install the add-on via this link or find it on the G Suite Marketplace:

Set up a JSON Client Importer 

Open Coupler.io in the Add-ons tab of a spreadsheet, click on the +Add Importer button and choose JSON Client. To set it up, you need to fill in certain parameters. 

Required fields

Title – add the name of your data source. 

Sheet Name – type in the name of your sheet.  

JSON URL – insert the URL of the endpoint to import from. Where do you get it? Check out the RESTful API documentation of the platform you need to export data from. The base URL for making an HTTP request usually looks like this:

https://api.{platform-domain}/

Automatic data refresh – enable this function and pick the schedule that you want to use for Coupler.io to update data in your spreadsheet.

Additional fields 

HTTP Method – you can pick an HTTP method for making a request based on the documentation of your data source platform. Usually, GET is the default method.

HTTP headers – you can apply specific HTTP headers for your request. For example, the Authorization header lets you specify credentials required to make an HTTP request. 

URL query string – you can assign values to specified parameters.

Request body – if your request method is POST, PUT, PATCH or DELETE, you can add data to your request to be sent to API. In the blog post, “Clockify to Google Sheets”, you can check out what it looks like.

Fields – you can specify the fields (columns) to be imported to your spreadsheet.

Path – you can select nested objects from the JSON response. 

Check out the Coupler.io knowledge base to learn more about Additional fields.

Settings

Mode – choose a data import mode: replace or append.

  • Replace – fully replaces data at further re-imports.
  • Append – appends data below at further re-imports.

Cell address – specify a start cell where the data will be imported.

Last updated – if Yes, you’ll get an additional column with time when rows have been updated last.  

Once you’ve filled in all the required fields, click Add Importer.

That’s it for the theory. Now, let’s see how the JSON Client Importer works in practice. For this, we’ll try to import data from two different platforms – Trello and Typeform. 

Use-case #1: Importing JSON data into Google Sheets from Trello

Let’s say you need to integrate your Trello data with Google Sheets. First, go to Trello’s RESTful API documentation to find specifications required to set up the JSON Client Importer. Here you will learn the URL to make a GET request to list all the boards associated with your account:

https://api.trello.com/1/members/{memberID}/boards?key={yourKey}&token={yourToken}

This is a JSON URL to insert to the JSON Client Importer. You need three variables to fill in:

  • {memberID} – the username of your account. You can find it in the Profile and Visibility tab of your account 
  • {yourKey} – a developer API key. Get it at this link:  https://trello.com/app-key 
  • {yourToken} – an API token. Click the Token link on the Developer API Keys page, and the token will be generated in a moment. 

Here is how a final link should look:

https://api.trello.com/1/members/your-username/boards?key=cad024c7i4vab2672157cgfh3485c1af&token=25fv35db762c7cbe4fb4aab872da735cdf151c25bf5dca6bb24a3afbdc3dc42z

Insert the link in the JSON URL field, click Add Importer and run it afterwards. This is what you’ll get:

If you want to import cards from a particular board, you’ll need to amend your JSON URL according to the Trello’s API documentation: 

https://api.trello.com/1/boards/{idBoard}?cards=all&key={yourKey}&token={yourToken}

For more importing capabilities, read Trello’s REST API documentation in detail. 

Use-case #2: Importing JSON data into Google Sheets from Typeform

Let’s check out another data source to import JSON data from – Typeform. Traditionally, you should begin with Typeform’s API documentation. Here, you’ll learn the base URL:

https://api.typeform.com/

The endpoint (JSON URL) to request all forms responses of a type form is the following:

https://api.typeform.com/forms/

As you can see, there are no authentication strings attached to this URL. For Typeform’s HTTP request, you’ll have to use the Authorization HTTP header.  

Authorization: Bearer {your_access_token}

You can read about how and where to get your Typeform’s Personal access token here. 

Fill in the JSON URL and the HTTP header fields in the JSON Client Importer and run it:

To import all responses of a particular form, use the following JSON URL:

https://api.typeform.com/forms/{form_id}/responses

{form_id} – you can find the ID of your form right in the form URL. For example, in

https://railsware.typeform.com/to/NfGhMX

the form ID is NfGhMX.

For more importing capabilities, read Typeform’s REST API documentation in detail. Check out also how we built a Post-COVID Destinations Dashboard using Typeform, Google Sheets and Data Studio. 

To wrap up: is the JSON Client Importer good for data import automation?

Setting up the JSON Client Importer is not a walk in the park, but it’s not rocket science either. However, we can’t say that it’s a ready-to-use solution like Xero Importer or Jira Importer provided by Coupler.io. JSON Client Importer gives you flexibility in configuring the way to fetch data from your data source. If you invest extra effort to master it, it will pay you back double. Feel free to provide your feedback in the comments, and good luck!

Back to Blog

Access your data
in a simple format for free!

Start Free