Hiram Software Blog

Importing and Exporting JSON from Google Sheets

Our experience using SheetsDB with clients for three months. In short, it's worked better than we could have hoped.

Sheets DB Example

Three months ago Hiram Software released SheetsDB to help our non-technical heroes manage human-sized datasets for a software project. Our first real project was was a small team that wanted to put their inventory online into a catalog, but they wanted to do something different than the existing shopping cart. We prototyped a mobile in-store experience that provided deeper context for their products on customers' phones. We can't tell you which experience because the team wanted to take credit (Hiram Software often works like this, quietly building prototypes and infrastructure).

The two biggest issues were designing the experience (the UX) and setting up a way for the store owners to manage the data. Why did we want to avoid managing the data? The project would go faster and achieve better quality if the store owners apply their expertise than if they asked us to "translate" their thoughts into code.

How to do this.... at first we joked about building a Content Management System, but obviously doing so would take up most of the budget and require training (and a whole list of other downsides). We then watched the store owners, and we saw they keep most of their inventory in Google Sheets already. What if we could just use that data source directly in the mobile app?

Our first thought was finding an API for customer's mobile phones to access the spreadsheet directly, but the best state of art we could find were hacks related to creating published URLs. Buried in these hacks are landmines related to throttling and dealing with an RSS-style format that doesn't play well with modern single page app frameworks like Angular.

Another issue is we combined data from another data source, and we wanted to provide a way for the store owners to clean up the merging process. They know the data best, and they are best positioned to get it right. We also wanted to create snapshots so the store owners could work on their spreadsheet without risking a customer's experience. The reverse also wouldn't fly: the store owners don't know JSON and trying to teach them would have been a waste of time.

We eventually settled on importing and exporting the Google Sheets spreadsheet to and from JSON, and thus SheetsDB was born. We use SheetsDB to import the external datasource into Google Sheets, and the store owners have some formulas to merge the external data with theirs, which results in a cleaned up "view" to share with customers. We export that combined view into JSON, and the mobile application consumes it natively. The store owners thus can keep private notes and private data from being included in the app, and they are comfortable using formulas to encode their custom business logic.

It has worked better than we hoped. During the engagement SheetsDB saved us two or three days of importing data, and it enabled us to go through four iterations of data imports (nobody gets this stuff right the first time, including our store owners who weren't quite sure exactly which products to include). But SheetsDB has paid off well after the engagement as well. In the last two months we are happy to report that the store owners have updated their datasets 7 of the last 9 weeks without calling us for support once. Their customers have thus been able to use and enjoy up-to-date information in the mobile app, and our customer has a product they can use without unnecessarily depending on us. Eventually they'll want to update the app, but it will be on their terms when they are ready, and we think that kind of success is longer-lived.

Advertising Pitch: If you have a case where a non-technical hero will do a better job managing underlying data, and that non-technical hero is comfortable using spreadsheets, may we recommend SheetsDB to bring the data into your technical projects? SheetsDB worked great to export JSON from Google Sheets, and SheetsDB enabled us to import data from external sources. It cut our dev time and enabled us to iterate a few times where we otherwise could not.