A guide on how to build a personal research assistant in a spreadsheet
July 31, 2015

A guide on how to build a personal research assistant in a spreadsheet

Research assistant in 4 steps

A while back we partnered up with Blockspring to enable anyone to use our API without needing to write any code. They’ve created an awesome solution that allows you to make use of a range of great APIs using only a spreadsheet. This enables you to bring data into your spreadsheet, run text-analysis and much more.

So today we want to show how you can make a spreadsheet based research assistant in just a few minutes! You provide it text and it provides related content. This could be used if you’re drafting an essay, article, or report, or just want to stay on top relevant news and research based on what you’re working on. It's a super quick way to see what is out there across different knowledge domains, and because it goes beyond keywords, it can find a broad range of related content.

To get started you will need:

  1. A Blockspring account - sign up here
  2. A Lateral API Key - once signed into Blockspring instructions are here
  3. A Google spreadsheet (You can now also use Excel, but this demo uses Google)
  4. A block of text to use as a source, be it a company description, article/essay draft, article URL, or abstract.

The Lateral functionality that Blockspring currently supports is our recommendation API for content such as News and arXiv academic articles (the full list is here). We'll use these to make our personal research assistant. For a bonus feature we'll also use our new article extractor. This pulls in the text, keywords and author info from article URLs into your spreadsheet.
And now let's jump into the 4 steps!

Step 1: Set-up the spreadsheet

Open a Google spreadsheet and sign into Blockspring, which is found under Add-ons. If you haven’t added it already you can install it from this page.

Step 2: Add news recommendations

Open the Blockspring console and search for the Lateral News recommender. Click insert into new sheet. This inserts it into a new sheet and adds nice formatting. Delete the placeholder text, and paste in your text to get your first batch of news recommendations. If you only want news recommendations, you're now all set and can skip down to the wrap up, but if you want some additional features move on to Step 3!

Step 3: Add arXiv article recommendations

If you're not looking for math, physics or computer science papers this isn’t strictly necessary, but will still be a fun way to experience obtaining results from different disciplines. Alternatively you can also use our Wikipedia recommender. To add arXiv, select a cell under your news results, search for arXiv lateral in the Blockspring console, and then click the "insert into selected cell" button. You will then have to edit the formula slightly to grab the text from the text cell above the news results, if you followed the steps above this should be A6.

Step 4: Paste in new text to get recommendations

Now you can reuse this whenever you want, paste new text into the text cell and results appear before your eyes. You can open this spreadsheet at your convenience and see if anything new has come in or use it for new content that you want more information on. Here are three examples we used, the links take you to the spreadsheet:

Optional step: Use article URLs

If copy and pasting text is not for you, you can replace the text field with a URL parser. This involves a bit of setup but means you simply have to paste an article's URL and its text will be retrieved automatically.

First create 3 rows below the text, I named mine URL, URL parse and text. Paste a URL into the cell next to the URL row, then select the cell below and search the Blockspring console for "Extract Content from Articles". Click "insert into selected cell".

The article extractor will then return the extracted parts of the article in a format called JSON. Blockspring have a great way to deal with JSON, simply select the cell containing the JSON, and click view cell in the upper right hand corner of the Blockspring console. You then click the empty cell below the JSON and in the Blockspring console under "cell window" scroll down to the part labeled "body" to automatically insert it into the cell.

The final step is to update your recommendation functions so that they grab the text from the correct cell. Simply click the cell containing the function and replace A6 with B7 (or the cell you placed the text in). Once this is complete you can simply paste a URL in the designated cell and away it goes, fetches the text and gets the recommendations!

Here is an example of a URL parser research assistant (I hid the text cell in row 7 for viewability).

Wrap up

The great thing about Blockspring is that you can run additional functions on top of any incoming text, such as sentiment analysis, entity extraction and summarisation. I will write a post exploring these in the near future.

I’ve made the sheet I used available for copy and pasting here, but remember you will have to make a copy, install the Blockspring add on and have a Lateral API key for it to work.

We will be looking to add more content databases to get recommendations from in the future, as well as the ability to retrieve recommendations from content you have uploaded to the Lateral API. If you have any comments or suggestions please get in touch. Also let us know if there are any data sources that would be particularly interesting to use. This is just the beginning and I’m sure there are many improvements and new ideas we can incorporate!

P.S if you’re a content owner/creator and would like this functionality for your content please get in touch!

Useful Links:

More in

By clicking “Agree”, you agree to the storing of cookies on your device to enhance site navigation, analyse site usage, and assist in our marketing efforts. View our Privacy Policy for more information.