Create API Request Based on a Cell

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.

Method 1: 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

You can also reference cells in your Header fields and POST request bodies:
api-connector-cell-references-img3

Method 2: Combine fixed values and cell references

Your request field can also 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

Notes

  • The cell you're referencing can use all the standard formulas and syntax of Google Sheets as usual. In the first example above, cell B2 within Google Sheets contains the following formula:
     
  • For additional efficiency, you can both reference cells and stack multiple requests together (paid feature). This is useful for APIs that limit the number of records they'll return in each response.
  • The ImportAPI() Custom Sheets Function is an alternate method of running API requests based on a cell.

Comments:12

  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?

      1. Awesome Ana, thanks for making the POST feature possible 🙂 looking forward to link cells in the request body too!!

    1. You can encode your API URL, which is useful for APIs that require parameters like /subscriptions?offset=[“1558137600000″,”14780876”]. However the POST request input only accepts un-encoded JSON. I’m open to adding this but haven’t received any requests for this feature before — is it possible to provide a little more info on your use case? Thank you!

  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.

    1. Thank you for your kind words, I appreciate that. Triggering API calls on cell changes is an interesting idea, I just emailed you to ask for more detail on what you’re looking for.

Leave a Reply

Your email address will not be published.