I tried going all on on Google Sheets earlier this year. I used the Python API and like almost nothing worked consistently.
I got 1 sheet to work well with like 1k rows and thought woohoo this is great! So I went all-in, put 25k rows in and Google pooped the bed. It gave errors all the time, rows and columns were funky. API issues galore.
SQLite however handled 25k rows without any errors, always rock solid.
I gave up. Overall a terrible experience once I got past 1k rows. I'm glad it's worked well for you.
I only had like 10 columns, so it wasn't like massive amounts of data. I was linking to a file out in Google Drive and that was where most of my issues were. Google drive's API was 99.99% completely useless.
My takeaway from the experience was, Google proclaims they have an API, but it seems more like nobody tests it or cares about it. Perhaps it's malicious compliance, they have it because they have to have an API, but not because they expect anyone to actually use it.
Yes, many of our customers hit the issues you mentioned as they scale spreadsheet-based applications. We built Big Tables for this case: https://www.glideapps.com/big-tables
You keep most of the convenience and programmability of a spreadsheet, but you get Postgres scale and a first-class API (which includes atomic table swapping for updating large datasets). It also supports live updating of any views displaying that data.
Thank you for discussing this openly, it can be a real bummer to write something, test it, have something work up to a point then fail due to situations outside your control.
You probably just saved me about 20 hours of coding.
Unreasonably large spreadsheets are surprisingly common. Excel 2007's row limit was 65536, and nowadays it's over a million. Spreadsheets actually get that big, too.
An app I worked on once wanted a Google Sheets Integration - just a quick button to import rows from the database and into the spreadsheet so that the client can do some quick visualization and pie charts (I offered them a readonly DB account but they didn't know any SQL).
Few years have passed, and there are currently 470 THOUSAND rows with 20+ columns in that spreadsheet which is used almost daily.
Rewriting this into a proper web app would take maybe a week (the API is already there and used by GS), but was never deemed a priority, so it will keep existing in the backlog until we hit row or cell limits - after which it will suddenly become urgent...
Did the commenter say that it was being edited concurrently? Because that's the only circumstance in which it would really be acceptable for it to break like that.
which is an awful idea because some versions of Excel will, if you open a file with more than 1million rows (or ~16k columns), automatically truncate and delete the excess data! And don't even get me started on automatic date conversion.
If you have more than a few thousand records, this is the wrong tool for the job. I've used Google Sheets as a "database" for a few applications in the past. In each case, it was an internal tool that did not generate more than a few thousand records, if that.
Even text files (like CSV) are fine for the amount of data the OP has trouble with, and Excel certainly is, so it’s unclear why Google Sheets should already balk on that. Google Sheets has a limit of 10 million cells per sheet (so for 25000 rows that would be 400 columns), and really shouldn’t be running into errors for sheets below that limit.
I guess? 25k rows with 10 columns shouldn't be a big deal. LibreOffice, Excel, Numbers, they all handled this data without trouble.
It is 99% static data, it would be updated maybe once a year if that. The point was to give users something they were familiar with to look at the data, search it, etc. While being in the "cloud" so I only had to update one place whenever I do get tasked with an update and to handle authn/z easily with no hassles.
Anyways, I used SQLite and a very simple web frontend(it had 3 screens: search, table view and record view). Solved the problem and moved on. Search just filtered the table view.
> If you have more than a few thousand records, this is the wrong tool for the job.
Just to be precise, Google Sheets might be the wrong tool for the job. Other spreadsheet applications can handle "more than a few thousand records" easily, seems this is a Google Sheets specific issue.
The projects I worked on always had a mechanism to dump data into a proper database. For example, I built a daily scraper to collect some of the inventory. I didn’t keep all the data in a single sheet. Instead, all the data was stored in a cloud-based managed SQLite or PostgreSQL database, or sometimes a local SQLite database. Only the day's data was stored in Google Sheets because the client wanted to see the spreadsheet themselves and have the UI be accessible for their users.
I have had a similar experience with their API. Very verbose json structure, lack of stable row IDs, inconsistent data types.
And being a spreadsheet, it also has to have styling/formatting details, complicating it even more.
There are extensions that let you query Excel worksheet files directly from SQLite (no importing, no transformation, just direct query). It's quite fun to do; you can use Excel (or Libre office, etc.) to maintain the spreadsheet, and build reports with SQLite.
Starting with a Google Sheet is perfectly fine, but for building a non tabular user interface, or having a few thousand records, I would directly switch to something like SQLPage with a SQLite database.
I got 1 sheet to work well with like 1k rows and thought woohoo this is great! So I went all-in, put 25k rows in and Google pooped the bed. It gave errors all the time, rows and columns were funky. API issues galore.
SQLite however handled 25k rows without any errors, always rock solid.
I gave up. Overall a terrible experience once I got past 1k rows. I'm glad it's worked well for you.
I only had like 10 columns, so it wasn't like massive amounts of data. I was linking to a file out in Google Drive and that was where most of my issues were. Google drive's API was 99.99% completely useless.
My takeaway from the experience was, Google proclaims they have an API, but it seems more like nobody tests it or cares about it. Perhaps it's malicious compliance, they have it because they have to have an API, but not because they expect anyone to actually use it.
Perhaps I'm just not cool enough for Google.