Search API Connector Documentation
Use Cell Values in Requests
When creating a custom request through the API Connector extension for Google Sheets, inputs 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
- Request body
- JMESPath expression
- 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.
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
- In the API Connector sidebar, construct your API request URL to include your date fields from the Inputs sheet, e.g.
- 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.
+++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.
- The ImportAPI() Custom Sheets Function is an alternate method of running API requests based on a cell.