Original nucleus/templates/page.html

Ob2SS (Object To SpreadSheet) is an Apps Script library that lets you use a Google Spreadsheet as a pseudo-database for your small projects. There's no setup required, works on most object types, and lets you create hybrid spreadsheet applications more easily in the Google ecosystem.

Status The project is my on-again off-again interest, but here's what we've got:

  • Reading rows works great and is where I want it.
  • Writing rows might be rough around the edges, but it handles numbers, strings, and dates like a champ.
  • We don't have on edit callbacks yet, which I really want.

You can view the source code and reference material on this project's GitHub page.

Overview

As of this writing, the library gives you a simple API to read a spreadsheet as though it were a database of objects. It's an extension of my blog post about passing objects from Spreadsheets into Apps Script. You can enjoy all the benefits of using spreadsheets to manage your data like easy edits or adding charts/graphs. Just add the Ob2SS library to your project and you can start working with your objects right away.

Ob2SS pairs well with underscoreJS for finding/filtering the results of toArray().

Setup

To get started using Ob2SS you need to have a basic understanding of Google Sheets, Google Apps Script, and probably a little JavaScript.

To integrate Ob2SS you must import it as a library into your project.

Open the libraries dialogue.

Here you can add a library by it's ID. Ob2SS uses the ID 1J1l7TVHEcPlVrDhvSAUSzbXyngZeivrtedfcVw1YLAlyoWvv5pqGlkFV. Click "Add" and select the latest version.

Add the Ob2SS library.

You're done! Now you can start using the library.

A Note On Permissions

This script requires a lot of scary-sounding permissions, but they're indeed all necessary. This script needs to be able to access the file you specify, read its data, and potentially edit it for you. If you don't trust my Library package of it (no hard feelings), you can get the code directly from Github, review it yourself, and add it directly to your project in one big file to get all the same effects.

Usage

Here's a code snippet showing you how you'd use the library to read objects from a spreadsheet you already have. You'll need to know your sheet key in order to use it as a database.

// Let's tell Ob2SS which spreadsheet to store this object to.
Ob2SS.initialize({
  sheetKey: '1mZRh6XJBdPPOs5b5duVeV9G8SgaoulTZtYtCd6WOXcM' // USE YOUR SHEET KEY HERE.
});

// Now we need the table within the spreadsheet databse.  It gets created automatically if it doesn't exist.
var table = Ob2SS.getTable('clients');

// Finally, we get that table as objects.
var clients = table.toArray();

From here the sky's the limit! Take a look at the Cookbook section to see some ideas for using this script.

Tip: You don't actually have to initialize if you don't want. If you have a bound script, Ob2SS will default to the parent spreadsheet.. If you create a standalone script, Ob2SS will default to creating a new spreadsheet for you in Drive (or, if one exists, it'll open that one).

Background & Recommended Use

There are many solutions for large-scale storage and retrieval of objects (MySQL, PostgreSQL, etc). These solutions are usually characterized by technically difficult implementations, complex APIs, and difficult analysis options in order to enable you to do some very big things. These are used by large corporations around the world all the time and excel at managing millions of rows of data.

But what if you want to do small things? For small-scale projects, it can be overkill to implement these kinds of systems. Most teams opt for manual data management for small sets of data specifically because it doesn't come with all that effort. Plus there're lots of benefits that come for free by using manual spreadsheets:

  • Robust viewing and dashboarding.
  • Really easy data editing.
  • Mellow learning curve.
  • Schema changes are easy, fast, and forgiving.
  • Charting.

Awesome right? The trouble is that spreadsheets- generally- don't make it easy to do programmatic data access. So far, I haven't found a similarly simple way to store/retrieve data from spreadsheets when they’re under manual control.

So I made Ob2SS so that I can write small applets on top of GSuite without losing the flexibility. And the name isn't lost on me. It is an annoyingly slow and thick way of doing data management, but at the scales we're talking about, it's more appropriate than spinning up a whole MySQL instance.

This performs best on smaller, low-IO datasets that are accessed both programatically and by hand. For example:

  • Managing the list of students that attend your summer camp each year (~500/year).
  • Coordinating the repair and loan status of construction equipment your business rents to clients (~100 machines).
  • Tracking the editing, finishing, printing, and shipping status of your magazine's issues each week (~52/year).

This performs poorly on large, high-traffic, or mission-critical applications.

  • Anything to do with financial transactions (ACID is important).
  • Anything that will ever go over 100,000 rows (large databases).
  • Anything with thousands of users accessing it constantly (high write QPS or IO).

Performance

If this section is anything but a curiosity for you, Ob2SS may not be right for you. It's not about performance, but I'm including my test results here in case you're wondering.

Action Average latency
Table create ~600 ms
Pending: INSERT tiny object ~330 ms
Pending:INSERT 1KB object ~330 ms
Pending:INSERT 500KB object ~460 ms
READ tiny object ~210 ms
READ 1KB object ~220 ms
READ 500KB object ~305 ms
Pending:DELETE tiny object ~135 ms
Pending:DELETE 1KB object ~140 ms
Pending:DELETE 500KB object ~155 ms

Limitations & Caveats

This library is basically a motorized skateboard, and as such should be used with caution. There are lots of caveats to using Ob2SS and some are intentional.

Summary

  1. Ob2SS only handles basic types (int, bool, string), arrays, and strings only.
  2. Do not rely on state; your data may change out from under you.
  3. Do not scale up with this solution.

Spreadsheets is worse with types than Javascript is. While I try my best to return objects as faithfully as they're written, your typing is going to be pretty dang loose through Ob2SS. The trouble is that we're not only dealing with Javascript's weirdness, we're also dealing with how Spreadsheets manages your data. Dates aren't dates, strings aren't strings, and everything relies on the "format" of a field to determine what it is in the AppsScript context. null, undefined, and "" are identical: non-existant. If you write any of these to an object's property and try to write it to an Ob2SS table, it will not get written.

Don't assume state at all. Remember that you're dealing with spreadsheets that might've been edited by humans. Google sheets has ACID transactions but from the time you read a field to the time you go to write it, things might've changed. Someone else might have even deleted that whole column! You might be expecting a number, but you might get back "Rachel didn't check in this morning" instead.

Be mindful of the spreadsheet's limits. This is for small applications only. While it scales well for 500KB objects, try to avoid writing too much data to any one field of your objects. e.g. If you can, spread out data globs into several fields rather than one monolithic field.