[HN Gopher] Show HN: Using Google Sheets as the back end/APIs of...
___________________________________________________________________
Show HN: Using Google Sheets as the back end/APIs of your app
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
Author : joaovcoliveira
Score : 115 points
Date : 2024-04-12 16:29 UTC (6 hours ago)
(HTM) web link (www.zerosheets.com)
(TXT) w3m dump (www.zerosheets.com)
| trollied wrote:
| There's a help page that gives you all the code you need to do
| this. https://developers.google.com/sheets/api/quickstart/js
| joaovcoliveira wrote:
| Yep, if you want to implement it from scratch, thats an option
| (way more painfull then using zerosheets) :)
| ctrlaltdylan wrote:
| 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 wrote:
| Couldn't you cache the reads? Not many usages really require
| real-time from their data store.
| RockRobotRock wrote:
| 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 wrote:
| 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 wrote:
| Assuming you have Redis
| internetter wrote:
| Redis uses like 5mb of baseline RAM and can be deployed
| in a few lines of docker-compose.
| hot_gril wrote:
| 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 wrote:
| 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 wrote:
| 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.
| joaovcoliveira wrote:
| 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 wrote:
| 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.
| maliker wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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?
| office_drone wrote:
| 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 wrote:
| 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 wrote:
| You need to query/write using HTTPS requests
| joaovcoliveira wrote:
| I loved your product!!
|
| My infrastructure is 100% focused in scale so I think we can
| work together, we just need to share the costs.
|
| Please send me a message here
| https://www.zerosheets.com/contact so I can get your contact.
| tonyhart7 wrote:
| 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
| toisanji wrote:
| tried it, sign in and ssl doesn't work.
| billythebuum wrote:
| same
| SJC_Hacker wrote:
| Do you have any bad data entry prevention?
| joaovcoliveira wrote:
| Yes
| withinboredom wrote:
| Reminds me of the days when we'd write plugins for Microsoft
| Office that would populate data from an API and even send edits.
| barrrrald wrote:
| reminds me of fuckitdb https://pypi.org/project/fuckitdb/
| guhcampos wrote:
| 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.
| ErikAugust wrote:
| Maybe we can replace Bitcoin with Google Sheets?
| progval wrote:
| already done: https://news.ycombinator.com/item?id=35119317
| nativeit wrote:
| 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 wrote:
| 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
| surfingdino wrote:
| Great stuff. I need to tell some of my friends about it. Thanks
| for making it!
| ulrischa wrote:
| Pricing is unclear to me
| pcthrowaway wrote:
| Pricing isn't mentioned anywhere
| vhiremath4 wrote:
| 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 wrote:
| 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.
| joaovcoliveira wrote:
| 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 wrote:
| 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!
| hot_gril wrote:
| 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.
| eeue56 wrote:
| 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
| mooreds wrote:
| Wrote something similar a decade ago:
| https://www.mooreds.com/wordpress/archives/1359
|
| Hope things have improved over that time.
| sadrasabouri wrote:
| 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 wrote:
| I found the docs pretty minimal as well. Ironic since they
| specifically called out Google for shallow docs in the
| description.
| uptown wrote:
| 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.
| yegle wrote:
| Related: https://www.levels.fyi/blog/scaling-to-millions-with-
| google-...
| trevor-e wrote:
| 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.
| HatchedLake721 wrote:
| Didn't https://www.glideapps.com famously start with Google
| Sheets backend?
| Zaheer wrote:
| Levels.fyi as well: https://www.levels.fyi/blog/scaling-to-
| millions-with-google-...
| shafyy wrote:
| Are Fortune, The Weather Channel, Fast Company, and National
| Geographic really your customers?
| _justinfunk wrote:
| 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 wrote:
| 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 wrote:
| 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.
| Zaheer wrote:
| https://github.com/benborgers/opensheet#readme Is the best and
| simplest tool for turning Google Sheets accessible quickly.
| franze wrote:
| For some of my apps I just load the full spreadsheet as Json into
| the app. Cache it. Check for updates after every new start.
|
| Fast efficient easy.
| sfink wrote:
| My next user script project idea would require something like
| this. It's for my own use, but I have to fill out grade sheets
| using an incredibly painful web UI. The data would be far more
| easily entered into a spreadsheet. (That's exactly what they used
| to use, but in order to make things "easier" the school
| implemented a horrific parody of an ultra basic CRUD web app...)
| So I want to make a user script that reads from a spreadsheet to
| populate the painful-to-use web form.
|
| I haven't started yet because (1) I still haven't finished
| writing up my last user script experience blog post, and (2) I'm
| terrified of the auth nightmare. It might be easier or harder in
| the user script context--I am in the context of a web page, so
| maybe that means I can do a normal oauth flow from there or
| something?
| amf12 wrote:
| Have you checked out Apps Script?
| rjbwork wrote:
| Also recommend checking out Azure Logic Apps' Google Sheets
| integration. It's been a few years since I've used it but I built
| some pretty nifty quick and dirty tools on top of Sheets, Logic
| Apps, and Azure Functions for back office app work at my last
| company.
| jumploops wrote:
| Neat! This seems very similar to Sheety[0], which I've used a
| bunch of times before (and found a few bugs...).
|
| Do you have any plans to open source?
|
| [0]https://sheety.co
| hot_gril wrote:
| The link is broken. Says "no https for this domain"
| londons_explore wrote:
| Google apps script already lets you write http handlers...
|
| They seem pretty powerful.
| pcthrowaway wrote:
| Do you know of any docs on this?
| thisismytest wrote:
| I love this. I recently had an idea for an app that would allow
| people to share their favorite things with other people, and i
| left that exploration thinking how amazing it'd be to build lots
| of apps on Google Sheets as the backend - you own the data, it
| has sharing built in..etc.
|
| so not just google sheets as the backend, google sheets as your
| personal backend for you instance of the app.
___________________________________________________________________
(page generated 2024-04-12 23:00 UTC)