Create API Request Based on a Cell
One of the advantages of using the API Connector add-on for Google Sheets is that 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 in the API URL path, Headers, and POST body fields.
Content
- Method 1: Reference cell containing full value
- Method 2: Combine fixed values and cell references
- Notes
Method 1: 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+++
You can also reference cells in your Header fields and POST request bodies:
Method 2: 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:
Notes
- When referencing dates in cells, make sure you match your API’s format requirements. If your API requires a date format like ‘2020-08-28’, it may not work if you use a formula like
=today()-1and set the display format in the toolbar. Instead, set your date explicitly with a function like=text(today()-1,"yyyy-mm-dd"). - For additional efficiency, you can both reference cells and stack multiple requests together (paid feature). This is useful for running through a list of requests.
- 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.