Detasheet: Google Sheets as a Database

Have you ever considered using Google Sheets as a database? You haven't? Well you just did!

The Why

Google Sheets is free, provides a built-in method to visualize data (with charts and graphs), and allows you to export data in a variety of formats.

Obviously, Google Sheets was never intended to be a database. The API is verbose and would be difficult to reuse across new projects.

As a result, I created Detasheet! Detasheet is a wrapper around the official Google Sheets API, allowing you to intuitively create and manage databases.

The documentation is available on Github (give it a star!) or NPM.

It Works!

Lessons Learned

OAuth is an absolute nightmare to deal with. It also doesn't make much sense to request a refresh token when initializing and interacting with the database should be relatively permanent. I opted to create a service account instead, using a private key and client email to authenticate my Google Sheets client.

After creating the service client, I realized I could read spreadsheets but not write to them. I found out that you could actually share your documents to the service account, which enabled realtime editing.

Internally, I tried to use some bleeding-edge object-oriented JavaScript (Node v14+), like real private methods.

class Client {
  async #authorize(credentials) {
    return new Promise((resolve) => { resolve(credentials) })
  }
}

const client = new Client()
client.#authorize() // won't work!

I've also had experience publishing modules to NPM before, so it wasn't that difficult (literally just npm publish --access public).

Conclusion

Detasheets was definitely an interesting and fun experiment. If you actually build something with it, let me know!