How to Use the =APIPHENY() Custom Function

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.

What does the APIPHENY function do?

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

Requested data appears in the cell you paste the formula in. — Paste the Apipheny custom function somewhere convenient to keep your workspace nice and tidy.

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.

How to use the APIPHENY function

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.

Step 1. Open Google Sheets and run Apipheny.

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.

Step 2. Make sure your endpoint is working.

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.

Step 3. Save your request.

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.

Pick an appropriate name for your saved request then click Save

Step 4. Go to the Manage tab to find your randomly-generated formula.

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.

Go to the Manage tab then click your saved request.

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.

Scroll down to the very bottom to find your formula.

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.

Step 5. Copy the formula and paste it wherever you like.

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.

Click any cell and paste in your formula.

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.

Modifications to the formula

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.