How to use a Google Spreadsheet as a database
api.blockspring.com
api.blockspring.com
I built HasGluten [1, 2] with react + google spreadsheet, hosted on github for free, you get a cheap, scalable, geo-distributed software stack, with simple interfaces to maintain both code (GitHub Pages) and data (Google Sheets — also great for the non-tech).
example, https://spreadsheets.google.com/feeds/list/1btWWclsRW6-wrIdC...
[1] https://github.com/hasgluten/hasgluten/blob/master/src/app/p...
You probably should try to do international free wiki-like open database of all foods with EAN codes. Maybe Fitbit and other vendors could chip in to provide food details.
I would love to scan all my food EAN codes and see in the computer what I have in my refrigator in calories, protein etc.
P.S. Google spreadsheet is not enough for database of all the world's food
Jokes aside, we maintain the list mostly for ingredients. Many people, especially "novice", often ask the same questions -- or you may have doubts for strange/unfamiliar foods, for instance if you're traveling in a foreign country.
As a person who gets asked to fix these kinds of projects once they hit a wall (performance/concurrency/etc) and then have to migrate them to a proper DB platform, just stop it! Put it on in a DB up front and save some poor developer their sanity.
Please.
Want a cheap database? A Postgres RDS micro instance costs $51 a year upfront or $0.018 per hour. Medium is $200 or $0.073, and that medium instance will probably be more than enough for a dozen projects.
You get the solidity and ease of use of PostgreSQL with all the admin details abstracted away by RDS, and you can be up and running in 10 minutes or less. Get AWS account, spin up DB, write to DB.
Should one of your projects take off and require more, it's a one click upgrade. Or you can pg_dump in seconds, and rebuild it on a separate instance and account and point your app at it. And you get backups and all kinds of nice things out of the box.
I seem to be in the minority in this thread, but I personally much prefer using UPDATE/INSERT/DELETE in the command line to modify data, than clicking on a cell and typing the modification.
At one job I'm like "people, just use Access, it's installed on your computer" and they look at me like I'm talking dark wizardry shit with their fingers itching on their pitchforks because they don't know if I'm going to eat their babies.
Arrrrgh!
Imagine if you had a Google Sheets adapter for ActiveRecord. You could start any project this way and it would be super easy to then migrate it to another supported DB.
But in all seriousness, using a spreadsheet for prototyping makes perfect sense. Why waste a ton of time setting up a database when you're still figuring out what you are doing, and a spreadsheet works just fine? Yes, there's some hassle when you have to migrate, but that's compared to the hassle of setup. The amount of time to get the first iteration launched is a LOT more valuable than time down the road.
The thing is these solutions all sounds great on paper, but in practice for the common person? Not so much. For those people that know what their doing it really doesn't matter because they know what they are doing and update as they scale.
It's the 99% of the rest of the people who see "how easy that was" and suddenly they're over their head. And they are the same people who tell you that you can't change anything about the broken ass interface bolted onto the spreadsheet while you're fixing it.
"Can't you just fix it so it will stop crashing? Why do you want to change all of this? We don't have approvals to change this, the spreadsheet is what was approved by change control. Just make it work."
IT WILL NEVER WORK. Go away. =D
How does blockspring deal with Google Spreadsheet availability issues? I remember the spreadsheet not always being available.
What "availability issues" did you run into? I haven't noticed any with my sheets yet
We went from using Spreadsheets to using our own flat files on Drive, but the API service would throw random rejection errors for both.
Long story short, we learned that you should not try to use Drive or Google docs as a program database. It's designed first and foremost for users.
https://code.google.com/p/gdata-java-client/ <--- Spreadsheets api is one of the three apis still alive out of all gdata apis
One of the greatest things about MS Access was that it made it trivially easy to create master-detail forms where a sub-form contains records related to the master record shown in the main form.
Even if I didn't make any money on the deal, at least I'd be able to have something better for tracking the progress of the cub scouts awards. It'd also let my wife and me set up the complex budget that we're trying to do right now in spreadsheets.
I bring this up with people who work for small businesses and they all recognize the pain point. Those that are old enough remember the good old days when you could put an Access file on a network share and then run your entire business out of it.
But I think Access is awesome for making and battle-testing prototypes that can eventually become actual CRUD apps. Great intermediate step somewhere in-between e-mailing spreadsheets and building a Rails app, for getting shit done at the office.
However I still can't see how to do the "getting started with SQL" type stuff, for people who don't want to use the command line for everything, but still want to use SQL in not too complex ways, maybe a more friendly version of phpmyadmin (and friends).
I thought of it since a buddy has some Tiki Bar that he wanted a site for, and I really didn't want to drop into WordPress or anything serious. Knew he could handle a spreadsheet.
Checkout http://www.tarbell.io/ - It's a CMS designed around google sheets. I think it's a bit of setup, but might be a solid solution.
- http://blog.apps.npr.org/2014/04/23/how-we-built-borderland-...
- http://www.gamasutra.com/blogs/WillHankinson/20150323/239489...
# 1st) Using Google Spreadsheets as a CMS
In this case you'd store data in a Google Spreadsheet and retrieve the content before showing to the user. Probably it makes sense to put some durable caching in place so you can sync the cache offline and worry less about Google Spreadsheets API downtime, quotas or latency. In this scenario the app would only read data from the Spreadsheet and not write. It will probably not support writes consistently for anything more than a toy.
# 2nd) Use Google Drive to store user Data
The main difference here is that in this case it would make more sense to store the spreadsheet in the user account, not yours. You'd fetch the userData once he logs in your application. If this is the use case there are better things than writing spreadsheets to users Google Drive. There's actually a feature in Google Drive to store application data:
But this is especially nice when you build a layer on top of Google reduces lock-in, instead of adding another proprietary API. This is what I did with sheet-down[1], which turns a Google Spreadsheet into a LevelDB-compatible data store that can be swapped out with a file system or other compatible backend[2] once you outgrow Google.
[1] https://github.com/jed/sheet-down
[2] https://github.com/rvagg/node-levelup/wiki/Modules#storage
Really curious, because I'd love to use something like this in our app.
In the latest version, it comes with a server-side API cache to prevent GSheet latency and availability issues.
Note: I'm the founder of APISpark
1. Joining data will be very slow. If you need to access 500 database to get the "comments" on a "post", you're going to have issues.
2. How will you change the database structure as you iterate?
3. Storage cost of data is so low, that by the time you would start paying for data, you would have greatly exceeded the capabilities of Google Sheets.
4. Google sheets are slower than databases - there are no indexes, keys, the data is not stored in a way meant for most db operations (selections, etc)
I'm sure theres more but these seem to be the biggest ones for me.
That said, you should definitely try it - it's an interesting project at the very least.
Even then you hesitate to give rights to laymen to edit the spreadsheet since everything breaks if they screw up. And what is the point if it can't be shared?
Still very expensive, but free for non-profits!
blockspring has SQL like commands in the client. gridspree has data formatting in the client.
I haven't hit a limit yet and tested with 200 queries / minute.
Will update this comment when I find that out.