
How to use a Google Spreadsheet as a database - donpinkus
https://api.blockspring.com/blog/google-spreadsheet-as-database
======
ecesena
I'm a big fan of spreadsheets instead of db -- it has it's limitations of
course, but works great for apps with a few thousand read-only records
(items/products or even just text captions).

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).

[1] [http://hasgluten.com](http://hasgluten.com)

[2]
[https://github.com/hasgluten/hasgluten](https://github.com/hasgluten/hasgluten)

~~~
dpweb
You can grab published Google Sheets in JSON using,
[https://spreadsheets.google.com/feeds/list/{{doc_id}}/od6/pu...](https://spreadsheets.google.com/feeds/list/{{doc_id}}/od6/public/values?alt=json)

example,
[https://spreadsheets.google.com/feeds/list/1btWWclsRW6-wrIdC...](https://spreadsheets.google.com/feeds/list/1btWWclsRW6-wrIdCD8Po5a0VTSh2BJhiPohWiJX8WaQ/od6/public/values?alt=json)

~~~
ecesena
I'm actually using multiple sheets and jsonp [1], but that's essentially what
I'm doing.

[1]
[https://github.com/hasgluten/hasgluten/blob/master/src/app/p...](https://github.com/hasgluten/hasgluten/blob/master/src/app/pages/Layout.js#L161)

------
bcRIPster
A spreadsheet is NOT a database! Argh!

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.

~~~
hbhakhra
He mentions that its explicitly for quick prototyping. I've started a few
projects but stopped when setting up the infrastructure became too tedious.
For the quick and dirty prototype, this seems perfect.

~~~
bcRIPster
I understand, but every time I get handed one of these it's because someone
who didn't know what they were doing was rapid prototyping something and woops
they have 200 hundred users and it's crashing, it's corrupting data, etc...

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!

------
mooreds
I beat my head on APIs from Google spreadsheets, so good on ya!

How does blockspring deal with Google Spreadsheet availability issues? I
remember the spreadsheet not always being available.

~~~
donpinkus
Thanks! Yea I was trying to use Google Spreadsheet, getting annoyed with the
JSONP and the goofy way they returned their JSON, so figured I'd just wrap it
in something easier to use...

What "availability issues" did you run into? I haven't noticed any with my
sheets yet

~~~
justincormack
I run a cache in front of mine. It is usually available, but can be slow.

------
mavhc
Where's the free, web based, easy to use database, the web version of MS
Access?

~~~
ryanjodonnell
[https://airtable.com/](https://airtable.com/) is exactly what you are looking
for

~~~
bradleyland
I've looked at AirTable. It is, unfortunately, not MS Access for the web. It's
cool, but the two are not analogous.

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.

~~~
jasoncrawford
You might be interested in Fieldbook; I think we got the master-detail thing
right: [https://fieldbookapp.com](https://fieldbookapp.com)

------
ngoel36
I'm shocked that using something like [https://github.com/gimite/google-
spreadsheet-ruby](https://github.com/gimite/google-spreadsheet-ruby) to, at
the very least, let Google Spreadsheets as a proxy for a bare-bones CMS hasn't
been more widespread

~~~
donpinkus
Yea seriously...

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/](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.

------
dudus
In the real world I think there are 2 main use cases here.

# 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:

[https://developers.google.com/drive/web/appdata](https://developers.google.com/drive/web/appdata)

------
jedschmidt
I really like the idea of using Google Spreadsheets as a quick and familiar
GUI for entry or querying on data sets, as long as you understand the
tradeoffs (write latency isn't great and they max out at 400,000 cells).

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](https://github.com/jed/sheet-down)

[2] [https://github.com/rvagg/node-
levelup/wiki/Modules#storage](https://github.com/rvagg/node-
levelup/wiki/Modules#storage)

~~~
bradleyland
How do you handle type constraints? For example, how do you prevent users from
inputing text in to number fields? I know Google Sheets has validators, but I
don't know of any way to restrict someone from changing those validations
while also providing the ability to input data.

Really curious, because I'd love to use something like this in our app.

~~~
jedschmidt
My situation is for an intranet environment, so I suppose I could just ask
nicely that they don't change validations. But chances are most of my target
users aren't familiar enough with Google Spreadsheets to even get that far.

------
jlouvel
APISpark (PaaS for APIs) is capable of creating a REST API (JSON/YAML/XML
formats) on top of a Google Spreadsheet, using it as a database:
[http://restlet.com/technical-
resources/apispark/tutorials/tu...](http://restlet.com/technical-
resources/apispark/tutorials/turn-spreadsheet-to-api)

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

------
eatonphil
I've been wanting to use Drive as a db recently on an app I'm working on as
cheaper/free data storage. That is, instead of hosting a db and storing user
data there, I would store it on each user's google drive. I see it as a way to
save costs by not worrying about the security of my databases, cost of
space/uptime/traffic, etc. What are issues am I not thinking of?

~~~
donpinkus
It's an interesting idea - here are some issues to consider:

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.

~~~
dudus
Storage costs are not low. They are free since Google Spreadsheets do not
count towards your Storage Limits. You'll be limited only by the 2M maximum
cells a spreadsheet can hold.

------
Ciantic
I have used Google Spreadsheet as a database, the hassle is not worth it. You
need to do some serious caching: insert caching, update caching etc. The API
calls weren't very sturdy few years ago.

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?

------
jaybna
Closest any company has come to a decent web version of Access is Intuit
Quickbase. But it is priced for enterprise and not hobbiest. Lacks full SQL
but can do some pretty amazing things. Also has reasonable REST-like API. I
built a PoC data backend for an iPhone app really easily.

~~~
aofstad
We also recently released an early version of the Airtable API. It provides an
API that's specific to each database you've configured in the app:
[https://airtable.com](https://airtable.com)
[https://airtable.com/api](https://airtable.com/api)

------
eddyparkinson
Not quite the same as gridspree, but has a lot in common.

blockspring has SQL like commands in the client. gridspree has data formatting
in the client.

[https://assembly.com/gridspree](https://assembly.com/gridspree)

------
ww520
What're the rate limits on Google Spreadsheet on update and read?

~~~
donpinkus
That's a really good question.

I haven't hit a limit yet and tested with 200 queries / minute.

Will update this comment when I find that out.

------
grw_
here is django library for this: [https://github.com/georgewhewell/django-
sheets](https://github.com/georgewhewell/django-sheets)

~~~
donpinkus
nice. who wants to fork this for Rails? :P

------
wuyingzhong1
1\. open google spreadsheet 2\. think about your application 3\. chose normal
form, design schema, add integrity constraints, build indexes and query
execution and optimization engine and done!

------
mslate
Nice content marketing

~~~
donpinkus
Thanks! Nice observation :)

------
clucktheduck
this is cool beans!

