How I built a spreadsheet app with Python to make data science easier

Alextrolese
Oct 4 · 3 min read

Today I’m open sourcing “Grid studio”, a web-based spreadsheet application with full integration of the Python programming language.

About a year ago I started tinkering with the idea of building the data science IDE that I had always wanted. Having worked extensively with Microsoft Excel, R (Studio) and Python, I envisioned how some integrated version of those would make my life easier.

Why?

The main issue that I set out to solve with Grid studio is the scattered workflow that I was experiencing while going back and forth between multiple tools such as R studio and Excel while working on data science projects.

While exporting that CSV file for the gazillionth-time, running into freezing up of application windows when my row count was too high or trying to do something straightforward such as reading in a JSON file I had had enough. The existing tools did not provide me with the environment and associated workflow that enabled me to be productive.

That’s why I decided to build something that would integrate my workflow into a single, modern and easy to use application fit for my data science needs.

How does it work?

Grid studio is a web-based application that looks remarkably similar to an ordinary spreadsheet program such as Google Sheets or Microsoft Excel. However, its killer feature is the deep integration of the Python language.

Viewing data in a tabular structure and manipulating it directly feels naturally to almost everybody who has used a computer.

Combining this simple UI with the power of a full fledged programming language such as Python really makes it stand out.

Scripting with Python is as straightforward as possible: just write a few lines and execute it directly.

Core integration: Reading and Writing to sheets

At the core of the Python integration is the read and write interface to your spreadsheet. A high performance connection between your sheets’ data and data in your Python process.

Simply write to the sheet like so:

sheet("A1:A3", [1, 2, 3])

And read from the sheet like this:

my_matrix = sheet("A1:A3")

With this simple yet powerful function you can read and write directly from and to sheets in order to automate data entry, extraction, visualization and more.

Writing custom spreadsheet functions

While reading and writing gives you a lot of flexibility through a simple interface, sometimes it makes a lot of sense to write custom functions that can be called directly in your spreadsheet.

Common spreadsheet functions such as AVERAGE, SUM, IF, etc. are already available by default. But what if you need more?

Simply write the function you need!

def UPPERCASE(a):
return str(a).uppercase()

Now call this function in your spreadsheet, just like you would a regular function.

Alextrolese

Written by

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade