Blockspring for Google Sheets

Help Guide


Installing

Step 1. Download the Google Sheets add-in.
Step 2. Open a Google Sheet.
Step 3. In Google Sheet's top toolbar, click the Add-ons tab. Open the Blockspring add-on.


The =BLOCKSPRING() function

What does this function do?

We install a single new function to Google Sheets: =BLOCKSPRING(). This functions lets you call any service that you have access to on Blockspring. It works just like any other Sheets 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 Google Sheets!


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 add-ons menu at top) 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 Google Sheets 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 Google Sheets 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. In Google Sheets, the main limitation is you cannot sleep for more than 30 seconds.
  • 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.


Known Issues

  • RAND(), TODAY(), and NOW() cannot be used with =BLOCKSPRING(). There's a bug in Google Sheets where if you do so, it'll corrupt your sheet and start running Blockspring functions at random, even when your sheet isn't open.

    If you'd like Google to support this, "star" this feature with Google.

  • Selecting any item in Google's Add-ons menu will recalculate all functions. There's a bug in Google Sheets where if you select any item in the Add-ons menu, it'll refresh all Blockspring functions. That means repeated API requests even though you did not mean it.

    If you'd like Google to fix this, "star" this issue with Google.

  • Reopening a Google Sheet will recalculate all functions. Google Sheets is designed to recalculate all functions every time you reopen the spreadsheet. So make sure you understand this is happening before making potentially expensive API requests.
  • Google Sheets has quotas on requests. If you begin running into Google Sheets quotas for "UrlFetch Requests", that means you've run too many =BLOCKSPRING() functions in a single day. You can at most run 20,000 requests per day on a free Google Sheets account. To increase this limit, Google wants you to sign up for Google for Work.