API Connector Documentation
Multi-Query Requests
Multi-query requests let you stack multiple requests together and run them one after the other as a unified block. For simplicity, the examples below show how to run multi-query requests by entering values directly, but all multi-query requests can reference cell values instead.
When you run a multi-query request, API Connector will cycle through the requests and print out the results one after another in a single sheet. There are three ways to run multi-query requests:
- Run requests for a list of URLs, e.g.
GET
data for each campaign ID in a list - Run requests for a list of request bodies, e.g. create new project tasks by sending multiple
POST
request bodies to the same URL - Run requests for a list of URLs and request bodies, e.g. update inventory by running down a list of product URLs and sending a different
PUT
request body for each one.
Contents
- Before you begin
- Run requests for a list of URLs
- Run requests for a list of request bodies
- Run requests for a list of URLs and request bodies
- Use cell values in requests
- Notes
Before you begin
Click here to install the API Connector add-on from the Google Marketplace.
Run requests for a list of URLs
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 were run in a row, with just the "term" parameter changed for each one.
The above method will work for GET
requests as well as POST
/PUT
requests that send the same request body to different URLs.
Run requests for a list of request bodies
In some cases, you may want to cycle through a list of POST
, PUT
, or PATCH
requests, sending a different request body for each API call while the request URL stays the same.
Multiple request bodies should be entered one by one, with each request body separated by the word :::BREAK:::
and the "Multiple request bodies" option ticked.
Run requests for a list of URLs and request bodies
In this type of request, you'll want to cycle through a list of request URLs, where each request URL also has its own request body. As above, each URL should be on the same line, and each request body should be separated by the word :::BREAK:::
with the "Multiple request bodies" option ticked.
The first URL will be associated with the first request body, the second with the second, and so on. In this example screenshot, the list of request URLs is represented by a cell reference.
In these requests, the count of request URLs must match the count of request bodies.
Use cell values in requests
Instead of directly entering values into the request input fields, you can instead reference values from the cells in your sheet: Use Cell Values in Requests
Notes
- A 2 second delay is automatically inserted between requests to avoid rate limits.
- To easily identify where each request starts, tick the "Add request URL" or "Add timestamp" boxes under Output options (leave the "Each row?" option unticked). This will print out the URL and/or timestamp at the start of each new request.
- Each request in a multi-query request block will consume one of your monthly runs. This is in addition to any monthly runs consumed through pagination.
Hello
How do I use multiple POST bodies?
API URL path
https://app.klenty.com/apis/v1/user/emailEngagements?
POST Body 1:
{
"startDate" : "2019-01-01",
"endDate" : "2020-10-14",
"cadenceName" : "Cadence 1"
}
POST Body 2:
{
"startDate" : "2019-01-01",
"endDate" : "2020-10-14",
"cadenceName" : "Cadence 2"
}
Klenty API Documentation:
http://support.klenty.com/en/articles/3197537-klenty-api
Thank you
Hi there, you would need to run these as separate requests, as currently only URLs can be stacked (not POST bodies).
You can now stack POST bodies too, I’ve updated the article to reflect that.
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?
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)
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.
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
Thank you for the comment! 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.
Thanks for the quick response Ana!
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'?
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.
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!
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?
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
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.
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?
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").
Hi Ana,
How can I iterate the date in the body of my POST API to get data into Google Sheet using API Connector?
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)
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
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.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?
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.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.
Can you please make sure you've cast your date cell into the correct format, like this?
=TEXT(A1,"yyyy-mm-dd")
In the cell B9 format =Today() in the B10 format=Today()+1. Output yyyy-mm-dd
Please use the TEXT formula, as the output you see is not necessarily the actual format. This article has more info (check the section on dynamic dates)
Anna, thanks, I made the format and the request is being processed.
=Text(bible!B9;"yyyy-mm-dd")
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?
Wow, 1 request per minute is really limited. We currently don't have adjustable delay times, I've noted this as a feature request.
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 bodyBODY:
{
"value":{
"INFO":"B1",
"INFO2":C1,
"INFO3":D1
}}
FOR SECOND ROW:
URL:
xxx/api/items/A2
with bodyBODY:
{
"value":{
"INFO":"B2",
"INFO2":C2,
"INFO3":D2
}}
etc.
Is this possible/
Thanks
Sorry, it's currently not possible to combine multiple request URLs with multiple request bodies.
Update: you can now combine multiple request URLs with multiple request bodies, I've updated the article with more info.
Hi Ana,
Is there anyway to get rid of the blank rows that's created between each request output?
There shouldn't be a blank row as the multiple request function generally starts printing out immediately after each request completes. Can you please verify that there is nothing at all in those rows between each request output?
Hi Ana,
great tool!
But very sorry to realise that "multiple request URLs with multiple request bodies" is not possible after signing up for Pro. Any idea when this will be possible?
Oops, sorry about that, please send a message to support if you'd like a refund. It's high on the to-do list, but we're finishing some other items first. It should be ready within the next month or so.
Update: you can now combine multiple request URLs with multiple request bodies (info).
Would it be possible to add a retry till successful feature? I have a spreadsheet with 2,000 JSON PUT requests and I find that if I do more than 30-40 at a time with the multiple requests I'll get a 503 error on the server side. It would be nice if I could load my complete list and then have it work to complete and if it encountered a failure, it would attempt again until successful.
API Connector doesn't currently have that ability, but thank you for the suggestion. I've added it to the list of features for future consideration.