5 ms·
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 lik
by zie 2y ago
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.
Perhaps I'm just not cool enough for Google.
- anyfactor 2y agoThe 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.
- matharmin 2y agoIt feels a little misleading to say you're using Google Sheets as the backend database, when you need an actual database in conjunction with it.
- mattigames 2y agoExtremely misleading, in this case Google Sheets it's working more like a frontend
- worthless-trash 2y agoThank 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.
- fragmede 2y agoto be fair, 25k rows seems like a few more rows than I'd use a spreadsheet for
- benatkin 2y agoDid 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.
- lelandfe 2y agoWait till you hear about the spreadsheets researchers use... My chemist friend uses Excel files that regularly exceed 2GB of just text...
- levocardia 2y agowhich 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.
- anthk 2y agoComputer illiteracy and shitty platforms (MSOffice is not apt for science at all) costs Scientists biliions on broken research: https://www.nature.com/articles/d41586-021-02211-4?error=cookies_not_supported&code=ddc2fb47-deae-4b67-bc31-e37fac73947d https://www.nature.com/articles/d41586-021-02211-4?error=coo...
- terribleperson 2y ago
- neeleshs 2y agoI 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.
- hliyan 2y agoIf 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.
- p2detar 2y agoA few thousand like the product of rows*cols? Just curious.
- baobun 2y agorecords = rows
- layer8 2y agoEven 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.
- diggan 2y ago> 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.
- mattigames 2y agoI wouldn't be surprised if it's not fixed on purpose, they do have cloud DB offering and all that overpriced jazz to sell you.
- baobun 2y agoCan also be a feature to cap the potential scale for a PoC you want to keep from getting productized as-is.
- lovasoa 2y agoStarting 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.
- bambax 2y agoThere 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.
- zoomablemind 2y agoCould you mention a few of such, to save time going through search results?
- bambax 2y agoThe one I use is xlite https://github.com/x2bool/xlite https://github.com/x2bool/xlite It's read-only and mounts spreadsheets as virtual tables. Very fast. Perhaps more relevant to this thread, libgsqlite loads Google Sheets as virtual tables: https://github.com/0x6b/libgsqlite https://github.com/0x6b/libgsqlite (I haven't tested it though.)
- dvdsgl 2y agoHello! Founder of Glide FTA. 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 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.
- causal 2y agoThank you for also having Airtable support, which I vastly prefer over Google Sheets for any kind of API work