Using Google Sheets as a simple database with Papa Parse

Inspiration

On a recent meeting, a fellow dev commented on a piece of software he saw as being "just like Windows 95". This brought up some nostalgia inside me and I quickly remembered there are several libraries available for recreating that classic look and feel.

One of the first results was React95 and the first link inside the showcase was a recipe app from one of the library's contributors. The readme stated it used Google Sheets as a database, which I found interesting and decided to investigate further.

Tabletop

The promising library user in the aforementioned example was Tabletop. I was excited while reading their npm page, but the Github readme stated how the package is getting deprecated by Google's changes. It's a shame since that package seemed to offer many utility features for listing data from spreadsheets out of the box (including reading data from multiple sheets). Luckily, they provided an alternative (albeit much more general-purpose, but still usable for simple examples).
The alternative in question is...

Papa Parse

Upon first look, Papa Parse seems to be a general-purpose CSV parser with a no-nonsense quick-start documentation that is readable at a glance. The parser will return JSON data which can be used for any purpose required, e.g.:

creating tables
visualization via charts
iterating through data to create blocks (by using a templating engine)

Creating a data source

The premise is simple: create a new Google Spreadsheet, enter the headers (column names) and fill the table with data (rows).

Next, we need to publish the worksheet: go to File -> Publish to the Web.

Make sure to click the Start publishing button. We need to parse CSV files, so we need to pass the link to CSV option to our code. If we only have a single sheet, this should suffice. However, if there are multiple sheets, we need to get links to each one by selecting them from the dropdown (and making sure CSV is selected).

Example

To demonstrate one way of using Google Sheets as a data source, we will use 2 sheets and a Codepen repo. The data is simple: I listed some movies and shows I've watched recently. Data will be shown inside two tables with minimal styling. To simplify HTML generation, Vue.js will be used.

The Sheet is available here and consists of two sheets: Movies and Shows. They have almost the same structure, with the only difference being Shows' Last watched season replacing Movies' Year column.

I followed the instructions above to get separate URLs for each sheet's CSV file and saved them to variables.

const moviesUrl =
  "https://docs.google.com/spreadsheets/d/e/2PACX-1vTtKbv_S8Fdo3HLhm64Tc94WZ6FuqtzqePIjuejNFJxKkUvAE8JF8V2KgKoz1n5jQUDfL8A3F-QoDWk/pub?gid=0&single=true&output=csv";

const showsUrl =
  "https://docs.google.com/spreadsheets/d/e/2PACX-1vTtKbv_S8Fdo3HLhm64Tc94WZ6FuqtzqePIjuejNFJxKkUvAE8JF8V2KgKoz1n5jQUDfL8A3F-QoDWk/pub?gid=1364847678&single=true&output=csv";

Next, a Vue instance is created and its data is initialized to empty arrays for movies and shows.

const app = new Vue({
  el: "#app",
  data: function () {
    return {
      movies: [],
      shows: []
    };
  },
...

Upon creation, Vue is told to use Papa Parse to get each CSV file and set it to its state. As a side note in this example, I used an arrow function as a callback for movies and a regular anonymous function for shows. Arrow function retained the lexical scope of this, while the regular function required this to be saved (this should refer to the Vue instance in order to correctly set data)

created: function () {
    this.fetchMovies();
    this.fetchShows();
  },
  methods: {
    fetchMovies() {
      Papa.parse(moviesUrl, {
        download: true,
        header: true,
        complete: (results) => this.movies = results.data
      });
    },
    fetchShows() {
      const _this = this;
      Papa.parse(showsUrl, {
        download: true,
        header: true,
        complete: function(results) {_this.shows = results.data;}
      });
    }
  }

The presentation part is very simple - a single <div> container for Vue instance and two tables with a template which iterates over the fetched data.

<div id="app">
  <table v-if="movies.length">
    <thead>
      <tr>
        <th>Title</th>
        <th>Year</th>
        <th>URL</th>
      </tr>
    </thead>
    <tbody>
      <tr v-for="movie in movies">
        <td>{{movie.Title}}</td>
        <td>{{movie.Year}}</td>
        <td><a :href="movie.Url" target="_blank">{{movie.Url}}</a></td>
      </tr>
    </tbody>
  </table>

  <table v-if="shows.length">
    <thead>
      <tr>
        <th>Title</th>
        <th>Last watched season</th>
        <th>URL</th>
      </tr>
    </thead>
    <tbody>
      <tr v-for="show in shows">
        <td>{{show.Title}}</td>
        <td>{{show["Last watched season"]}}</td>
        <td><a :href="show.Url" target="_blank">{{show.Url}}</a></td>
      </tr>
    </tbody>
  </table>
</div>

Notice how the shows' Last watched season column retains spaces just like we defined inside Google Sheets.

Conclusion

This article is purposefully simplistic and is only used as an intro to what might be possible by using Google Sheets as a data source. Its collaborative nature makes it easy to make changes which make the frontend update (similar to a very basic CMS) while retaining the ability to limit read/write access as usual.

However, there are limitations - speed, possible rate limiting and the dependency on Google to keep the CSV option available in the future. As such, this will never replace a proper database and backend combination, but can still be useful for quick prototyping and fun mini-projects, especially when the data is already available.

Blog