HN Explorer

Show HN: Using Google Sheets as the back end/APIs of your app (zerosheets.com)

356 points by joaovcoliveira ↗ HN
Hello everyone!

At a company I worked for, we needed to develop an MVP (basically a web page) and apply certain business logic to a Google Drive spreadsheet that was frequently updated by the Sales team.

In this case, we had two options:

Develop a backend to replace the current spreadsheet and have the sales team use it as a new "backoffice" - This would take a very long time, and if the hypothesis we were testing was wrong, it would be time wasted.

Create the web page and use Google's SDK to extract data from the spreadsheet.

We chose to go with the second option because it was quicker. Indeed, it was much faster than creating a new backoffice. But not as quick as we imagined. Integrating with Google's SDK requires some effort, especially to handle the OAuth logic, configure it in the console, and understand the documentation (which is quite shallow, by the way).

Anyway! We did the project and I realized that maybe other devs might have encountered similar issues. Therefore, I developed a tool that transforms Google spreadsheets into "realtime APIs" with PATCH, GET, POST, and DELETE methods.

Since it's a product for devs, I think it would be cool to hear your opinions. It's still quite primitive, but the basic features already work.

https://zerosheets.com

170 comments

[ 7.5 ms ] story [ 228 ms ] thread
[deleted]
(comment deleted)
ctrlaltdylan
Is the Google Sheets API rate limit open enough for actual production use?

I thought it was pretty restrictive, no more than 60 writes per minute, but I'm not sure about the reads restrictions.

r00fus
Couldn't you cache the reads? Not many usages really require real-time from their data store.
RockRobotRock
Do you really want to deal with caching logic for what should be a simple API call? Sounds like a convincing argument to use whatever this product is.
internetter
client = (APIcall) => redis.get(sha1(APIcall)) || { res = api(APIcall); redis.set(sha1(APIcall),res) return res }

Not that hard. Like 10 lines of code to get a decentish cache going.

hot_gril
Assuming you have Redis
internetter
Redis uses like 5mb of baseline RAM and can be deployed in a few lines of docker-compose.
[deleted]
(comment deleted)
hot_gril
I'm not allowed to do that where I work. License is a no-no, can't run jobs without red tape, and there's no Docker either.
internetter
Ok? I'm surprised your work lets you build a whole product ontop of google sheets, then. Also, why did you delete your original comment on not having a server?
hot_gril
I deleted it cause I realized this thing has a server (probably). Was mixing it up with other people's projects that didn't have one.

They're internal tools, but big ones. And I'm surprised too. You won't hit too much resistance doing things the well-supported ways, but for some reason there's no well-supported way to run a cache.

kellpossible2
It's max a couple of hours work to cache in some local database like sqlite or in memory.
joaovcoliveira
For now I`m setting no restrictions. Since it is an MVP, I`m trying to understand what a basic and a hard user would be. After a while, Ill figure out how to charge for it and what limitations should a free and a paid user have.

My Google API rate limit is way bigger then 60/minute.

stephenbez
I used Google Sheets as a data source that business people could update, but eventually we moved away from it as we found it unreliable. We would get an occasional error (maybe a 429) even though we were polling the sheet once a minute (we had a few other sheets that polled once every few minutes).

This manifested as an issue when doing a deploy but being unable to get critical data. We added retries and stuff like that but it seemed not great to run a business of something that isn’t designed for this purpose.

coderintherye
Perhaps the dreaded 503 Internal Error ?

I'm convinced most of the people in this thread haven't tried working much with Google Sheets API at scale. Most of the time it's fine, then it will have days where 30-40% of the calls (as measured by Google Cloud console API monitoring) will throw an internal error which Google advises the option for is to "try again later". Also API calls that take up to 4 minutes (?!) to return (again as measured by their own API monitoring tools in Cloud console).

It's too bad because I otherwise really like this approach.

elondaits
Yes. I used Google sheets as a database to build a website and ran into this issue. The worse part is, if you come across the limit there’s not much you can do but wait or rate limit.

Another problem I had is an API change one year in.

I would not use Google Sheets again. Maybe I’d try Airtable, Notion, or some other similar platform where the API access is more of a priority to the company.

yawnxyz
For reading sheets, it's better to use the "share as CSV" option since that gets cached pretty well w/o limits
gofreddygo
I've resisted this temptation to integrate with google apis for these 2 specific reasons rate limits and api changes.
maliker
Great concept. Congrats on launching.

Do you have a sense for how much usage Google will allow on a given sheet or user? I.e. will Google shut down the API after a certain usage level?

(Side-note, your SSL cert seems to be having trouble, i.e. www.zerosheets.com is correctly encrypted, zerosheets.com is not.)

joaovcoliveira
Hello!! Google limits for me are:

300 Read requests per minute 300 Write request per minute

Since Ive just launched it, that number is far far away from being reach. (If it happens, the number can be increased).

Regarding the SSL cert, some other users pointed the problem but I can`t replicated. Tried with many different browsers. Im not sure about whats happening, it`s always working for me but I will keep digging.

Thank you for your comment bro :)

hankjacobs
I get an error on Chrome as well.

From what I see, the Common Name for the cert at https://zerosheets.com is `*.ip-66-228-52-143.cloudezapp.io` rather than zerorsheets.com. It's issued by Let's Encrypt. www.zerosheets.com is issued by Amazon. Hope that helps.

btown
Have you thought about auto-installing https://developers.google.com/apps-script/guides/triggers into the connected sheet, and maintaining a cache of the data that you invalidate onEdit? Would likely improve read latency dramatically, though it would make reads eventually-consistent.

To others seeing this - has anyone else ever tried this approach? Are there players out there who do this and have SOC II etc. data security measures?

jpgvm
Disclaimer: I work at Glide but not on Google Sheets integration.

Triggers/AppScript - not that I am aware of right now...

Everyone I know doing stuff with GSheets (including Glide) is using a full-sync approach with an optimistic write layer of some sort.

GSheets API frequently reports being unable to do things, sometimes because it's calculating (common for huge sheets), sometimes just because it's down, other-times because rate limits

My impression is it's not a great API to build on directly.

A trigger based system that captured writes and wrote them to an outbox sheet could drastically speed up the sync process however.

The reason why we don't use that approach is there isn't APIs sufficient to setup this sort of thing automatically (last time I checked at least) and it makes changes to the users sheets which isn't impossible to live with but is distasteful.

davidraedev
Same issue here. At least on firefox initially the bare domain shows the error, but if you visit www and then try to load the bare domain it will then redirect to www on subsequent loads. I was able to replicate in a private window.
office_drone
I'd like to plug PocketBase [0] for a similar use case.

Last week I was looking for a place to store random data with API access, and was looking at making a Google Sheets backend, but PocketBase was easy and didn't have a 60 rpm quota [1].

Deploying to a cheap VPS was very easy with CapRover.

[0] https://pocketbase.io/

[1] https://developers.google.com/sheets/api/limits

hgyjnbdet
I was looking at pocketbase but I didn't really understand how to use it. I know sqlite and am good at SQL, but couldn't work out how to use it.
phantompeace
You need to query/write using HTTPS requests
tonyhart7
one vouch for pocketbase, I use pocketbase as general collecting data and export it to csv via api and transfer it to google sheet for viewing,edit etc

super easy for prototyping and getting the job done, while google sheet as a backend is nice too but I need authentication etc etc

withinboredom
Reminds me of the days when we'd write plugins for Microsoft Office that would populate data from an API and even send edits.
guhcampos
There are loads of good use cases for this. At one point in my career I had to somehow re-tag thousands of resources in multiple clouds, some were terraformed, many were manually created over the years.

The solution? Export them all to a Google Sheet with one tag per column. Edit and apply-on-save. Filter, sort, summarize, pivot all the much you want. It's incredible.

nativeit
I was exploring some alternatives for templating and CMSs a few days ago, and stumbled over NPR’s internal toolkit[1] for publishing articles with data, charts, visualizations, etc., and thought it was interesting that they included Google-Sheets-as-a-CMS.

1. https://github.com/nprapps/dailygraphics-next

altdataseller
I rather use Google Sheets as the front end of my app. Have it call endpoints in my backend and populate the sheet with the data, and auto-generate charts based on that data. Easy, no-nonsense dashboard. Add in some dropdown menus and inputs and you got yourself a good enuff frontend
_adamb
Me too! I've normalized it in our organizations. We use a combination of app scripts and what we call "Single Button Apps" that are web pages that have a text box (paste the URL of the Gsheet) and a "go" button. The input and output are all in the sheet, but putting the button in a web app makes it easy to iterate on feature set as/if the application grows. https://blog.adambuilds.com/sba-not-spa-the-most-minimal-ui
ipince
I read your comment and your blog post and I still don't understand what happens when I put a link to a sheet in the text box and click Go.. what happens? You say your inputs and outputs are all in the sheet.. ok.. what does the button do? Are you just calling some API and giving it the sheet as the input, and then writing the output of the call back to the sheet? So then these Single Button Apps are just API endpoints that you're calling with sheets instead of json or, say Postman (if you want a nicer UI)?

I think I'm failing to see the point here :-/

afro88
Same here. If the idea was to make it so non-technical people can use the google sheets + API app, just put a script in the sheet that calls the API? It will appear in a menu inside google sheets itself
johtso
You can even put a button on a sheet to trigger a script
dorgo
I used to put buttons in the menu ( or even side panels ), but nowdays I draw a big button and attach a apps script function to it. Looks more bad ass.
surfingdino
Great stuff. I need to tell some of my friends about it. Thanks for making it!
vhiremath4
Funny story. Before pivoting my startup to Loom, we were a user testing company named Opentest. Instead of spinning up a DB and creating a dashboard for my co-founders to look at who requested certain user tests, I just dumped everything into a Google Sheet. It was so good. No downtime. Open access. Only 3 people looking/editing, so no conflict. Didn't have to deal with database upgrades or maintenance. I often think about this decision and feel like I've learned a bunch of "good engineering practices" that pale in comparison to how being truly scrappy can be a genius unlock at any level.
ydant
Agreed. Google Sheets is a great scrappy option for a startup / small company.

I've used it for a lot of system data that needs to be modified by a few people at most. With a little bit of careful code and caching (I like validating and syncing to S3) you can easily use it as a crud frontend for important system data.

It also can make great adhoc dashboards - plug into APIs (even private ones if you add in custom Google Scripts code) or push data updates to the sheets. I've had rather large reports auto updated on a schedule with multiple views of the data (pivot tables, queries, lookups, etc). Fully focused customized views into the data needed to solve specific internal problems with really quick delivery times.

Sure, a custom developed option should be substantially better than Google Sheets, but you won't develop it quicker. By the time you actually need something bigger/better, you should be at a place where your needs are better defined and you can afford the resources to develop something better.

xcrunner529
Yeah my client uses Google forms for some simple use cases and it goes straight to google sheets and I just pull from that for verifying submissions and such in code. Not bad at all.
niutech
Why anybody would like to store their sensitive business data on a public cloud such as Google Sheets?
joaovcoliveira
its a honor to hear from a loom founder, Ive been using your product for a long time and I love it! 100% onboard on what you said.
vhiremath4
Thank you for recording with us! And good luck with this product. There's definitely a bunch of use cases for scrappy prototyping or early mocks!
[deleted]
(comment deleted)
hot_gril
Google Sheets is the backbone of several projects I work on, even though I'm a SWE at a large company. It's used as a fronend in some places or a backend in others. But part of this is because we have too much red tape around using simple eng tools like React or Postgres.
2OEH8eoCRo0
Sounds neat, let me ask permission first before I put company data into Google Sheets.
xtracto
If your email is Google based , chances are your company already have plenty of its data on Googles services.
NSMutableSet
All five employers in my work history used Google Workspace / G-Suite. I wouldn't say it's uncommon.
datascienced
Google sheets can also be thought of as a managed nosql DB with a built in management UI.
jakjak123
If you can do this one-way data dump, it sounds like a good idea. The issue starts when you have seven different sheets that is supposed to authoritatively control some other processes. The people writing in the spreadsheet can basically do any kind of mutation to the spreadsheet, so it will break extremely easily.
eeue56
I recently made a full web app using only AppsScript and Google Sheets as the database, and wrote about it here [0], and open sourced it here [1]. It was a novel experience, but I felt particularly compelled by the idea of having a data store than non-devs can easily interact with while having a web app in front of it that didn't require a server to be set up. But, AppsScript is too slow for this kinda thing to be a nice experience. Zerosheets looks nice, and I'll investigate it further if I look into this idea again!

- [0] https://thetechenabler.substack.com/i/142898781/making-a-sim...

- [1] https://github.com/eeue56/simple-link-aggregator

enumjorge
> having a data store than non-devs can easily interact with while having a web app in front of it that didn't require a server to be set up

Isn't this one of the use cases for Airtable?

sadrasabouri
Reading the documentation, I was expecting more tbh. As I found out there is no way to customize the API. The response is always a JSON dump of that google Sheet.
giarc
I found the docs pretty minimal as well. Ironic since they specifically called out Google for shallow docs in the description.
uptown
I used to use a Google Sheet to drive a restaurant website's menu page. It was perfect. The restaurant could update the spreadsheet whenever something changed, and it'd instantly be reflected on the web. If they ever messed something up, they could just revert.
chambored
A couple of weeks ago, I set up this exact thing for a restaurant. Simple and free. I had app script build the site on updates. So they could edit it any time, it would trigger a rebuild, and redeploy as a static site.
hipjiveguy
where was the site hosted? Is it available online for us to checkout ?
chambored
I set it up initially on Vercel, but now I use Firebase because I'm sure it'll be cheaper in the long run.
trevor-e
This looks great. I tried doing it myself a while back using existing client libraries for Python and was surprised how annoying the setup is, like the spreadsheet auth in particular.

I'm also surprised Google hasn't tried to kill this use-case. That's my main hesitation for using it on anything semi-serious.

shafyy
Are Fortune, The Weather Channel, Fast Company, and National Geographic really your customers?
_justinfunk
I had this same question. Also, are "developers all over the world" "happily using Zero Sheets"?

Based on OPs description, I wonder if this company was spun up while already doing business with those companies.

Klonoar
There are far more startups/products using that tagline of "used by developers all over the world"/"used by $x company" than our industry seems to want to admit.
giarc
Usually what this means is some small little team within those companies either signed up for a trial (using their @fortune.com email address) or they use it for some small little project. Pretty common marketing technique for startups. Not totally honest, but not totally false.