As growth hacker for Tessin.se and previous as CTO for Offerta.se, I often face tasks in the form of “take this Excel file with user emails and retrieve column X, Y & Z from the database” (like name or total order value).
I’ve been looking for a practical solution where this can be done in an efficient way without having to write custom code each time. After a few iterations, this is what I came up with:
I’m a big fan of the .Net scratchpad LINQPad. This application makes is super simple to query a database using C# and linq. And since I prefer C# over VBA this is exactly the tool I want to use to solve the problem.
A first attempt was to write a script in LINQpad that opens a XLS file and reads the input value (email) from one column and then outputs the results in other columns. This is not very flexible though.
The holy grail for me was to have a way to create ad-hoc formulas inExcel that take an input such as an email and return a specified value from the database.
To do this I created a small self hosted JSON REST service using the Nancy Web Framework in a LINQPad script:
When running this script a small web service is started locally on your computer:
Next step is to connect this REST API with Excel. To do this I’m using a tool that I wrote called SeoTools for Excel. This is an Excel add-on (not free but with a 14 day trial) that has a bunch of features useful for growth hacking. For this solution, I’m using the JsonPathOnUrl function to fetch the JSON response from the REST service we wrote and with the JSONPath query language get a piece of the structure:
=JsonPathOnUrl("http://localhost:8090/user-by-email/"&A2;"firstName")
Now we can easily get all the values we need. SeoTools takes care of caching so that the same request isn’t executed more than once. The Nancy REST service can easily be modified for whatever information you need to retrieve from the database. This takes a bit of setup, but once you have it up and running it works like a charm. If you don’t want to use SeoTools it’s not that difficult to find a VBA solution.
I also used this technique if I have a SOAP service (there’s a SOAP driver for LINQPad 4) that I want to query using Excel. I’m not a VBA fan at all so if I have anything that needs scripting (string manipulations etc.) then it’s a lot easier for me to write this code in C# in LINQPad.
This is definitely on my top-10 list of productivity hacks that saves me time every day! Please let me know what you think or how you would have solved this.
LINQPad boilerplate code
You also need to add the Nuget package Nancy.Hosting.Self to your LINQPad script and include it’s namespaces.