Search API Connector Documentation
Use Cell Values in Requests
When creating an API request, inputs can be based on the values within your spreadsheet. There are two ways to use cells in your requests:
- Reference cells in a single request, e.g. enter a date into your spreadsheet and run an API request that plugs in the value from that date cell
- premium Reference cells in multi-query requests, e.g. create a list of campaign IDs in your sheet and cycle through them, running an API request for each campaign in the list
- Before you begin
- Reference cells in a single request
- Reference cells in multi-query requests
Before you begin
Click here to install the API Connector add-on from the Google Marketplace.
Reference cells in a single request
Single cell references can be used almost anywhere:
- Preset input fields
- Request URLs
- Request bodies
- JMESPath expressions
To insert cell references into your requests, wrap the cell references 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.)
- A reference to cell B2 in a tab called QuerySheet would be written like this:
- Cell references can also be combined with other values or other cell references, e.g.
Instead of hard coding dates into your requests, you can reference a Sheets cell containing a date function. That way you never need to manually update your API URL, since the request will automatically pull in the latest date from the Sheets cell. Every API will be a bit different, but the basic process is as follows:
- Run an initial request to fetch historical data
- Add a formula in a new tab (let's call it Date) that returns the date you want to use in your next request. That may be yesterday, e.g.
=today()-1, or the maximum date from the prior response, e.g.
- Now enter a reference to that date cell in your request using the syntax
- Switch to append mode so new response data goes to the end of your sheet
- Now every time you run your request, it will automatically use the latest value from your date cell.
If you're referencing a cell through a date input field in a preset integration, API Connector will automatically convert date cells to the right format.
However, if you're creating your own custom request, make sure you explicitly match the format requirements of the API with a function, as changing the display format through Google Sheets' format menu does not change the underlying format of the cell. For example, if the API requires dates in yyyy-mm-dd format, use a function like
Troubleshooting: If your request contains cell references, and fetches data for "nearby" dates (e.g. 11/30 when your date is written as 12/1), it may indicate an issue with time zones. To avoid this issue, use the
=text() function to force your date into a non-dynamic string (as shown above for custom requests).
Reference cells in multi-query requestspremium
Note: This section applies only to custom requests. Multi-query cell references aren't available for requests configured through the preset application menu.
When you reference a list of cells, API Connector will cycle through the requests and print out the results one after another in a single sheet. This is called a multi-query request (for more information, check this article: Multi-Query Requests)
Multi-query cell references can be used as follows:
- Run requests for a list of URLs, e.g. GET data for each campaign ID in a list
- Run requests for a list of request bodies, e.g. create new project tasks by sending multiple POST request bodies to the same URL
- Run requests for a list of URLs and request bodies, e.g. update inventory by running down a list of product URLs and sending a different PUT request body for each one.
Syntax - list of request URLs
Cell references should be listed one after the other on separate lines:
You can also use a shorthand method where the start/end cells are separated with a colon, e.g.
https://api.com?site=+++Sheet1!A1:A10+++is permissable, while
https://api.com?site=+++Sheet1!A1:A10+++&from=+++Sheet2!A1:10+++is not. If you have multiple variables, first construct the URLs in your Sheet, and then reference that list.
Syntax - list of request bodies
In some cases, you may want to cycle through a list of
PATCH requests, sending a different request body for each API call while the request URL stays the same.
+++Input!A1:A10+++ shorthand method shown above is for request URLs only. Multiple request bodies should be entered one by one, with each request body separated by the word
:::BREAK::: and the "Multiple request bodies" option ticked.
+++Sheet1!A1+++ :::BREAK::: +++Sheet1!A2+++ :::BREAK::: +++Sheet1!A3+++
Syntax - list of request URLs and request bodies
In this type of request, you cycle through a list of request URLs, where each request URL also has its own request body. To do this, combine the two syntaxes above.
The first URL will be associated with the first request body, the second with the second, and so on.
Handle varying request body counts (+ demo sheet)
Sometimes it's convenient to concatenate all the request bodies into a single cell, such that you only need to reference a single cell in your request. This is especially useful if you need to cycle through varying numbers of request bodies. This sheet has an example setup (click to make a copy).
+++syntax is only for use within the API Connector sidebar; you can't reference a cell that itself contains
- If you wish to construct URLs in your Sheet, use ampersands, e.g.
="https://api.com?startDate="&A1&"&endDate="&B1. You can then use
+++syntax to reference that URL from the sidebar.
- When you run multiple requests, a 2 second delay is automatically inserted between requests to avoid rate limits.
- The ImportAPI() Custom Sheets Function is an alternate method of running API requests based on a cell.