API Connector Documentation

Print

Use Cell Values in Requests

Last Updated OnApril 11, 2024
byAna
< Knowledge Base

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:

Contents

Before you begin

Click here to install the API Connector add-on from the Google Marketplace.

Reference cells in a single request

Usage

Single cell references can be used almost anywhere:

  • Preset input fields
    usecells-preset
  • Request URLs
    usecells-requesturl
  • Headers
    usecells-headers
  • Request bodies
    usecells-requestbody
  • JMESPath expressions
    usecells-jmes

Syntax

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: +++QuerySheet!B2+++
  • Cell references can also be combined with other values or other cell references, e.g. https://api.demo.com/report?start_date=+++DateInput!A1+++&end_date=+++DateInput!A2+++.

Reference dates

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. =max(Sheet1!B:B), etc.
  • Now enter a reference to that date cell in your request using the syntax +++Date!A1+++
    usecells-preset
  • 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.

Date formats

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 =text(today()-1,"yyyy-mm-dd").
api-connector-cell-references-img5

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 requests

Note: This section applies only to custom requests. Multi-query cell references aren't available for requests configured through the preset application menu.

Usage

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:

Syntax - list of request URLs

Cell references should be listed one after the other on separate lines:
usecells-multiurl

You can also use a shorthand method where the start/end cells are separated with a colon, e.g. +++Input!A1:A3+++
usecells-multiurl-shorthand

Syntax - list of request bodies

In some cases, you may want to cycle through a list of POST, PUT, or PATCH requests, sending a different request body for each API call while the request URL stays the same.

The +++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+++
post-requests-img6

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 multiple ranges

A request URL can reference one range of cells only, e.g. https://api.com?site=+++Sheet1!A1:A10+++ is permissable, while https://api.com?site=+++Sheet1!A1:A10+++&amp;from=+++Sheet2!A1:10+++ is not. If you have multiple variables, first construct the URLs in your Sheet, and then reference that list.

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).

Notes

  • The +++ syntax is only for use within the API Connector sidebar; you can't reference a cell that itself contains +++ syntax.
  • 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.
  • Named ranges can be used in place of fixed cell references. If you create a named range called Dates, the cell reference will look like +++Dates+++
  • The ImportAPI() Custom Sheets Function is an alternate method of running API requests based on a cell.

69 thoughts on “Use Cell Values in Requests”

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

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

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

  5. 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

  6. Is there a way to stack calls similar to how we use a range of cells such as:
    http://dnd5eapi.co/api/equipment/+++EquipmentList!B3+++/
    http://dnd5eapi.co/api/equipment/+++EquipmentList!B4+++/
    http://dnd5eapi.co/api/equipment/+++EquipmentList!B5+++/
    but instead do this:
    http://dnd5eapi.co/api/equipment/+++EquipmentList!B3:B5+++/

    Reply

  7. 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. I’ll try to add that in the future.

      Update: this is now available.

      Reply

  11. Is it possible to reference a cell to a nested value:
    "dateRanges": [{"startDate": "2021-10-27", "endDate": "2021-11-02"}],
    "metrics": [

    I am trying to set the startdate and end date based on a cell

    Reply

      • Thanks! Unfortunately, I keep getting a 400 error. I am using this as URL Path for the Google Analytics API: https://analyticsreporting.googleapis.com/v4/reports:batchGet?"dateRanges": [{"startDate": "+++Inputs!B2+++", "endDate": "+++Inputs!B3+++"}]

      • The GA API uses a URL of https://analyticsreporting.googleapis.com/v4/reports:batchGet for all API requests. If you want to add date ranges you'd include them in the POST body like this:
        {"reportRequests":[{"viewId":"11111111111","dateRanges":[{"startDate":"+++Inputs!B2+++","endDate":"+++Inputs!B3+++"}],"metrics":[{"expression":"ga:users"},{"expression":"ga:sessions"}],"dimensions":[{"name":"ga:browser"},{"name":"ga:country"}]}]}
        Please check this article for more info.

  12. Great app. Oh my you have done well. So, I have my API call working with static data within the body. I get the results I expect. I have also put in a variable in place of a static piece of data and it too works great. I am wondering if there is a way that a conditional could be set up to look at new data based on a cell change, and post the API call pulling the data. Example. I have a spreadsheet that we used to sell tickets like a box office would do. We enter Name, Email, next to a Seat. The seat is the data that I need to flow into the API, but only if the Name and Email is completed. I can of course do this manually, but hoping it would run as a service, or on a time schedule. The API books the seat on the server so it is no longer available for booking. Thanks again for this great tool.

    Reply

    • Hey John, thank you! I'm glad you like API Connector.
      There are 2 ways you can trigger API calls automatically: through scheduling (which will update at some time interval), and through the IMPORTAPI function, which can update when a cell changes. If you can add an IF (or similar) function around your Sheets functions, such that the cell only changes upon the condition you're looking for, then it looks like IMPORTAPI would work for this use case. Feel free to reach out to support if you'd like more specific help setting it up.

      Reply

  13. Hi Ana,

    I was hoping to be able to add a custom date-time, which I could then cell-import to the API URL. However, I received an Error 400 because Binance API detects the "+++" as its own separate input, resulting in an error from Binance API. How can I resolve this? Thanks in advance.

    1. I want to get the candlestick data from a specific date... for instance 27 December 2021 14:00:00 (1640613600)
    2. I multiplied the Unix by 1000, because Binance API's Unix has three extra zeros (1640613600000)
    3. I referenced the Unix000 Cell to the API URL's startTime as +++Sheet1A1+++ (https://binance-docs.github.io/apidocs/spot/en/#kline-candlestick-data)
    4. Error 400, Illegal Character

    Reply

      • Hey Geraldo, I don't think Binance detects +++ as an input since we don't send the +++, we only send through the referenced value.
        So I suspect there's some issue with your URL or the value in the cell.
        Can you please share your request URL or a link to your sheet so I can take a look?

  14. Can a POST request include a dynamic cell reference when used within an IMPORTAPI() function? I would like to be able to copy the function down a column and have the request reference cells within that row only.

    Reply

    • If you mean the POST request URL, then there's no problem copying it down to reference subsequent cells in the row. But if you mean the request body, then there's currently no way to do that because IMPORTAPI only lets you enter a dynamic URL, not request body.

      Reply

  15. Hello.

    The following error occurs:
    IMPORTAPI Error: Request 'requestName' doesn't exist!

    I ran this Request in the sidebar Manage Api Requests, and the result is positive.

    Reply

    • Is your request actually named requestName? The name you use in the IMPORTAPI function needs to match the name you’ve saved the request as.

      Reply

  16. First of all, Appreciated of you work.
    It help me a lot!

    Now I found I can't get cells dynamic with the following url.
    Cell B2 is content with the same format with "2022-6-2T16:40:28"

    https://testsite.com/api/v2/graphql?query={sales(startDate:+++Sheet1!B2+++,endDate:"2022-6-2T16:40:28"){edges{node{createdAt,items{quantity,item{id}}}}}}

    Any Idea I can reference to Cell B2 dynamically?

    Reply

    • Thanks! It looks like your startDate is missing the quotes around it. It should be query={sales(startDate:"+++Sheet1!B2+++",endDate:"2022-6-2T16:40:28")

      Reply

      • Hi Ana,

        Thank you for your reply.I did tried to add quotes around and successfully get data from the cell.
        However, format on the cell was "2022-05-31T02:00:00", when this cell has been referenced, it turns in to "%3A%22Tue+May+31+2022+02%3A00%3A00+GMT+0200+%28CEST%29%22%2C" in the query.
        which means :"Tue+May+31+2022+02:00:00+GMT+0200+(cest)".
        Anyway I can referenced data from the cell and format aswell?
        The DateTime scalar type represents dates and time values as specified by ISO 8601.

  17. Love the plugin - nicely done. I'd like to enter a relative variable into the POST request body so that the =IMPORTAPI() function can use a cell value within a request body. How can I achieve this?

    Reply

    • If I've understood your question correctly, it could work like this:
      1) Set your cell reference in the request body like this: {"key":"+++Sheet1!A1+++"}. Save the request as Data.
      2) Enter an IMPORTAPI function that calls the Data request and uses Sheet1!A1 as a parameter, e.g. =IMPORTAPI("Data","",A1)
      3) Now every time you change cell A1, it will trigger the request, automatically substituting into the request body whatever value you entered into A1.

      Reply

  18. Hi Ana,

    I'm currently having issues referencing a cell in my URL request.
    Here is a copy of what I'm sending (minus the key):
    https://portal.cloudposhq.com/api/v1/sales/?company_key=xxxxx&app_key=xxxxxx&limit=3000&filter[DATE]=+++DateFilter!B1+++

    The Date in the cell referenced(DateFilter!B1) is set to: 2023-01-09

    The request returns an error : 404 No results found.

    When i send the request without referenceing the cell and entering the date in the url reference, eg. https://portal.cloudposhq.com/api/v1/sales/?company_key=xxxxx&app_key=xxxxxx&limit=3000&filter[DATE]=2023-01-09

    The request runs not a problem.
    Am I entering the syntax incorrectly, I'm quite confused.

    Any assistance would be greatly appreciated. this is an amazing application.

    Reply

    • Hey Cameron! I suspect the problem is with the date format. Can you please try forcing the date into the yyyy-mm-dd format required by the API? In your DateFilter tab you can make a new cell with a function like =TEXT(B1,"yyyy-mm-dd") and then reference THAT cell instead of the original one. Please check if that resolves the issue.

      Reply

  19. Every time I run it it wipes the cell that contains the date clear, meaning I would have to re-enter the date after each run of the request.

    Any idea what's going wrong?

    Thanks!

    Reply

    • Hey Joe, by default API Connector will clear data to the right and below the API response. To avoid overwriting your date input, you can move it out of the way, e.g. put your date input into column A and set the API response destination to column B, or move the date input cell into a different tab altogether. Or, if you would prefer not to clear data in your sheet at all, you can toggle off the Clear sheet data option under Output options.

      Reply

  20. Hi Ana,

    Question regarding 'Syntax - list of request bodies': I've 100's and 100's of cells, and it's not feasible for me to manually enter each, like the example you've shown above:

    +++Sheet1!A1+++
    :::BREAK:::
    +++Sheet1!A2+++
    :::BREAK:::
    +++Sheet1!A3+++

    I've tried +++Sheet1!A1:A3000+++ and it's not working.
    What are my options?

    Reply

    • Hey Faraz, the +++Sheet1A1:A10+++ syntax is only for request URLs, so that's why it doesn't work for your request bodies. If you want to cycle through the list of request bodies, you have to separate each one with the :::BREAK::: delimiter. You can simplify /automate this in your sheet, the demo sheet has an example of how that could work (basically, list all your request bodies in a single column and create a formula that appends :::BREAK::: to the end of each one). With that said, I think you have a larger problem here, which is that there's almost no way you can run through 3000 API calls in Google Sheets. Google limits you to 6 minutes for the full chain of requests, which generally corresponds to about 20-200 API calls (really depends on the API but that's a general range). If you need to run 3000 API calls, you'll either need to split these requests up and run them at different times, or use a more powerful BI/data tool than Sheets.

      Reply

  21. I'm facing an issue with cell reference of the request body
    here is my original body which works fine

    {"test":1,"ID":221,"HID":3,"YID":295,"fromDate":"+++sheet1!A2+++",
    "toDate":""+++sheet1!A2+++","selectedId":0}

    however, when I do this it through an error

    Reply

    • I suspect your dates aren't in the right format, since what you see in your sheet isn't necessarily the underlying format that gets picked up by a cell reference. Please make sure you've explicitly forced the date into the format required by the API (info).

      Reply

  22. I'm trying to run the same API call for multiple ID's like such :

    https://app.myURL.com/patient/+++PatientID!A3:A6+++/eligibilities

    in the cells in the PatientID, A3 to A6 is something like this :
    708040980692993
    707686797082625
    706486824861697
    628155992834049

    it only runs for the 1st one.

    Reply

    • That configuration looks right to me, so I'm not sure what the issue is yet. Can you tick the "Add request URL" box under Output options to return the exact URL that was sent, and then run one of those problematic URLs individually to see what gets returned?

      Reply

  23. Is it possible to iterate through multiple API calls that uses cell references for both the URL and the Request Body?

    For example let's say macroid is column A:
    macroid1
    macroid2
    macroid3

    and for each macroid, I want to send a request body (column B) that will differ in values as well as structure:

    1st id request body :
    {"macro": { "action":[ {"field": fieldname1, "value": value1}]}}

    2nd id request body:
    {"macro": { "action":[ {"field": fieldname1, "value": value1},{"field": fieldname2, "value": value2},{"field": fieldname3, "value": value3}]}}

    3rd id request body:
    {"macro": { "action":[ {"field": fieldname1, "value": value1},{"field": fieldname2, "value": value2}]}}

    Ideally, each API call refers the corresponding URL id value and request body. Most examples with cell references seem like URL or Request Body. Is there a way for me to achieve the above?

    The json is above is a fake example the resembles Zendesk. I'm trying to update 300+ macros and their actions, but the endpoint requires that I specify all actions in the request body, which can vary the number of actions between each macro.

    Reply

  24. If i were to reference a range crossing multiple columns in the request for example +++Sheet1!A1:C3+++ will each column be processed as a new line in the multi-request?

    Reply

    • Yes, they will. The references are processed left to right and then top to bottom, so your example will be processed as if it were written like this:
      +++Sheet1!A1+++
      +++Sheet1!B1+++
      +++Sheet1!C1+++
      +++Sheet1!A2+++
      +++Sheet1!B2+++
      +++Sheet1!C2+++
      +++Sheet1!A3+++
      +++Sheet1!B3+++
      +++Sheet1!C3+++

      I would personally avoid writing it like +++Sheet1!A1:C3+++ just because it's rather unclear for whoever views your request next, but the above is how it would be processed.

      Reply

  25. Hello, I created custom request in API Connector side panel with the following request URL:

    https://eu.kobotoolbox.org/api/v2/assets//data.json?query={"_submission_time": {"$gt": "+++Date!A1+++"}}

    In the tab called "Date" with cell "A1" I entered the next formula:

    =text(today()-1; "yyyy-mm-dd")

    In this case the response contains no data, but when I use hardcoded date in request, i.e.

    https://eu.kobotoolbox.org/api/v2/assets//data.json?query={"_submission_time": {"$gt": "2024-04-10"}}

    it returns data as expected. Could you help me, please?

    Thank you in advance

    Reply

    • That all looks correct to me. What happens if you switch your formula to =text(today()-2; "yyyy-mm-dd")?
      I'm wondering if it could be related to the date setting in the sheet, such that today()-1 isn't available yet while today()-2 is.
      If that doesn't work, please feel free to contact support so I can take a look at your sheet.

      Reply

    • Sorry, it currently doesn't work in the pagination fields. The fields in which it works are listed here and include integration input fields, request URLs, headers, request bodies, and JMESPath expressions. May I ask why you'd like to reference cells in the pagination fields? We can consider adding support for that if there's a use case.

      Reply

Leave a Comment

Jump To