Ob2SS.com

Writing Apps Script is a great way to speed up business processes in Google Workspaces. Writing scripts that interface with Spreadsheets can be pretty tricky, though. You need to index your columns, write all sorts of getters and setters, and juggle serializing and deserializing your objects. Why isn't there a standard library for doing this?

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.

If you're new to Ob2ss, get started with the quickstart guide here. If not, here's that library ID for you:

10r01m6-bM7-Ksz1ccwIceIdxmKyjd7LvqUtw8C6FupcLjgoBecG_Q2dv

Features include:

  1. Well documented - no one can be expected to memorize how every interface works. Ob2ss has documentation here, on the project website, and directly in the Apps Script IDE via JSDocs!
  2. Robust - does exactly what it says in the documentation.
  3. Speedy - the library implements caching to make your calls speedy and efficient.
  4. Simple - you can get started without writing a single line of initialization! Or you can open existing spreadsheets to read and write data you already have.

You can view the source code and build details on this project's GitHub page.


Background & Purpose

Ob2ss was created when I got sick of writing the same serialization and deserialization code for the hundredth time. I wondered if it was possible to build an abstract library that could do it for me, to and from any spreadsheet, and with a few convenience features. So I wrote Ob2ss a few years ago, got half-way done and moved on. I came back to it recently and got it to where I wanted it.

For Business Scripts On Existing Data

Mosts scripts I and my colleagues write are on existing spreadsheets that need some kind of automation. Maybe it's a list of employees who need to be emailed about something or a leads list to process into another sheet. If you're in a hurry you'll probably write something like

const data = SpreadsheetApp.getSheetByName('my_data').getRange('A1:F500').getValues();
data.map((row) => return {
  let entry = {};
  entry.name = row[0];
  entry.id = row[1];
  entry.role = row[2];
  // ...
  return entry;
});

That is insanely brittle because any changes to that spreadsheet threaten your script. What happens if there's a new field? What happens if the fields are rearranged? What happens when new rows are added?

You can get around this with some common patterns like column indexing, but they don't solve the problem completely:

const nameColumn = 0;
const idColumn = 1;
const roleColumn = 2;

const data = SpreadsheetApp.getSheetByName('my_data').getDataRange().getValues();
data.map((row) => return {
  let entry = {};
  entry.name = row[nameColumn];
  entry.id = row[idColumn];
  entry.role = row[roleColumn];
  // ...
  return entry;
});

It shouldn't be this difficult to get my code to understand that each row is a record and I want those records in Apps Script. So I wrote apps script to make these projects more robust and durable in the face of minor structural changes to my spreadsheets.

For small projects

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:

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.

Permissions

This script requires a lot of scary-sounding permissions, but they're indeed all necessary. This script needs to be able to access your files, read their data, and potentially edit them for you. If you don't trust my Library deployment of it (no hard feelings), you can get the code directly from Github, deploy it yourself, and add it directly to your project to get all the same functionality.

You might also get some permissions errors while using Ob2ss. This is because Apps Script can't tell which code paths it will encounter while running the library. While your use might be perfectly safe (e.g. "I just want to read some data from a bound spreadsheet"), it might still error out ("Cannot access DriveApp while running a custom function"). This has to do with how Apps Script manages permissions; you just can't do certain things in certain contexts.

P.S. There's also a typescript implementation if you want to include the source in your project.

Performance

1 Item 1000 Items A 100KB Item
Creating 554ms 476ms 554ms
Reading 197ms 230ms 75ms
Removing 309ms 222ms 301ms

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

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

Caveats & Warnings

Obviously, this isn't supposed to be the backbone of a real application. You should be using real databases if you're creating a real business application. Some of the reasons why are summarized below:

  1. Ob2SS only handles primitive types.
  2. Do not rely on state; your sheet may change out from under your code.
  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. Blank fields do not get returned when read, unless they're an entry in an array.

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 begin an operation to the time it returns from Ob2ss, 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.