Blockspring for Excel

Help Guide


Installing

Step 1. Download the Excel add-in.
Step 2. Open the Excel add-in download and follow the installation instructions.
Step 3. Open Excel.
Step 4. In Excel's top toolbar, click the BLOCKSPRING tab. Sign in and get started.


The =BLOCKSPRING() function

What does this function do?

We install a single new function to your Excel client: =BLOCKSPRING(). This functions lets you call any service that you have access to on Blockspring. It works just like any other Excel function, you can feed it inputs and references, and when those change, it recalculates.

What's unique about =BLOCKSPRING() is that while it's a single function, it allows you to call any web service. You give it inputs, like any other function, these inputs are sent to the cloud to call the web service, which then responds with results you can use in Excel!


What inputs go into =BLOCKSPRING()?

The easiest way to use the blockspring function is to avoid using it directly. Open the Blockspring Console (found in the ribbon) and find the service you need. To insert the function preconfigured into your workbook click either [Insert into Sheet] or [Insert into Cell].

Another way to use =BLOCKSPRING() is by typing the formula manually into a cell. Here is an example that you can copy into a cell to immediately search Wikipedia:

=BLOCKSPRING("wikipedia-search", "query", "harry potter", "limit", 50)


Why does the input order look strange?

Another difference between normal Excel functions and the Blockspring function is the order of the inputs. In the Blockspring function, the first input is always the block name, and all inputs after alternate between input name and input value. That way, you don't need to maintain any specific input order when submitting your function.

Block formula

When you use this function, you'll want to tweak the input value (eg change "Harry Potter" to "Michael Jordan"), but you do not want to change the input name.


#SPECIAL! Results

What is a #SPECIAL! result?

While Excel is great for handling numbers, text, booleans, dates, and tables, it's not so good at handling nested data structures and files. Blockspring still lets you render these results, which we call #SPECIAL! results, in your spreadsheet using the Blockspring Console.


How do I view special results?

When a service creates a special result in a cell, the cell will like this:

#SPECIAL! Select this cell, then click 'View Cell' in the Blockspring Console.

You can view special results by highlighting the cell, then clicking on the “View Cell” tab in the Blockspring console.


How do I use special results in my spreadsheet?

You can insert the data from a special result into a cell. In the "View Cell" tab, the result's data has labels for each section. Select the cell you want to insert that data into, then click "Insert into cell".


Do I need to use the Console to get #SPECIAL! data?

Blockspring comes with the function =bGetKey(result, key) to get a specific value from a special result.

Example 1: If the result { "my_name": "Don", "age": 27 } is in cell A1, then to get the "age" you would use =bGetKey(A1, "age").

Example 2: When viewing a special result using the "View Cell" tab, the keys are highlighted in blue. If your special result is in cell A1 and the View Cell tab shows a key for "age", then you would use =bGetKey(A1, "age") just as above.


Advanced =BLOCKSPRING() Configuration

Often times you want more control over how your =BLOCKSPRING() function runs. Here are some advance inputs you can provide to do just about anything in a spreadsheet:

  • Caching: Type =BLOCKSPRING([function-name], "_cache", 7200) to cache the result of your function for 7200 seconds. That means no matter how many times you run your function, it'll return the same result without contacting the dependent data source. This is great for preventing duplicate requests. You can cache for a maximum of 6 hours.
  • Delay: Type =BLOCKSPRING([function-name], "_sleep", 5) to wait 5 seconds before making your function request. This helps with rate-limits by giving you full control over how many requests you send to Blockspring, and underlying web services, at a time.
  • Run: Type =BLOCKSPRING([function-name], "_run", FALSE) to prevent a function from executing. Let's say you to manually control when an entire worksheet executes, add a run=false to each blockspring function and control from a single reference. You can do this with an =IF() statement, but this is a nice helper.
  • Flatten: Type =BLOCKSPRING([function-name], "_flatten", true) to completely flatten the internal hierarchy of the table you receive. Often times, you receive tables with strange JSON responses (lots of squigly brackets in a cell). This means there's more data packaged in that cell. Add the flatten input and this data will unpackage automatically into columns.