Search API Connector Documentation

Print

Multi-Query Requests

premium

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.

Contents

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:

  1. page parameters
    Page 1: https://www.demo.com/api?page=1
    Page 2: https://www.demo.com/api?page=2
  2. offset-based pagination
    Page 1: www.demo.com/api?offset=0&limit=100
    Page 2: www.demo.com/api?offset=100&limit=100
  3. page directory pagination
    Page 1: www.demo.com/api/1/
    Page 2: www.demo.com/api/2/

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.
multiquery-response

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:
multi-query-img2

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:
multi-query-img3

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.

The +++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. +++Input!A1+++

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+++
post-requests-img6

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).

Result

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.

Notes

  • 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.
Previous JMESPath Filtering
Next Pagination Handling

32 thoughts on “Multi-Query Requests”

  1. Love the API add-on… just purchased premium.

    I do have a quick question on having the API work around URL’s that come back invalid. I have a long set of Twitter usersname’s that I’m pulling back data on…. and every so often a user will close their account and their username becomes invalid. It then throws back an error in that larger batch and I’m required to fish through 100’s of usernames to find the invalid username.

    Can I have the script bypass an invalid username?

    Reply
    • Thank you for your purchase, I am glad you like API Connector.

      API Connector stops running when there’s an error, so what about using API Connector’s custom function ImportAPI() as a workaround? Basically you can list out all your usernames in one column, and then copy down the formula that calls the Twitter API. Unlike running multiple queries in a single request, it will run each request individually.

      To do this you first save an API request in API Connector. Let’s say it’s named “User”, then you can call that request through ImportAPI like this:
      =importAPI("User","https://api.twitter.com/1.1/users/show.json?screen_name="&A2)

      Reply
      • Update – I’ve modified the logic so API Connector will now keep running through a request list even when it hits an error. In those cases, it will just skip that request and print out an error notification into the Status area at the bottom.

  2. Hi Ana, thank you for the informative guides to you very useful API Connector tool.

    Could you advise how to pull 2 API References using your custom IMPORTAPI() Function as I have found this to be very well suited to my requirements.

    Can you add 2 (or more) URLS separated by a space, comma, ; or maybe |? (I’ve tried all these and don’t think they are the right way!)

    Thanks again

    Reply
    • Thank you for the comment! Sorry, multi-query requests can only be run through the standard sidebar method as shown above, ImportAPI() doesn’t have this feature. However you can “zip” through a list of URLs by listing them out on subsequent rows in your sheet, you can see an example of that in this article (check the section called “Example Referencing Multiple Cells”).

      Reply
  3. HI,
    @ “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.”

    Will this work also when output ptions is set to ‘overwrite’?

    Reply
    • Yep, it will work. Multi-query requests run as a block. So if you set it to overwrite, then the block of requests overwrite everything on the page, while if you set it to append, the block of requests append to the end of what’s already there.

      Reply
  4. Hi,
    I am sorry I mistakenly posted the comment above when I had not finished typing and I can not delete it.

    I am trying to get limit the number of response that is returned when the API fetches the data. Usually, it fetches 500 rows when using concatenate format and list them from ascending to descending order. However, I only want the last 50 rows and I also want them from descending to ascending. So I do not know what to add to the end of the request URL path to limit the number of rolls obtained and to sort it from descending to ascending order.

    Example: DATA FETCHED NORMALLY from URL
    ROW 1 – CELL A1= 1
    ROW 2 – CELL A2 = 2
    ROW … – CELL A… = …
    ROW 499 – CELL A499 = 499
    ROW 500 – CELL A500 = 500

    THIS IS WHAT I AM TRYING TO FETCH
    ROW 1 – CELL A1 = 500
    ROW 2 – CELL A2 = 499
    ROW 3 – CELL A3 = 498
    ROW … – CELL … = …
    ROW 50 – CELL A50 = 450

    Please Help!

    Reply
    • Hi there, every API is different so I would need a little more information to answer this. For example some APIs split data with a page number, some with an offset parameter, etc (you can see more about this in this article on pagination).
      Also, not all APIs provide an option to sort data, so you might need to sort it with a Sheets function instead.
      Can you please share which API you are using?

      Reply
    • The API URL that I was referring to is:
      https://api1.binance.com/api/v3/klines?symbol=BTCUSDT&interval=1h
      What happens is that when requested, 500 line responses is returned using concatanate. What am trying to do is to limit it from 500 to say 50 lines and resort them from descending to ascending order.

      Also, I want to run a second API together with the first API on one request but have the results displayed on different part of the same Google Sheet. The second API is:
      https://api1.binance.com/api/v3/ticker/price

      Reply
      • Hi Ibiye, you can see Binance’s API documentation here. It says that you can add a limit parameter like this if you only want 50 records:
        https://api1.binance.com/api/v3/klines?symbol=BTCUSDT&interval=1h&limit=50
        They don’t provide a sort option, though, so I suggest doing it in your sheet. For example, if your data comes into a sheet called “Binance”, create a new summary sheet that contains the function =sort(Binance!A:M,1,false). That will sort your data in descending order.

        Currently you can’t run API requests onto the same sheet because we clear each sheet before printing in a new API response. However you could pull the response into your summary sheet with a function like the one above, such that you have 2 API data sheets and 1 summary sheet. Hope that helps, let me know if I can clarify anything further.
        Update: you can now print API responses onto the same sheet, please check the settings under Output options.

  5. I don’t need stacking for the purpose of split post bodies, but rather to post an authentication request first (get a token response), use token in subsequent read. How can I always fire the GetToken Post before any GET automatically?

    Reply
    • If you set up scheduling, you can arrange your requests such that the GetToken request always runs before the data request. Please see this article for more info: (check the section called “Trigger Ordering”).

      Reply
    • Hey Atharva, each request body needs to be entered separately, you can’t cycle through a set of dates from within a single request body. So you would need to update the date in each separate request body, and then add each request body into your request. You could semi-automate this (or at least make it more convenient) by creating your request bodies in your sheet, then using a Sheets function so the request body’s date updates each time you copy the cell down, and then reference those cells in your request (separating each one with the :::BREAK::: delimiter)

      Reply
  6. Hi Atharva,
    I have tried to put my datetime of “12/6/2021 0:00:00” in cell A1 and its formula-caculated utc form of “1638720000” in cell A2. My idea is to refer A2 in API URL GET method, so that the user could only modify A1 when they want to get new data, which is much more friendly for my colleagues. Unfortunately, it didn’t work. Our API Provider has restricted that the input of datetime has to be utc time.
    Could you please tell me whether there’s possibility to realize my idea?
    Kind regards,
    Xing

    Reply
    • Sure, you can use a Sheets functions like =TEXT(A1,"yyyy-mm-dd") to put your date in the format you need, and then reference that cell in your API request, like +++Sheet1!A2+++T0:00:000. You can see an example in this article (check the section on dynamic dates), or feel free to message support if you’d like me to take a look.

      Reply
  7. Hi Ana – I’m trying to update the Request Body of my POST API referencing a column of cells. I would like to cycle through hundreds of cells. It would take a long time to include a reference to each cell with :::BREAK::: in between each cell reference. Is there an easier way to cycle through a range of cells?

    Reply
    • Sorry, there’s no other way to cycle through a range of request bodies, but you should be able to semi-automate it by creating your request body in, say, cell A1, then creating a reference to it in cell B1 like ="+++Sheet1!A"&row(A1)&"+++". Then in cell B2 enter =":::BREAK:::". You’ll need to adjust it for the structure of your sheet, but basically you end up with some functions that you can just copy down the sheet, and then you can copy/paste the entire block into API Connector. That way you don’t need to manually type your references or :::BREAK::: hundreds of times.

      Reply
  8. Hi Anna.
    I have a request with identification by token. I register it in the header.
    The request body contains:
    {"sid":"xxxxxxxxx",
    "from": "2022-02-12",
    "to": "2022-02-13"
    "id_team": "1654"
    }

    on the sheet from the cell, I take the value of the seed, the date today and tomorrow does not work.

    {"sid":"+++bible!B7+++",
    "from": "+++bible!B9+++",
    "to": "+++bible!B10+++",
    "id_team": "1654"
    }

    Help me please.

    Reply
  9. Fantastic tool, thank you. I am accessing an API that won’t accept more than one request per minute. is there a way to add additional delay time between requests?

    Reply
    • Wow, 1 request per minute is really limited. Unfortunately we currently don’t have adjustable delay times, sorry about that. I’ve noted this as a feature request and will try to add this as an option in the future.

      Reply
  10. Hello, I’m trying to understand if it’s possible to use a mix of
    the multi-query requests (using a value from a cell) and then multiple body information?

    I want to create a PUT formula that uses data from a feed I currently have.
    Column A has to go on the URL:
    xxx/api/items/{itemId}
    And the body changes by row
    {
    "value":{
    "INFO":"B",
    "INFO2":C,
    "INFO3":D
    }}

    So it would look like:
    FOR FIRST ROW:
    URL:
    xxx/api/items/A1 with body
    BODY:
    {
    "value":{
    "INFO":"B1",
    "INFO2":C1,
    "INFO3":D1
    }}

    FOR SECOND ROW:
    URL:
    xxx/api/items/A2 with body
    BODY:
    {
    "value":{
    "INFO":"B2",
    "INFO2":C2,
    "INFO3":D2
    }}

    etc.

    Is this possible/
    Thanks

    Reply

Leave a Reply to Ibiye Cancel reply

Table of Contents