Whether you’re a technical marketer or full-time developer – if you’re looking to pull data from APIs, Blockspring has the shortest time to aha of any product you’ll use this year.
It allows you to extract data from any API they’ve integrated into a spreadsheet instantly.
Here at CIFL, we use it to supplement existing apps, build entirely new apps in spreadsheets, and run marketing and operations processes.
With a Blockspring account plus knowledge of a few key spreadsheet functions, you’ll have the superpowers to build similar templates in literally 60 seconds.
Once you’re using it, you’ll see why we call some members of the league ‘API artists’ – Blockspring allows you to freely paint with data from APIs in a spreadsheet, with no interruption to write and test code.
- Install Blockspring from their homepage here.
- Once you’ve set up an account, open a new Google Sheet and select Add-Ons -> Blockspring -> Open Blockspring.
- The Blockspring sidebar will open – browse APIs, play around with adding Blocks to your worksheet, and follow the instructions in the sidebar to connect new APIs.
Quick sidebar: If you’d like to dive deep (maybe too deep) into Blockspring and it’s close cousin Supermetrics, head on over to the new CIFL course, The Lazy Toolbelt.
The blockspring function
The function you run to call data from an API will be composed of a combination of 3 elements:
- The ‘Block’ you’d like to run, which designates both which API will be called, and how the data will be returned.
- The parameters you’d like to pass – a Block will have at least one required parameter (“url” in the example above), and may have many optional parameters.
- The value for that parameter (“http://www.yahoo.com” in the example above).
This Block would return all links on the Yahoo homepage, right into your spreadsheet:
Each Block will have its own set of required and optional parameters, which are highlighted in the sidebar before you insert the Block into your sheet. When in doubt, follow the instructions in the sidebar.
Blockspring helper functions
The Blockspring add-on comes out of the box with a handful of functions (denoted by starting with a lowercase ‘b’), that are helpful when manipulating results returned by Blockspring functions.
Many Blockspring functions will return data in JSON format into one cell (which requires selecting ‘View Cell’ in the sidebar to view the expanded response). The bGetKey function allows you to pull just one value from the JSON response.
For example, this function uses the FullContact API to find data on a person based on their email, then pulls just the ‘likelihood’ field:
Returns a set result number from an array. For example, if you want to pull the 1st result from a Blockspring function that searches for Wikipedia articles, you can wrap your Blockspring function in bGetItem like so:
Use these in combination with the transpose function to pull exactly the cell you need from a function (and not return a bunch of extraneous data). This allows you to build much cleaner spreadsheet templates (and entire spreadsheet apps), without exposing the user to unnecessary data.
For example, the following function will return only the result Descriptions from a Bing search API query:
A Few Good Blocks
These are the blocks we find ourselves using most frequently at the league:
For examples of these within Google Sheets templates, just grab an invite to our private Trello board below.