Search API Connector Documentation

Print

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.

Contents

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+++

api-connector-cell-references-img1

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:

api-connector-cell-references-img2

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:

https://itunes.apple.com/search?term=+++Input!A1:A10+++ 

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:

  1. 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:
    api-connector-cell-references-img4
  2. 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).
    api-connector-cell-references-img5
  3. In the API Connector sidebar, construct your API request URL to include your date fields from the Inputs sheet, e.g.
    https://api.harvestapp.com/v2/time_entries?from=+++Inputs!B2+++&to=+++Inputs!B3+++
  4. Now every time you run your request, it will use the dynamic date fields from your sheet.

Notes

Previous Set Request Method

30 thoughts on “Use Cell Values in Requests”

  1. 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?

    Reply
  2. 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.

    Reply
  3. 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!

    Reply
  4. 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?

    Reply
    • 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.

      Reply
  5. This is a great tool. Is there a way to stack cells in body as well? I tried with +++Sheet1!A2:A5+++ but returns only A2.

    Reply
    • The JMESPath field does not currently accept cell references, cell references can only be used in the request URL, headers, and POST body fields. Sorry for the inconvenience, I’ll try to add that in the future.

      Reply
    • If you run requests through the sidebar, they will always output to cell A1 (or the first empty cell in append mode). So I think you have two options:
      1) pull data into a ‘raw data’ sheet, and then use VLOOKUP or QUERY functions to pull the data exactly where you want it in a second sheet.
      2) use API Connector’s custom IMPORTAPI function, which lets you pull data to any cell you want. It has some caveats, though (you can read about those in the linked article)

      Reply

Leave a Comment