Google Sheet Based DB API

Ship #1 Changes requested

@monkeyBanana2048 on Google Sheet Based DB API · 21 days ago

SheetsDB: Turn google sheets into a functioning backend for your applications!

This project uses apps script in order to manipulate and store data on a google sheet that can be edited visually, or through POST requests. In order to use like a database, the user can send POST requests with the type of request (GET, POST, PUT, DELETE) in the body (bypass url limits) to the apps script deployment url.

The most challenging part of this project was designing the metadata tables and figuring out the best ways to organize data in the google sheet so it could be accessed more easily by the apps script.

The best part about this project was seeing it being able to handle many of the functions that industry databases have, such as renaming tables, queries with parameters such as (sort, where, etc), and handling schema.

Detailed instructions on how to use this project can be found in the readme file on the repository. A demo is also available on the website.

3 devlogs
6h

Try project → See source code →

Open comments for this post

@monkeyBanana2048 on Google Sheet Based DB API · 21 days ago

34m 57s logged

Decided to create an implementation of RLS. It’s kind of sloppy, but it should work for it’s purpose. It works by having hidden columns on every table that dictate what keys have access to the rows, and what permissions those keys have. Service key is able to set RLS status (enabled/disabled) and individual keys are generated per user. I think the project is ready to be shipped, because honestly SheetDB is pretty slow because it uses apps script and google sheets. So if you want to scale up and have multiple users, its better to use Supabase or something of that sort.

Open comments for this post

@monkeyBanana2048 on Google Sheet Based DB API · 25 days ago

43m 51s logged

Made auth better

I didn’t think putting the password as a argument in the URL itself was a good implementation, so I took the liberty to beef things up a bit more.
Now:

Separate master key
Separate read-only key
Keys submitted in the body of post requests

Additionally, I found a bug with removing rows. It didn’t cascade down leading to holes in indexing so I fixed the indexing part of the script.

TODO:
Tbh, this is almost complete imo, but if I did have to do some more work, then:

Implement RLS
Implement indexing/cache (optional ultra pro max)

Open comments for this post

@monkeyBanana2048 on Google Sheet Based DB API · 25 days ago

4h 33m 8s logged

Finished all the required stuff to have a sorta working database, still need better auth. I was trying to abstain from AI, but sometimes CORS errors really get on your nerves. People can start using this today, but the authentication is pretty much non-existent. Future updates will probably focus around getting the authentication to be better, and maybe implementing indexing emulation and RLS. Website is deployed here. You can find more details on gh bc I already spent a lot of time writing docs lol.