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 in the API URL path, Headers, POST body, and JMESPath fields.
- Reference cell containing full value
- Combine fixed values and cell references
- Reference a list of cells
- Example: dynamic dates
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:
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:
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. Since this API requires ISO 8601 format, which is like ‘2020-08-28’, set the format with a function like
=text(today()-1,"yyyy-mm-dd"). Your request may not work if you don’t set your format explicitly (setting the format via the display toolbar doesn’t change the underlying format).
- In the API Connector sidebar, construct your API request URL to include your date fields from the Inputs sheet, e.g.
- Now every time you run your request, it will use the dynamic date fields from your sheet.
- The ImportAPI() Custom Sheets Function is an alternate method of running API requests based on a cell.