Show HN: Using Google Sheets as the back end/APIs of your app(zerosheets.com)
zerosheets.com
Show HN: Using Google Sheets as the back end/APIs of your app
https://www.zerosheets.com/
170 comments
Just beware of the modern version of the "Excel noob trap" which lots of investment banks fell into in the 80s and 90s. Spreadsheets are amazing generic calculating frameworks and you can do a ton in them, so a lot of banks ended up building a huge ediface of excel spreadsheets that ran a wide variety of risk, pricing and operational functions.
With a few plugins and extensions you can really do amazing things with this type of setup. The problem is that your spreadsheets basically become an unmaintainable and impenetrable nightmare and all your business logic is held hostage in various people's personal sheets. Making wide changes becomes hard/impossible and doing something that in a traditional software framework would be easy ("Let's change the frobnicator to twizzle first and then twozzle afterwards, instead of the other way round as it does it now") become very difficult requiring many many changes in many many sheets with a lot of risk, and a lot of dilligence to be sure you have actually made a particular critical fix everywhere.
With a few plugins and extensions you can really do amazing things with this type of setup. The problem is that your spreadsheets basically become an unmaintainable and impenetrable nightmare and all your business logic is held hostage in various people's personal sheets. Making wide changes becomes hard/impossible and doing something that in a traditional software framework would be easy ("Let's change the frobnicator to twizzle first and then twozzle afterwards, instead of the other way round as it does it now") become very difficult requiring many many changes in many many sheets with a lot of risk, and a lot of dilligence to be sure you have actually made a particular critical fix everywhere.
This is remarkably similar to the situation you get with visual scripting in game development - it's intended as a way for designers and artists to do small coding tasks on their own without needing to bother a programmer (e.g. set up a pressure plate that opens a door when you step on it), but their creations also tend to spiral out of control. There's a whole website dedicated to examples of this from Unreal Engine[1]
Personally, I think good refactoring support would be the way to go - make it easier for a programmer to go in and "extract method" or similar to clean things up if they have gotten out of hand, without necessarily having to rewrite the whole thing from scratch.
[1] https://blueprintsfromhell.tumblr.com/
Personally, I think good refactoring support would be the way to go - make it easier for a programmer to go in and "extract method" or similar to clean things up if they have gotten out of hand, without necessarily having to rewrite the whole thing from scratch.
[1] https://blueprintsfromhell.tumblr.com/
It's not always by choice. It took me around 4 hours of red tape at work to get into a place where I could run a policy compliant python hello world.
Not a full IDE or god forbid pip modules - just hello world. And that's one of the better experiences thus far. Some finance shops simple don't give you any options.
Its par for the course to give office workers access to only Excel and then people are surprised when they build abominations in excel...
Not a full IDE or god forbid pip modules - just hello world. And that's one of the better experiences thus far. Some finance shops simple don't give you any options.
Its par for the course to give office workers access to only Excel and then people are surprised when they build abominations in excel...
Totally agree. I’m working for a company right now that put off ‘refactoring’ their tangle of spreadsheets and database query export ‘pipelines’ for over a decade. We are hemorrhaging money right now because it does not scale. And you won’t notice that until it all breaks down.
Now you have effectively created bottlenecks for all critical processes because simultaneously working on spreadsheets is about the most dangerous thing you can do. Data integration becomes a perilous and time consuming affair; there’s no easy way to guarantee conformity between spreadsheets.
You paper over these problems with more code than you ever would have had if you switched to the proper tools earlier.
Now you have effectively created bottlenecks for all critical processes because simultaneously working on spreadsheets is about the most dangerous thing you can do. Data integration becomes a perilous and time consuming affair; there’s no easy way to guarantee conformity between spreadsheets.
You paper over these problems with more code than you ever would have had if you switched to the proper tools earlier.
> Making wide changes becomes hard/impossible
To add to this - no revision control.
OK, maybe you can store your spreadsheets in a RCS, but can you get diffs of the changes, so you can confirm the revision you're about to push is what you meant to do? Can you review the history of diffs, so you can see how the system has changed over time? If multiple people make changes, can you do a merge? Do you even have a separate "working copy" where you can try stuff out, or are you just editing the production copy with no safety harness?
To add to this - no revision control.
OK, maybe you can store your spreadsheets in a RCS, but can you get diffs of the changes, so you can confirm the revision you're about to push is what you meant to do? Can you review the history of diffs, so you can see how the system has changed over time? If multiple people make changes, can you do a merge? Do you even have a separate "working copy" where you can try stuff out, or are you just editing the production copy with no safety harness?
> no revision control
I'm not claiming this is a sufficient solution, but Google Sheets does keep a version history and you can see changes. Doesn't solve the problem of working copies and merging though.
I'm not claiming this is a sufficient solution, but Google Sheets does keep a version history and you can see changes. Doesn't solve the problem of working copies and merging though.
Try to compare version histories on hundred thousands row of google sheet. It's not a pleasant experience.
Absolutely. I'm just mentioning it because sometimes I forget that it even exists and it is better than nothing at all.
Only thinking of this now, but a flat OpenDocument shreadsheet (“FODS”, a single XML file, unlike the usual XMLs-in-ZIP flavor) should actually be amenable to text-based version control.
But it would be ok maybe to use sheets just for storage. I wonder what size limitation do they have.
Sadly many companies WANT to be in the excel trap...
Can you elaborate? I understand the reasoning "we need to grow no matter the cost, will deal with problems when they arise", but deliberately sabotaging yourself doesn't sound reasonable.
For a number of reasons (some of them actually fairly substantial), spreadsheets are the only end-user programming tool that caught on and endured, so users will often choose them. It’s not even that they won’t need to find/hire/allocate programmers, it’s that using them doesn’t seem like a big deal at all.
Of course, just because you told yourself you aren’t doing a software project doesn’t mean you’re not prone to the standard problems of managing those, but the incubation period for many of them is long enough to catch a lot of non-practicioners unaware. I don’t think telling people to leave it to the professionals is the answer, for what it’s worth,—I just don’t see how we get from here to a world where one could interpolate between systems and end-user software more gracefully.
Of course, just because you told yourself you aren’t doing a software project doesn’t mean you’re not prone to the standard problems of managing those, but the incubation period for many of them is long enough to catch a lot of non-practicioners unaware. I don’t think telling people to leave it to the professionals is the answer, for what it’s worth,—I just don’t see how we get from here to a world where one could interpolate between systems and end-user software more gracefully.
> It’s not even that they won’t need to find/hire/allocate programmers, it’s that using them doesn’t seem like a big deal at all.
I don't understand why it doesn't seem like a big deal, knowing the limitations of spreadsheets. The spreadsheet experts have to know that it will be spaghetti in a decade, right?
I still think the solution for unifying those is to use a database as a backend, with the spreadsheets merely being a frontend for that backend. It does require the people to come somewhat closer to the software, though. I don't know that software can reasonably work with the ill-defined schema of spreadsheets.
I don't understand why it doesn't seem like a big deal, knowing the limitations of spreadsheets. The spreadsheet experts have to know that it will be spaghetti in a decade, right?
I still think the solution for unifying those is to use a database as a backend, with the spreadsheets merely being a frontend for that backend. It does require the people to come somewhat closer to the software, though. I don't know that software can reasonably work with the ill-defined schema of spreadsheets.
not op but probably because some people are so used to using spreadsheets that even if you built them a database with custom visualizations, they'd ask for spreadsheet export.
Not op but that's one of a milder example. Some management people prefer the look and feel administration to be excel-like, even able to do data modification like excel.
Some even worse that they require the data to be in excel since that's all they know.
Some even worse that they require the data to be in excel since that's all they know.
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.
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.
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.
Why anybody would like to store their sensitive business data on a public cloud such as Google Sheets?
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.
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.
Google sheets can also be thought of as a managed nosql DB with a built in management UI.
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.
[deleted]
Sounds neat, let me ask permission first before I put company data into Google Sheets.
All five employers in my work history used Google Workspace / G-Suite. I wouldn't say it's uncommon.
If your email is Google based , chances are your company already have plenty of its data on Googles services.
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.
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!
You don't actaully need any fancy wrapping.. just open https://script.google.com/ you can already access all google's APIs + you will be able to integrate your sheets with your gmail (send email), calendar (you can modify the calendar when the sheet get changes), create pages, allow inputs from form and etc..)
The problem with that is the sheet will not have any .. transaction based ops like a real database so for example you want to lock certain resources.. you may fail..
Every time someone makes a simple solution for a problem on this website someone else points out that a much more complicated option already exists.
I'd like to nominate this comment for the HN Hall of Fame
It's not a simple solution if it suggests replacing one API with two APIs (sheets + this wrapper). It defeats the point of going with Sheets. Both APIs can break now.
[deleted]
Is it more complicated, really?
It depends on the end user actually. If it is a developer then it might not be that complicated.
[deleted]
I'm surprised no one's posted Spread API yet: https://spreadapi.roombelt.com/
It's a free Google Sheets / Apps Script you just paste to your sheet, and it turns the sheet into a full CRUD. It's kind of rate limited though but completely free!
Edit: I've thought about creating a company around Sheets before, and the problem is that once you get to the "willing to pay" stage, you also kind of outgrow Sheets. I'd rather migrate to Turso, Cloudflare D1, or Pocketbase instead of staying with Sheets or SpreadAPI, because of the limitations.
It's a free Google Sheets / Apps Script you just paste to your sheet, and it turns the sheet into a full CRUD. It's kind of rate limited though but completely free!
Edit: I've thought about creating a company around Sheets before, and the problem is that once you get to the "willing to pay" stage, you also kind of outgrow Sheets. I'd rather migrate to Turso, Cloudflare D1, or Pocketbase instead of staying with Sheets or SpreadAPI, because of the limitations.
So glad that people find my little project useful!
Please keep in mind that you can always post your improvement ideas or PR's here https://github.com/ziolko/spreadapi.
Please keep in mind that you can always post your improvement ideas or PR's here https://github.com/ziolko/spreadapi.
Looks neat! Seems very limited though.. like you can't even insert multiple lines in one request?
Just tried it out. The script is able to handle multiple commands (GET, POST, etc) in one go, but you will have to package the payload as an array.
See https://github.com/ziolko/spreadapi/blob/master/spread-api.j...
As an example:
See https://github.com/ziolko/spreadapi/blob/master/spread-api.j...
As an example:
curl -L \
--data '[{ "method": "POST", "sheet": "Sheet1", "payload": { "username": "John", "age": 25 } }, { "method": "GET", "sheet": "Sheet1" }]' \
"https://script.google.com/macros/s/$APP_ID/exec"
Wow this is really cool, thanks for linking!
what are the Spread API rate limits? Wasn't able to find info about this on the site
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
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
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
super easy for prototyping and getting the job done, while google sheet as a backend is nice too but I need authentication etc etc
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.
You will probably enjoy Datasette more for this use case
You need to query/write using HTTPS requests
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.
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.
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
- [0] https://thetechenabler.substack.com/i/142898781/making-a-sim...
- [1] https://github.com/eeue56/simple-link-aggregator
> 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?
Isn't this one of the use cases for Airtable?
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?
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?
Have you checked out Apps Script?
If you’re using Google sheets then yeah you’ll have to do a normal oauth flow. Alternatively you could use excel and write a simple macro to automate the flow. The easiest thing IMO would be to skip the complicated part of the script (auth) and just copy and paste the values from the clipboard and process the data accordingly.
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
1. https://github.com/nprapps/dailygraphics-next
I did something very similar with https://github.com/kellpossible/avalanche-report/ we started with Google Sheets because it allowed us to quickly iterate with data entry workflow. When paired with a server this also allowed us to generate custom charts/diagrams via the IMAGE function with a crafted URL query. Reads are cached in a local sqlite database.
We're in the process of migrating away from Google Sheets now as it's a little painful to set up and it's impossible to completely prevent users from editing the wrong fields in our use case. But it has served very well so far and would highly recommend this approach to start with for anyone!
We're in the process of migrating away from Google Sheets now as it's a little painful to set up and it's impossible to completely prevent users from editing the wrong fields in our use case. But it has served very well so far and would highly recommend this approach to start with for anyone!
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.
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.
where was the site hosted? Is it available online for us to checkout ?
I set it up initially on Vercel, but now I use Firebase because I'm sure it'll be cheaper in the long run.
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
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
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 :-/
I think I'm failing to see the point here :-/
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
To anyone using Google Sheets as its data layer: be sure never to create a primaryKey column, always use the title column. Also be sure to use one single spreadsheet with many tabs, and coreference cells by their sheet/line/column. That’s such a superb mental model of what your data represents.
Of course denormalize your data without caring. Tabular model is not good at managing cardinalities, but [truth to be told] neither is our brain.
Also use AppScript for formulas and business logic. Your future dev will love reverse engineer it.
Then when things become hairy, hire an IT consultant and pay him well. Be sure to complain that his added value is limited wrt this Gsheet chef d’œuvre.
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.
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.
2 Google Sheets backend stories:
- Needed to manage vocab learning content for 60 world languages, mostly being updated by a handful of university students and a bunch of remote volunteers. Google Sheets was our source of truth, with a nightly sync into MariaDB. We even wrote some callable scripts that could detect ambiguities and other common issues. Saved us a ton of tooling effort, and stakeholders found it cool that we could update content so quickly.
- Working on a new product, my boss found a Figma plugin that lets you wire up prototypes to a Google sheet. It’s report-heavy and we’re still building the real app, so to dogfood it internally I’m running a wizard-of-oz collecting data with a different tool we already built and dumping results into sheets and then sorting/filtering/calculating for all the needed views. Internal stakeholders get a real, clickable prototype with real values that closely approximates what the real product will look like. It’s been amazing!
- Needed to manage vocab learning content for 60 world languages, mostly being updated by a handful of university students and a bunch of remote volunteers. Google Sheets was our source of truth, with a nightly sync into MariaDB. We even wrote some callable scripts that could detect ambiguities and other common issues. Saved us a ton of tooling effort, and stakeholders found it cool that we could update content so quickly.
- Working on a new product, my boss found a Figma plugin that lets you wire up prototypes to a Google sheet. It’s report-heavy and we’re still building the real app, so to dogfood it internally I’m running a wizard-of-oz collecting data with a different tool we already built and dumping results into sheets and then sorting/filtering/calculating for all the needed views. Internal stakeholders get a real, clickable prototype with real values that closely approximates what the real product will look like. It’s been amazing!
what Figma plug-in?
How does this differ from AppSheet?
https://about.appsheet.com/how-to-create-an-app/
https://about.appsheet.com/how-to-create-an-app/
There's a help page that gives you all the code you need to do this. https://developers.google.com/sheets/api/quickstart/js
Yep, if you want to implement it from scratch, thats an option (way more painfull then using zerosheets) :)
Didn't https://www.glideapps.com famously start with Google Sheets backend?
Yes, we’ve powered millions of apps with Google Sheets.
Levels.fyi as well: https://www.levels.fyi/blog/scaling-to-millions-with-google-...
https://github.com/benborgers/opensheet#readme Is the best and simplest tool for turning Google Sheets accessible quickly.
not if you need authentication...
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.
I thought it was pretty restrictive, no more than 60 writes per minute, but I'm not sure about the reads restrictions.
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.
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.
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.
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.
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.
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.
For reading sheets, it's better to use the "share as CSV" option since that gets cached pretty well w/o limits
I've resisted this temptation to integrate with google apis for these 2 specific reasons rate limits and api changes.
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.
My Google API rate limit is way bigger then 60/minute.
Couldn't you cache the reads? Not many usages really require real-time from their data store.
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.
It's max a couple of hours work to cache in some local database like sqlite or in memory.
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.
Not that hard. Like 10 lines of code to get a decentish cache going.
Assuming you have Redis
Redis uses like 5mb of baseline RAM and can be deployed in a few lines of docker-compose.
[deleted]
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.
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?
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.
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.
Nice product.
Couple of suggestions/questions from using Google sheets to put enter some data that changed manually via back office admin which we then showed in a website that was accessed by thousands.
1. Will I hit rate limits if I access this API often via say GET requests from a public facing site? (What we did was used Redis as an intermediate cache)
2. Let's say the data on the sheet is changed, is there a webhook to notify the change? (What we did incorrectly initially was to rely on polling for change detection. Which was a bad idea especially as we grew increasingly reliant on this. Instead, the better way to do this is to put an 'Update' button in the sheet which then triggers an API call to an intermediate server to bust the cache I mentioned in the previous point. This also initiated a new fetch of the sheet data. A more user-friendly/better UX way to do this would be to use the Appscript Sheet APIs sheet change event handlers which can be leveraged for this if you don't want to enforce an update button on users though I haven't used this myself. [1])
In your case, I guess the way to do 2 would be to ask users to clone a sheet that has this Appscript code embedded in it when they create a new sheet that must be served with your API. No sure if the UX of that is acceptable though.
Wish your product success.
[1] https://developers.google.com/apps-script/reference/script/s...
Couple of suggestions/questions from using Google sheets to put enter some data that changed manually via back office admin which we then showed in a website that was accessed by thousands.
1. Will I hit rate limits if I access this API often via say GET requests from a public facing site? (What we did was used Redis as an intermediate cache)
2. Let's say the data on the sheet is changed, is there a webhook to notify the change? (What we did incorrectly initially was to rely on polling for change detection. Which was a bad idea especially as we grew increasingly reliant on this. Instead, the better way to do this is to put an 'Update' button in the sheet which then triggers an API call to an intermediate server to bust the cache I mentioned in the previous point. This also initiated a new fetch of the sheet data. A more user-friendly/better UX way to do this would be to use the Appscript Sheet APIs sheet change event handlers which can be leveraged for this if you don't want to enforce an update button on users though I haven't used this myself. [1])
In your case, I guess the way to do 2 would be to ask users to clone a sheet that has this Appscript code embedded in it when they create a new sheet that must be served with your API. No sure if the UX of that is acceptable though.
Wish your product success.
[1] https://developers.google.com/apps-script/reference/script/s...
Are Fortune, The Weather Channel, Fast Company, and National Geographic really your customers?
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.
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.
Based on OPs description, I wonder if this company was spun up while already doing business with those companies.
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.
I use BaseQL which is a GraphQL layer for Airtable. It's worked for 3 years. For performance reasons after we translated our catalog we had to put in postgres + hasura as middleware but we still use BaseQL all over. It's rad.
https://www.baseql.com/
Being able to get data joined across sheets is priceless.
Being able to get data joined across sheets is priceless.
My startup currently does this. Not as an API. I have different sheets set to populate different firebase relatime databases on a build script. I think it's the right way to go for early startups because then everyone can help build the product. I will be setting it up for our translation dictionaries.
I use Opensheet as the backend for storing reviews of my book. It's not CRUD, but easy enough to fetch data as JSON and display it on a website (and for free!).
Highly recommended.
https://github.com/benborgers/opensheet
Highly recommended.
https://github.com/benborgers/opensheet
x2, opensheet is one of those tools I use so often I forget how useful it is. It’s so nice to be able to take form responses (or just generic svg data) and display it to a user base without having to setup a SQLite database and APIs.
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.
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.)
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.)
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 :)
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 :)
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?
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?
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.
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.
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.
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.
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.
curl is good for isolating issues. Here's requests to https://zerosheets.com & https://www.zerosheets.com: https://gist.github.com/Buzer/67adedefe59f9efdf3b16b0b8def11...
Wrote something similar a decade ago: https://www.mooreds.com/wordpress/archives/1359
Hope things have improved over that time.
Hope things have improved over that time.
How does it compare to teable[1], which is a postgres + airtable fusion?
I can see that Google Sheet has higher availability but fewer builtin dashboards.
[1] https://teable.io/
I can see that Google Sheet has higher availability but fewer builtin dashboards.
[1] https://teable.io/
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
Do you have any plans to open source?
[0]https://sheety.co
Why do you trap yourself into vendor lock-in to a proprietary Google service, instead of easy open source BI solutions such as Pretzel (https://pretzelai.github.io/) or Datami (https://datami.multi.coop/?locale=en)?
This looks like an awesome product!
I'm currently in a bit of the same problem. Make an MVP of a webapp. What makes my predicament extra annoying is that my toolbox is primarily Microsoft. I have Power Automate, SharePoint, Office365, etc available to me. I do not have Azure unfortunately.
The answer is spin up a flask app with SQL and do things right. Does something like Zerosheets exist for the MS environment?
Really cool product! I'm bookmarking it for future products that don't need to live in MS land.
I'm currently in a bit of the same problem. Make an MVP of a webapp. What makes my predicament extra annoying is that my toolbox is primarily Microsoft. I have Power Automate, SharePoint, Office365, etc available to me. I do not have Azure unfortunately.
The answer is spin up a flask app with SQL and do things right. Does something like Zerosheets exist for the MS environment?
Really cool product! I'm bookmarking it for future products that don't need to live in MS land.
I also used Google Sheets as a datastore backend: https://github.com/adam-p/danforth-east
It's a community association membership management thing, and I wanted the (non-technical) association board members to be able to easily inspect the data and do something useful with it if I moved away. It has mostly been successful, except that sometimes someone will manually edit the sheet and break something.
It's a community association membership management thing, and I wanted the (non-technical) association board members to be able to easily inspect the data and do something useful with it if I moved away. It has mostly been successful, except that sometimes someone will manually edit the sheet and break something.
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.
so not just google sheets as the backend, google sheets as your personal backend for you instance of the app.
I made a family app with similar scope using Glide and I’ve been using it with my family for several years now.
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.
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.
to use zerosheets, you need to allow it: See, edit, create, and delete *all* your Google Sheets spreadsheets.
Seems like you'd want a special purpose Google account then, rather than your personal one with your financial spreadsheets in it.
Based on my experience, you'll get problem when the data is somehow large, and at that point it'll be too hard of a problem to fix / optimize CMIIW. Use it for prototyping and migrate to your own backend, or make a driver compatible with google sheet api, to connect to sql.
> CMIIW
First time I’ve seen “correct me if I’m wrong” abbreviated like this, good to know.
First time I’ve seen “correct me if I’m wrong” abbreviated like this, good to know.
Very opportune! I'm just building an app that needs to interface with some sort of spreadsheet. I was going to use Airtable because their API is just better than Google Sheet's, even though I kinda prefer Sheets. I'll give this a try this weekend and see how it goes!
Nice work! Small typo: there should be no apostrophe in "Your APIs," as shown on https://www.zerosheets.com/docs.
I get invalid cert but I love this concept, I'm curious what performance is like. Would love to see a side-by-side of an API/DB vs Sheet-based APIs.
Can you share the tool that transforms the spreadsheet into a realtime API? Sounds creative and interesting!
Can you share the tool that transforms the spreadsheet into a realtime API? Sounds creative and interesting!
Great job having fun and practicing making a company. Love it. Keep going! I remember using this one https://www.sheetgo.com/ back when they were starting. Fun to watch them grow!
Good luck!
Good luck!
Actually it was https://sheetsu.com/ ! :)
Good memories
Good memories
Website design is very nice fair play. Is there a cost to using the product? I dont see that mentioned on the site and theres no pricing page listed. That would naturally cause me to freeze a little, rather than authenticating with my google account
[deleted]
This is a great way to prototype. I got a moderate distance along a road of a startup idea and we were going to start with a simple Google Sheets backend.
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.
I found the docs pretty minimal as well. Ironic since they specifically called out Google for shallow docs in the description.
https://afoo.me/tools.html
I built this page with several google sheets as data source，；）
I built this page with several google sheets as data source，；）
Reminds me of the days when we'd write plugins for Microsoft Office that would populate data from an API and even send edits.
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.
Fast efficient easy.
Might've been overlooked, but "fastly" should probably be "quickly" on the main page.
There are open source tools out there already including budibase, nocodb, joget
What is the selling point of your solutions versus something like Baserow ?
Why is this quicker to implement than free-tier RDS on AWS?
Google apps script already lets you write http handlers...
They seem pretty powerful.
They seem pretty powerful.
Do you know of any docs on this?
reminds me of fuckitdb https://pypi.org/project/fuckitdb/
The link is broken. Says "no https for this domain"
Wow ! So smart and useful! Thank you for sharing.
no https for this domain
Great stuff. I need to tell some of my friends about it. Thanks for making it!
levels.fyi started out using Google Sheets as a back end. Submissions were done via Google Form, inserting into the sheet.
https://www.levels.fyi/blog/scaling-to-millions-with-google-...
https://www.levels.fyi/blog/scaling-to-millions-with-google-...
wow this is now the 9th time someone has built this lol
you can use Google Sheets as front-end too. lol
[deleted]
Maybe we can replace Bitcoin with Google Sheets?
already done: https://news.ycombinator.com/item?id=35119317
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