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 by Coupler.io 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 others. Each integration is implemented through an importer to be set up. For parsing 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 export 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 make the following steps. 

1. Fill out the “Title” field

Name your importer. 

2. Set up your data source

2.1. Fill out the “JSON URL” field

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}/

Click Show advanced to set up optional parameters for your data source:

  • HTTP Method – you can pick an HTTP method for making a request based on the documentation of your data source platform. 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, “Post Messages to Slack from 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.

3. Set up your data destination

3.1. Fill out the “Sheet name” field

Type in the name of your sheet.  

Click Show advanced to fill out the “Cell address” field. It lets you specify a start cell where the data will be imported.

4. Configure importer’s settings

4.1. Enable the Automatic data refresh

Enable this function and customize the schedule that you want to use for Coupler.io to update data in your spreadsheet. Check out more about Automatic data refresh.

Click Show advanced to set up optional settings:

  • Import Mode – choose a data import mode: replace or append.
  • Add “Last Update” column – toggle this parameter on, if you want to add a column specifying the date of the last data refresh.

Check out the Coupler.io knowledge base to learn more about the JSON Client importer.

Once you’ve completed those steps, click Save to save the parameters or Save & Run to save the parameters and run the initial import right away.

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

Use-case #1: Import 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 and click Save & Run:

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: Import JSON data into Google Sheets from Typeform

Let’s check out another data source to export 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

Comments are closed.

Access your data
in a simple format for free!

Start Free