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.
- Reference cell containing full value
- Combine fixed values and cell references
- Reference a list of cells
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:
You can also reference cells in your Header fields and POST request bodies:
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 Multiple 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:
- 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
- The ImportAPI() Custom Sheets Function is an alternate method of running API requests based on a cell.