[GSheets add-on] JSON to Google Sheets

Connect to any app or system that supports JSON API and seamlessly import any JSON data to Google Sheets with no special tools or scripts required.

The 4 steps to complete the setup

  1. Fill out the Title field
  2. Set up your data source
  3. Set up your data destination
  4. Configure importer's settings

1. Fill out the "Title" field

Name your importer. We recommend you pick a name that will enable easy in-app navigation for you and your team.

2. Set up your data source

2.1. Fill out the "JSON URL" field

Enter the JSON URL to export data from.
Click Show advanced to set up optional parameters for your data source

2.2. Select an HTTP Method

Pick one of the HTTP methods from the drop-down list: Get, Post, Put, Patch, or Delete. Read more about HTTP methods. 

2.3. Fill out the "HTTP headers" field

Specify an HTTP header that can be used in an HTTP request. HTTP headers do not relate to the content of the message. Read more about HTTP headers. 

Example:

Host: "developer.mozilla.org"  User-Agent: "Mozilla/5.0"<br> 
Authorization: Bearer xoxb-1h345kj24tr34kj35y63kjn243kjn2tf2N
Content-Type: application/json

2.4. Fill out the "URL query string" field

Enter a query string (a part of the JSON URL), which assigns values to specified parameters.

Example:

limit: 100
offset: 20

2.5. Fill out the "Request body" field

If your HTTP method POST, PUT, PATCH, or DELETE, you can specify the Request body - a part of an HTTP request that contains data sent to API.  

Example:

customer_id: 1   
customer_email: email@example.com
dateRangeStart: '2020-01-01T00:00:00.000Z'
dateRangeEnd: '2020-01-07T23:59:59.999Z'
sortOrder: ASCENDING
description: ''
rounding: false
withoutDescription: false
userLocale: en_US
customFields: 
weeklyFilter:
  group: PROJECT
  subgroup: TIME
exportType: CSV

Check out " How to use macros in JSON Client importer" to optimize your scope with the date and time values. 

2.6. Fill out the "Fields" field

Specify the names of the columns to import to your spreadsheet, as well as their order. Separate column names by commas. The default behavior: select all columns.

Example:

Title, Status

2.7. Fill out the "Path" field

Enter a string joined by dots to select nested objects from the JSON response. The default value: select all objects.

For example, all JSON objects look as follows:

{
  "data": {
    "items": [
      {"id": 1, "name": "First"},
      {"id": 2, "name": "Second"}
    ]
  },
 "page": 1,
 "per_page": 1
}

If you specify the following Path string:

data.items

The imported data will be limited to the following:

"items": [
  {"id": 1, "name": "First"},
  {"id": 2, "name": "Second"}
]

3. Set up your data destination

3.1. Connect a Google Account you want to import data to.

  • Pick an account you want to connect to.
  • Sign in to the chosen account.
  • Review the contents of access rights which you are granting to Coupler.io and press Allow.
  • Confirm your choices.
  • Read the "Close this window" message and close the pop-up.

3.2. Fill out the "Sheet name" field

Name the sheet, which will be receiving data. If the sheet with this name does not exist, Coupler.io will generate a new one for you.

Click Show advanced to set up optional parameters for your data destination.

3.3. Fill out the "Cell address" field

Type in the address of the first cell where the data range will be imported. The default value is A1.

4. Configure importer's settings

4.1. Enable the Automatic data refresh

  • Select Interval
  • Select Days of week
  • Specify Time range

Check out more about Automatic data refresh.

Click Show advanced to set up optional settings for your importer.

4.2. Pick the Import Mode

Follow this link to read more about choosing a data import mode.

4.3. Add the "Last Update" column

If you want to add a column specifying the date of the last data refresh, toggle the Last Update parameter on.

4.4. Save the changes

Click Save to save the parameters or Save & Import to save the parameters and run the initial import right away.

Book a free onboarding call! 
For any questions, feel free to email our team at  contact@coupler.io

Related Articles