[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
- Fill out the Title field
- Set up your data source
- Set up your data destination
- 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
Click Show advanced to set up optional parameters for your data source
2.2. Select an HTTP Method
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