Using Grist to Manage Publications + App Data

Industry. Publications, Personalize City Guides

Creator. Stephen S., Owner

Finding a simple self-hosted, relational data tool

Stephen and his team manage a lot of documentary data such as statistics, geolocated records, reference images, and maps. As the founder of OurQuietPlaces, a website and app in-development that will help busy urbanites find moments of respite and cultural enrichment, he’s tasked with designing and maintaining the company’s data infrastructure and organizing research and publication. For the app’s core data, he’d found a system he liked using technical tools, database, and applications, self-hosted on several Linux servers, that met his tech-forward needs. But the rest of the team needed an easy-to-use, flexible and collaborative data tool to handle various types of data such as inventories, worksheets, publishing plans, and more. It wouldn’t be cost or time effective for Stephen to build GUIs on the hosted database to meet those needs. Members of the team needed to be empowered to create and edit their own documents.

The spreadsheet made sense, but quickly ran into problems. It was unstructured, there was no way to relate data in different tables, and complicated sheets became a nightmare to modify and share. Then they tried Airtable for its relational-spreadsheet hybrid approach, which worked well, but was expensive. Worse yet, when Stephen tried to move data off of Airtable, the experience was painful. Data could only be exported as CSV tables, they lost their relational structure, and some data types did not convert well, leading to data loss. He spent days piecing it all back together.

The more I use Grist, the more I LOVE it. I have been surveying self-hosted database GUI tools, and I was consistently disappointed with their shortcomings or bugginess. Grist is the first to have tilted the balance the other way.

Stephen S.

Burned by a bad experience, Stephen looked for a spreadsheet-database product that would not sequester company data, was easy to use, robust, and low cost. If it could be self-hosted, all the better. He tried dozens of startup products such as Retool and nocoDB, but rejected all those tools either because they were very recently developed and often buggy, limited in features, or increased his database overhead. His exhaustive research led him to Grist, which met and exceeded his expectations.

Specifically, here’s what I love about Grist:

  • It is open source and self-hostable using Docker.
  • It is opinionated, providing a totally new way to manage data that is quite disruptive, but convincing, even for database specialists.
  • Thanks to Grist’s use of SQLite, data is portable and not sequestered.
  • It is modest. Grist keeps it simple and does what it does superlatively.
  • It requires only a soft learning curve with lots of potential to dig deeper with outstanding documentation.

Stephen S.

Today, OurQuietPlaces uses grist-core, the open source version of Grist, to manage its data inventory, hardware inventory, technical infrastructure (hosted apps, storage), publication schedule and writers’ assignments. Grist has been a key tool for this remote team of four who needed a real collaboration tool that made sharing easy.

Grist, a Hacker Friendly Spreadsheet

VisiCalc screenshot from history-computer.com.

Spreadsheets attract people with a hacker mindset, since they reward a little technical curiosity with a big jump in capability. Despite that, while there are some open source spreadsheet projects, most of the action is closed source (Excel, Airtable, Honeycode, etc). Grist is a solid, modern, powerful spreadsheet that fits well into the open-source ecology, and is as friendly to hacker-developers as it is to hacker-users. Here are some reasons why.

Grist borrows visually from classic spreadsheets and modern dashboards, with every view backed by relational data, filtered and linked with other views as needed. Grist takes care of the data, and keeping it live, so custom views are easy to add if you need something new, using standard HTML/JS/CSS. For example, live-updating invoicesmailing labelsgoat pedigreesexoplanet birthday card, or morse code quizzer that look just how you want them.

Grist is a self-contained format, based on SQLite. Grist tables are literally SQLite tables, which opens up all sorts of possibilities. For example, I can take a Grist example investment spreadsheet and publish it using Simon Willison’s datasette as a Glitch remix without any conversion steps needed. This is a step up from exporting CSV.

Grist uses python as its formula language. Python has a good standard library, nice syntax for working with lists, and is a favorite of data scientists. Grist itself is written in TypeScript, with formula evaluation isolated in a sandbox

The Grist format makes it easy to find the most recent common ancestor of two versions of a document, facilitating three-way comparisons and git-style revision control. When working on a Grist spreadsheet with someone, you can use real-time collaborative editing – or you may sometimes prefer to “fork” your spreadsheet, make a set of changes, compare them with the original and then land them back. We’re excited to flesh this workflow out, and I think any programmer can see where we’re going with it.

Grist code is available at github.com/gristlabs/grist-core under a standard free and open source licence (Apache 2.0). Grist software is developed by Grist Labs, which offers free and paid hosting plans. Whether you choose us to host your spreadsheets, or to run Grist yourself, you have the comfort of knowing the developers have revenue (so they won’t one day vanish in the morning sun like an elusive gleam of dew on a flower), and you have the code and your data (ditto).

“Fiddle” sites are very handy for communicating and learning about a technology. I know I learned a lot about javascript by playing with this 30-line implemention of a spreadsheet: https://jsfiddle.net/ondras/hYfN3/. Grist supports fiddles almost by accident, because you can use Grist without logging in, and you can quickly edit a “fork” of a spreadsheet without changing your original. Here, try fiddling with one of our examples. You won’t break it.

Grist supports attachments, and those attachments can be downloaded as part of a single Grist file format. Grist has solid undo/redo support. Hosted Grist makes automatic backups at hourly, daily, monthly, and yearly intervals. Grist has an API and a Zapier integration. Grist supports real time collaboration. Grist supports many Excel functions. We publish a Grist docker image (see the README), and have an unreleased standalone app for Windows/Mac/Linux that we’ll be bringing up to date.

Plimpton 322 tablet image from wikipedia.org.

Spreadsheets have evolved a lot since Babylonian times. In open-source, Gnumeric and Calc are venerable standalone Excel replacements. LuckySheet is a shot at a clone of that standalone experience in the browser. Ethercalc is a real-time collaborative spreadsheet, developed by Audrey Tang, now Taiwan’s Digital Minister, extending work they did with Dan Bricklin on SocialCalc. Evolving further, hybrid spreadsheet-database models are growing in usefulness and popularity. That’s what Grist is, and we are pleased to bring it now to the open-source community.

Spreadsheets have a long tail of features that are “must haves” for someone out there. We think a quality, open code base is a big help for realizing those features, and having peace of mind in relying on them. Just as Babylonian clay tablets were, we hope that Grist will prove a sturdy data storage and presentation infrastructure that can survive for years (if not centuries!) to come.

Come play with hosted Grist, or explore and build Grist source code. In either case, we’d be grateful for your feedback at @getgrist or support@getgrist.com!

Paul Fitzpatrick

Paul Fitzpatrick, CTO

Dmitry Sagalovskiy

Dmitry Sagalovskiy, CEO