The Apipheny add-on for Google Sheets comes with a built-in feature that lets you reference any cell value in your API request.
Referencing cell values in Apipheny allows you to pull data from a cell(s) on your spreadsheet and use that data in your API request.
Reference a single cell
To reference single cell values in your Apipheny API request, all you have to do is wrap the sheet name (e.g., Sheet1, Sheet2, Sheet3) and cell value (e.g., A1, B2, C3) in 3 curly brackets, separate by a colon.
Syntax: {{{Sheet1!CellValue}}}
Example: {{{Sheet1!A1}}}
You can reference a single cell value in your API URL, Headers, Body, or in the Response Viewer.
Reference multiple cells
To reference a range of cell values in your Apipheny API request, follow the same steps as referencing a single, but add an additional colon and cell value, like so:
Syntax: {{{Sheet1!CellValue:CellValue}}}
Example: {{{Sheet1!A1:A10}}}
You can reference a range of cell values in your API URL, Headers, Body, or in the Response Viewer.
Referencing multiple ranges of cells in one request
It is possible to reference multiple ranges of cells in the URL, header and body. The restriction is that the ranges should all be one dimensional and of same size. This will resolve into multiple calls with one element of each range used for each call. Below is an example of multiple ranges being used in the URL, headers and body:
URL:
https://a.b.com?parameter1={{{input!A1:A2}}}¶meter2={{{input!B1:B2}}}Headers:
Key: a
Value: {{{input!C1:C2}}}Body:
{
param1:{{{input!D1:D2}}},
param2:{{{input!E1:E2}}}
}In the example above, there is 5 different ranges of cells being referenced. Two in the URL, one in the Header Value, and two in the Body. Since all the referenced ranges are of the same size (2 cells being referenced), then the above configuration will result in 2 different API calls with these sets of values used:
Set 1
input!A1,input!B1 in url
input!C1 in header
input!D1 and input!E1 in bodySet 2
input!A2,input!B2 in url
input!C2 in header
input!D2 and input!E2 in bodyIn the above example, if the body or header did not reference a range, while the url references a range, then the same body and header will be used for each of the calls.
Troubleshooting
- Only 1D ranges are allowed (eg. A1:A10), 2D ranges are not allowed (eg. A1:B5)
- You can reference multiple ranges in your URL, but they must match in size. Example:
https://example.com/api?parameter1={{{sheet1!A1:A10}}}¶meter2={{{sheet1!B1:B10}}} - You will get an error in Apipheny if any of the values referenced causes a Sheets error (#N/A, #REF, etc)
- Currently the “reference a single cell value” feature works in API URL, headers, and bodies
- The “reference a range of cells” feature currently only works in the API URL. Referencing a range of cells will be available in headers and bodies soon. Please contact us if you need this feature, so we can gauge demand and prioritize this feature accordingly.
- If the range of cells you are referencing contains an empty cell, the request will stop and not continue at the empty cell reference. Please make sure your range does not contain empty cell values, if you do not intend to send blank values.
Google API Usage Limits
Please note that Google has limits when using APIs and other services such as formulas and scripts:
- 20,000 API calls per day
- 6 mins processing time per hour
- 90 mins processing time per day
- 50 mb per call
Click here for info on Google service quotas.
A google user’s quota can be limited based on the combined amount of activity from things such as scripts, addons, and formulas (eg. GOOGLEFINANCE).
Running a request with a lot of cells referenced can cause you to reach your quota.
Step-by-step instructions
Step 1.) Install and open the Apipheny add-on for Google Sheets
Apipheny is a free API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to API data sources, easily.
1.) Install Apipheny by opening the following link on desktop and then clicking the Install button in the Google Marketplace: https://apipheny.io/install
2.) After you’ve installed Apipheny, open a Google Sheet and then click on the Extensions dropdown in the menu at the top.
In the dropdown list you should see Apipheny. Click Apipheny then click Import API to open the Apipheny sidebar in your Google Sheet.
Tip: you can open a new Google Sheet by entering this URL in your browser: sheet.new
Step 2. Choose the cell or column of cells you want to reference
Before you can reference the cell, you need to know where it is. Locate the cell and record the sheet name and cell value(s).
In the example above, we want to reference the cell that contains “ditto”. The sheet name for that cell is “Pokemon”, and its cell value is “A1”.
Step 3. Use Apipheny’s cell reference format and type everything into Apipheny.
The official format for referencing cell values in Apipheny is: {{{SheetName!CellValue}}}
Just take the cell’s sheet name and cell value, separate them with an exclamation point (!), and wrap everything in 3 curly braces {{{x:y}}}.
Then insert that into your API URL, header, or POST Body.
So let’s say our API URL is:
https://pokeapi.co/api/v2/pokemon/ditto
And let’s say we want to insert the last parameter, “ditto”, into our spreadsheet and reference this cell instead of including it directly in our URL.
To reference the cell containing “ditto”, here’s what we’ll use: {{{Pokemon!A1}}}
If we append that to the base URL of the API we’re using, here’s what we’ll get:
https://pokeapi.co/api/v2/pokemon/{{{Pokemon!A1}}}
which is the same as running a request with this URL:
https://pokeapi.co/api/v2/pokemon/ditto
You can even reference a cell containing an API’s base URL to make things even simpler. If we do that using the example above, here’s what we’ll get:
https://{{{Pokemon!A5}}}{{{Pokemon!A1}}}
which is the same URL as before.
Step 4. Run your request.
After you’re done setting everything up, press Run to send your request to the server.
And that’s it!
Congratulations, you now know how to reference cell values in your Apipheny API requests.
