Skip to main content

Making of Sheets BI

· 6 min read
Siva Dirisala
Creator of SQL Frames

We recently announced a preview of Sheets BI a BI tool that works realtime with your Google Sheets, Excel and CSV files on Google Drive, public datasets and local files.

In this post I am going to talk about some of the technical journey of the process.

Seets BI started off as a web app with a server and database that one has to install and work with. As the project evolved, we wanted to make the product available for free for individual (personal or professional). For this, there is no real need for a server and it only adds complexity to users. That too, with Sheets BI we are targeting non-technical folks and asking them to setup a database on their computer will be too much of an ask. We wanted to provide a true desktop app experience where they download the software and install it and next thing they have a fully functional app up and running, all configurations taken care off behind the scene so they just experience the app. Except for one specific, setup step, which will be described below, we managed to achieve this on-click installation experience.

NodeJS or ...​

Prior to making the decision to offer Sheets BI as a free desktop app, we developed a fully funcitonal web app using NodeJS. Given the front-end is JavaScript (or rather TypeScript) heavy, we wanted to keep the same programming language on the server side and this was actually a good decision in terms of productivity. However, when the server was ready and we created the docker container, the size of the image was large enough that we wanted to revisit this decision. We had familiarity with ElectronJS in the past but that is also a very bulky solution.

Since the entire app runs within the browser, via REST API calls, we just needed a locally running web server packaged along with the app. So, we decided to go with Go language and the benefits after redoing the entire server code were very obvious. The entire app fits within 30MB and when compressed comes less than 12MB. Imagine this with the size of other BI desktop apps which are close to 700MB compressed and 2GB uncompressed! Of course, some of those apps like Tableau, might be bundling a lot of resources like maps and such.

Docker Image Size

The initial docker image was close to 2GB. Then we went with a slimmer base image that was still close to 1 GB. Then we only installed production npm packages which excluded for example Monaco editor (which is a whopping 72MB). This got the image size down but we were still at 300MB.

SSL​

Even though it is via localhost, we wanted the app to be using https protocol rather than http for better security. And also, the app requires using Sign-in with Google for integration with Google Sheets and Google Docs. Asking end users to generate SSL certificates is again not a good installation experience. So we decided to automatically generate the SSL certificate. In fact, we generate a new certificate everytime the app is restarted. But this has an issue that each time the user has to let the browser know that the certificate can be trusted which is annoying. To avoid this, we instead generate a root-certificate on first invocation of the desktop app and save it and then create SSL certificates using this root certificate. As a result, the end user needs to accept the root certificate only once.

Mini-CA

The idea for self-signed root certificate comes from Mini-CA

Database​

Sheets BI uses ORM and is made to be database agnostic. It has been tested with MySQL and SQLite. And we intend to certify it with PostgreSQL. Making it work with SQLite is perfect for a desktop app. No need of a separate Database server. On the first invocation of the app, we create a new SQLite db instance and migrate the schema. On subsequent invocations of the app, we first check if there is a need to sync the schema and if so, we first take a backup of the db, then apply the delta changes on the backup and if that works without errors, we apply the same delta changes to the main instance. This strategy has allowed us to automate the schema migration process for the desktop app.

Sign-in with Google​

Given Sheets BI* has integrations to work with Google Sheets and Google Drive, the setup involves creating Google OAuth Credentials. For the desktop app, we can't ship our credentials and instead expect the end users to use their own. This is the only step that is slighly complex for end users, but this also offers them the best security.

ORM​

When we were doing the NodeJS based web app, we were using Mikro-ORM. It is a great ORM solution for NodeJS. But as part of migrating to Go language, we evaluated many solutions but in the end decided to stick to well established soluton that is GORM.

Desktop UI​

While much of the app is all web based, we do have a small layer of native UI. The things we use native UI are

  1. About
  2. Setup
  3. Root Certificate download
  4. Database Backup
  5. Check for Updates

Even though we could have done all of the above except the Setup screen, within the web UI, doing this via native UI provides enhanced security and ease of use.

We evaluated several solutions for creating cross-platform native UI in Go language. In the end we choose Gio-UI for creating windows. We also choose systray to show the app menus. The desktop app works great on Mac, Linux and Windows.

While these libraries make it easy to create cross-platform UI, we miss the simplicity of using HTML and JavaScript to craft UI components with ease.

Conclusion​

As much as we enjoyed bringin Sheets BI to life with all the necessary R&D work, we hope you would enjoy using it. You can dowload the app from Sheets BI.