Search API Connector Documentation
This advanced feature of the API Connector add-on for Google Sheets enables you to stack multiple request URLs (or request bodies) together and run them one after the other as a unified block. API Connector will cycle through the requests and print out the results one after another in a single sheet.
- Before you Begin
- Why Use the Multi-Query Function?
- Example 1: List URLs in the URL input box
- Example 2: Reference Cells
- Example 3: Reference a Range of Cells
- Example 4: Multiple Request Bodies
Before you Begin
Click here to install the API Connector add-on from the Google Marketplace.
Why Use the Multi-Query Function?
Multi-query requests are commonly used to query the same (or similar) API endpoint multiple times, e.g. for different company IDs, different stock symbols, different time periods, etc. Instead of creating and saving a separate request for each query, you can run them in a row and send them all into the same sheet.
In addition, multi-query requests can account for simple API pagination, in which an API limits the number of records returned in each response and requires you to make multiple requests to retrieve the full set of data. There are a few common methods of pagination that can be managed via multi-query requests:
- page parameters
- offset-based pagination
- page directory pagination
Example 1: List URLs in the URL input box
To run multiple requests, list your URLs on separate lines. When you run the request, each subsequent response will be appended to the first empty row. In this example, 3 requests to the iTunes API are run in a row.
Example 2: Reference Cells
You can reference cells in the input box (info). Again, make sure each separate request is on a new line, to ensure API Connector recognizes it as a new request:
Example 3: Reference a Range of Cells
As a shorthand method, instead of referencing cells one by one, you can reference them as a range, like this:
As shown in the screenshots above, when you reference a cell, you can reference the entire URL or part of the URL.
Example 4: Multiple Request Bodies
While the above will work for cycling through a list of GET requests, POST, PUT, and PATCH requests usually require different request bodies. To cycle through a list of POST, PUT, or PATCH requests, you can enter multiple request bodies into the request body input box. Tick the “Multiple request bodies” checkbox and then separate each request body with the text delimiter
:::BREAK::: (the word BREAK surrounded by three colons on each side). This will tell API Connector that a new request body has started.
+++Input!A1:A10+++shorthand method shown above is for request URLs only. Multiple request bodies can only reference a single cell per reference, e.g.
Use cells to create request bodies
If you place your request bodies into cells, you can combine the multi-request body feature with cell references, such that your request body looks something like this:
+++Sheet1!A1+++ :::BREAK::: +++Sheet1!A2+++ :::BREAK::: +++Sheet1!A3+++
Handle varying request body counts (+ demo sheet)
You can further expand on this technique by concatenating all the request bodies into a single cell, such that you only need to reference a single cell in your request. This is especially useful if you need to cycle through varying numbers of request bodies. This sheet has an example setup (click to make a copy).
When you enable this feature, API Connector will automatically run through your list of request bodies, sending each request body to the same request URL and printing out each response consecutively into the same sheet.
- When you stack requests, response data from each query will be sent to a single sheet, with each subsequent request appended to the first empty row.
- When running multiple queries, a 1.5 second delay is automatically inserted between requests to avoid rate limits.