The Apipheny add-on for Google Sheets comes with a built-in, custom APIPHENY function which you can use to run a specific API request in any cell on your spreadsheet.
=APIPHENY(randomly-generated-string)
Upon pasting the complete formula (which is composed of the function and the randomly-generated string) into a specific cell, Apipheny runs the API request associated with that specific string, and loads the API data into the cell that the function was pasted in.
In other words, if you paste the complete formula into cell E4 (like in the image below), Apipheny runs the associated request for that formula and loads the API data right into that same cell.
This function comes in handy when you need your API data in a specific area on your spreadsheet. This lets you continue working on your main workspace with the data right beside it.
You can paste the complete formula anywhere on the spreadsheet and as many times as you like.
You can also run multiple requests in a single spreadsheet. Just be careful not to overwrite other data.
Using the APIPHENY function is easy.
Just save your API request, copy the randomly-generated formula, and paste it into the cell of your choice.
When you save an API request, Apipheny generates a formula containing the APIPHENY function and a randomly-generated string.
If you’re new to Apipheny, just follow the steps below and you’ll be ready to use the custom function in no time.
To begin, go to your spreadsheet then click Add-ons > Apipheny – API Tool > Import API. If you don’t already have the add-on, install it here.
Before anything else, make sure that your endpoint is working. To do this, enter your chosen endpoint into the API URL Path field then click Run to see if the endpoint is working as intended.
If the endpoint is working as intended, press Save to store the request into Apipheny. Once you save your request, you can reuse it for future use.
Pick an appropriate name for your Saved Request, one that’s easy to remember, then click Save.
Apipheny generates formulas for Saved Requests so you can reuse them whenever you like. To find the formula for your saved request, navigate to the Manage tab.
In the Manage tab, you’ll see a list of all your Saved Requests. Find and click the name of the request you wish to use.
Upon clicking the name of your saved request, you’ll be taken to a menu that allows you to edit the details of your request.
Scroll down to the very bottom to see your formula.
Copy and paste the formula into any cell. You can do this as many times as you like and for as many different requests as you like.
The requested data appears starting from the cell on which you pasted the formula.
And that’s it!
Congratulations, you now know how to use the APIPHENY custom function.
The =APIPHENY() formula allows some modifications. You can see the syntax when you start typing =APIPHENY
in your spreadsheet:
Syntax:
=APIPHENY("req-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")
Examples:
=APIPHENY("req-ID#","",A10)
This will cause the formula to auto-update whenever cell A10 is changed. req-ID#
is a unique code from your saved API request settings.
=APIPHENY("req-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
","URL_OVERRIDE_HERE")
In this formula, you can insert an API URL in place of URL_OVERRIDE_HERE
. You can keep req-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
as is. Now, whenever you use the formula, it will call the URL_OVERRIDE.
=INDEX(ARRAY_CONSTRAIN(TRANSPOSE(APIPHENY("req-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";"https://api.vatcheckapi.com/v2/check?vat_number="&A1));100;2);MATCH("registration_info.is_registered";ARRAY_CONSTRAIN(TRANSPOSE(APIPHENY("req-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"; "https://api.vatcheckapi.com/v2/check?vat_number="&A1));100;1);0);2)
This is an example of a formula from this tutorial. This formula is designed to fetch the data from vatcheckapi.com
and filter down to only the value of the field with the key registration_info.is_registered
.
In this example, we inserted the function into cell B1 and pulled it down, in order to fill column B with the value, like so:
You can design a similar formula that fetches and filters data, too. Then use that formula directly in your sheet like you would use any other formula.
If you get an error saying your trial has ended or the formula does not seem to be working properly, try these troubleshooting steps:
Crypto API Tutorials: