How to get JSON data from any API into Google Sheets

Step 1 : Add new Data Source

To get data from any API endpoint, we need to add the source data and credentials (if needed). Go to Data Sources section and click on the Add button, then proceed with the following steps :

• In the appeared form, provide a name for the Data Source and then Select API Bridge from "Beta -> API Bridge".
• And then provide the Base URL of that endpoint. (For example: https://gorest.co.in or https://gorest.co.in/public-api)
  • Note : Make sure Base URL is not ended with '/'.
• Provide the Authorization credentials if needed. It includes,
  • Basic Authorization (Username and Password).
  • No Authorization.
  • Bearer Token.
• Provide Header fields if the headers should be included.
• If you are sure that the connection strings are correct, proceed to save by clicking on the Save Button. Otherwise, you could select Test Before Saving and select a method (GET/POST/PUT) from the Method dropdown and give a Suffix URL (For example: /public-api/products or /products). And then click on the Test and Save button to check if the connection strings are valid and Save the Data Source.
  • Note: Suffix URLs should start with a forward slash '/'.

Step 2 : Add a Query and get data into the sheet

Now that we have added a Data Source, let’s query that source (Endpoint) and get the data into the sheets. Go to Data Queries section and click on the Add button, then proceed with the following steps:

• Give a name for your query. Then select the Data Source you saved from Step 1. And then Select the Sheet in which you wanted to fill the data in.
• After that, select the method (GET/POST/PUT) from the Method dropdown.
• And then Specify a Suffix URL of the endpoint you want to get the data from.
  • Note: Suffix URLs should start with a forward slash '/'.
• You can provide a JMESPath optionally if you want to extract a specific set of data from the endpoint.

Learn more about JMESPath in How to use JMESPath?.

How to Format Output

• And then select the output format in which you want the data. By default, the Grid output format is selected.
  1. Grid Format – If a field is deeply nested in a JSON, the Grid format creates new rows for each nested row and copies down other respective columns.
  2. Flat Format – If a field is deeply nested in a JSON, the Flat format creates new columns for each nested field.

Learn more about Output Formats in Output Formats in API Bridge.

How to Handle Pagination

• Select the pagination if you want to make a request by pages, limits, or offsets. By default, None is selected. Other pagination types are,
  1. Page Parameter – Specified with the range between the pages. And the results will be fetched running through each page.
  2. Offset-Limit – Specified with the offset and limit value.

Learn more about Pagination Handling in Pagination Handling in API Bridge.

• Finally, click on the Run query button to get the Data into the Sheets and save the query.

Step 3 : (Optional) Query by referencing other cell(s)

You may want to query an endpoint with specific data from the sheets. You can do that by providing the range of the cells in the Suffix URL. To do that, just surround the Spreadsheet Formula Range with three plus signs "+++".

For example, from the above sample data, you may want to query with the cell data from A4 to A9 in Sheet 1. You can do that by giving the range as +++Sheet1!A4:A9+++ in the Suffix URL. You can also query data from more than one column by adding more than one cell reference.

Note: The number of selected rows in each column should be the same for all the cell references when more than one reference is used in the same query.

Learn more about formulas in How to use Spreadsheet Formulas in API Bridge query.