Search API Connector Documentation
Use Cell Values in Requests
When using the API Connector add-on for Google Sheets, API requests can be based on dynamic or fixed values within your spreadsheet. This is a very flexible function that enables you to, for example, use an automatically updated date in a spreadsheet as the start or end date of a query.
You may reference cells almost anywhere:
- API URL path
- Headers
- Request body
- JMESPath expression
Contents
- Reference cell containing full value
- Combine fixed values and cell references
- Reference a list of cells
- Example: dynamic dates
- Notes
Reference cell containing full value
To use this function, wrap your cell reference in 3 plus signs on either side. (The plus signs tell API Connector that this is a cell reference and not part of the original URL.) So a reference to cell B2 in a tab called QuerySheet would be written like this: +++QuerySheet!B2+++

Combine fixed values and cell references
Your request field can contain a combination of fixed values and cell references. Just wrap the cell reference piece in 3 plus signs as described above, like this:

Reference a list of cells
You can both reference cells and stack multiple requests together (paid feature). This is useful for running through a list of requests, like this:
https://itunes.apple.com/search?term=+++Input!A1:A10+++
Example: dynamic dates
Instead of hard coding dates into your requests, you’ll often find it easier to reference a Sheets cell containing a dynamic date. That way you don’t need to manually update your API URL.
Let’s take the Harvest API as an example. Every API will be a bit different, but this will demonstrate the process:
- Check the documentation to see what date fields are available for your API. In this case, Harvest’s documentation shows a few different date fields you can work with:
- Create an Inputs tab in your sheet containing your date, making sure you match any format requirements shown in the documentation.
Setting the format via the display toolbar does not change the underlying format. So if the API requires an ISO 8601 format like “2021-01-26”, explicitly set the format with a function like=text(today()-1,"yyyy-mm-dd")
. - In the API Connector sidebar, construct your API request URL to include your date fields from the Inputs sheet, e.g.
https://api.harvestapp.com/v2/time_entries?from=+++Inputs!B2+++&to=+++Inputs!B3+++
- Again, make sure to use the date format and parameters required by your API. Harvest uses from and to, but other APIs may use start_date, end_date, or something else.
- Now every time you run your request, it will use the dynamic date fields from your sheet.
Notes
- The
+++
syntax is only for use within the API Connector sidebar; you can’t reference a cell that itself contains+++
syntax. If you want to construct a URL in Sheets, use Google’s native cell-referencing syntax, e.g.="https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11111111111&start-date="&'Dates'!A2&"&end-date="&'Dates'!B2&"&metrics=ga:transactions,ga:transactionRevenue"
. - The ImportAPI() Custom Sheets Function is an alternate method of running API requests based on a cell.
can post requests be sent?
Not currently, but this feature is coming very soon!
This feature is live now : )
So, linking to cells for a POST is now possible? If so, can the body of a request be dynamic based with fields tied to cells?
Sorry, currently only the API request URL can be based off a cell, not the request body.
Awesome Ana, thanks for making the POST feature possible 🙂 looking forward to link cells in the request body too!!
You can now base the request body on a cell. I’ve updated the documentation above to reflect that.
Hello Ana, firstly I would like to thank you for your contribution, this add-on is simply a great work!
Quick question – would it be possible to Trigger the API call when there is a change in the Cell Value? There are few scenarios where this would be helpful.
Thank you for your kind words, I appreciate that. Triggering API calls on cell changes is an interesting idea, I just emailed you to ask for more detail on what you’re looking for.
I’ve now added a feature to trigger API calls based on cell changes. Please see this article for more info: https://mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/
Can you reference a cell in the required headers? APIs often require authentication tokens in the header, which then require an API POST to get that token for their temporary period of time (usually 5min). Great add-on by the way, makes writing APIs super easy!
Disregard, I saw you already added this! Thanks!
Hello, I am new to API calls. If I wanted to use parameters how would I adjust for that in the API add-on. Example, I want to do an API call to get the value of a vehicle. Here is an example from the API source: GET
http://api.carsxe.com/marketvalue?key=&vin=1FT8X3BT0BEA61538&mileage=120000&interval=180&format=json
Are the variables HEADERS? Can you assist me?
You would add that entire URL into the API URL Path input field, making sure to include your API key in the empty space where it reads
?key=
(the API documentation should tell you how to get your API key). The documentation will also tell you if that API requires headers; if not, you’d just leave that section blank. I suggest checking out the Quick Start article for a complete example of how to set up and run an API request.Is there a way to stack calls similar to how we use a range of cells such as:
http://dnd5eapi.co/api/equipment/+++EquipmentList!B3+++/
http://dnd5eapi.co/api/equipment/+++EquipmentList!B4+++/
http://dnd5eapi.co/api/equipment/+++EquipmentList!B5+++/
but instead do this:
http://dnd5eapi.co/api/equipment/+++EquipmentList!B3:B5+++/
No, but that’s actually a very good idea! Thank you for suggesting. I’ll add that to the to-do list for a future release.
Thank you for the quick reply!
This has now been implemented : ) Please check this article for more information.
This is a great tool. Is there a way to stack cells in body as well? I tried with +++Sheet1!A2:A5+++ but returns only A2.
Currently you can only stack URLs, not POST bodies. Sorry for the inconvenience but I hope that helps clarify. I’ll consider adding that feature in the future.
You can now stack POST bodies too, you can see more about that in this article (check the section called “Multiple POST Bodies”).
doesnt it work on jmespath??
The JMESPath field does not currently accept cell references, cell references can only be used in the request URL, headers, and POST body fields. Sorry for the inconvenience, I’ll try to add that in the future.
Update: this is now available.
Dang it!
This is exactly what I need so I can filter down a big dataset to only 1 value…
Any hope of getting it soon?
Oh, I found another comment and then got it to work!
Thanks for all the sick support/updates.
hahah, glad you found it!
thank you. please add that.
Added! You can now reference cells in your JMESPath expression too.
Is it possible to output the result to a specific cell?
I think you have two options:
1) pull data into a ‘raw data’ sheet, and then use VLOOKUP or QUERY functions to pull the data exactly where you want it in a second sheet.
2) use API Connector’s custom IMPORTAPI function, which lets you pull data to any cell you want.
Update: You can now set a specific starting cell for the response.
Is it possible to reference a cell to a nested value:
"dateRanges": [{"startDate": "2021-10-27", "endDate": "2021-11-02"}],
"metrics": [
I am trying to set the startdate and end date based on a cell
Sure, you’d just make your reference like this:
"dateRanges": [{"startDate": "+++Sheet1!A1+++", "endDate": "+++Sheet1!A2+++"}]
Thanks! Unfortunately, I keep getting a 400 error. I am using this as URL Path for the Google Analytics API:
https://analyticsreporting.googleapis.com/v4/reports:batchGet?"dateRanges": [{"startDate": "+++Inputs!B2+++", "endDate": "+++Inputs!B3+++"}]
The GA API uses a URL of
https://analyticsreporting.googleapis.com/v4/reports:batchGet
for all API requests. If you want to add date ranges you’d include them in the POST body like this:{"reportRequests":[{"viewId":"11111111111","dateRanges":[{"startDate":"+++Inputs!B2+++","endDate":"+++Inputs!B3+++"}],"metrics":[{"expression":"ga:users"},{"expression":"ga:sessions"}],"dimensions":[{"name":"ga:browser"},{"name":"ga:country"}]}]}
Please check this article for more info.
Great app. Oh my you have done well. So, I have my API call working with static data within the body. I get the results I expect. I have also put in a variable in place of a static piece of data and it too works great. I am wondering if there is a way that a conditional could be set up to look at new data based on a cell change, and post the API call pulling the data. Example. I have a spreadsheet that we used to sell tickets like a box office would do. We enter Name, Email, next to a Seat. The seat is the data that I need to flow into the API, but only if the Name and Email is completed. I can of course do this manually, but hoping it would run as a service, or on a time schedule. The API books the seat on the server so it is no longer available for booking. Thanks again for this great tool.
Hey John, thank you! I’m glad you like API Connector.
There are 2 ways you can trigger API calls automatically: through scheduling (which will update at some time interval), and through the IMPORTAPI function, which can update when a cell changes. If you can add an IF (or similar) function around your Sheets functions, such that the cell only changes upon the condition you’re looking for, then it looks like IMPORTAPI would work for this use case. Feel free to reach out to support if you’d like more specific help setting it up.
Hi Ana,
Can I iterate the date in the body rather than in the URL as a parameter somehow?
Sorry, currently we can only cycle through cell ranges in the URL, not in the request body.
Hi Ana,
Can you make it possible? Since the API we are using only allow it via request body in POST method. It will be a huge help.
Regards,
There are some challenges with that, but I’ll add it to the feature request list. For now, you can cycle through multiple request bodies as described here.
Hi Ana,
I was hoping to be able to add a custom date-time, which I could then cell-import to the API URL. However, I received an Error 400 because Binance API detects the “+++” as its own separate input, resulting in an error from Binance API. How can I resolve this? Thanks in advance.
1. I want to get the candlestick data from a specific date… for instance 27 December 2021 14:00:00 (1640613600)
2. I multiplied the Unix by 1000, because Binance API’s Unix has three extra zeros (1640613600000)
3. I referenced the Unix000 Cell to the API URL’s startTime as +++Sheet1A1+++ (https://binance-docs.github.io/apidocs/spot/en/#kline-candlestick-data)
4. Error 400, Illegal Character
* +++Sheet1!A1+++ (typo, I missed the ! on my Comment)
Hey Geraldo, I don’t think Binance detects the +++ as an input since we don’t send the +++, we only send through the referenced value.
So I suspect there’s some issue with your URL or the value in the cell.
Can you please share your request URL or a link to your sheet so I can take a look?
Can a POST request include a dynamic cell reference when used within an IMPORTAPI() function? I would like to be able to copy the function down a column and have the request reference cells within that row only.
If you mean the POST request URL, then there’s no problem copying it down to reference subsequent cells in the row. But if you mean the request body, then there’s currently no way to do that because IMPORTAPI only lets you enter a dynamic URL, not request body. To use dynamic request bodies please check out the multi-query function instead.
Is the development of relative cell references inside of a POST request body possible?
Maybe in the future, but for now please use the :::BREAK::: delimiter between request bodies.
Hello.
The following error occurs:
IMPORTAPI Error: Request ‘requestName’ doesn’t exist!
I ran this Request in the sidebar Manage Api Requests, and the result is positive.
Is your request actually named requestName? The name you use in the IMPORTAPI function needs to match the name you’ve saved the request as.