
Google Spreadsheets and Python - happy-go-lucky
https://www.twilio.com/blog/2017/02/an-easy-way-to-read-and-write-to-a-google-spreadsheet-in-python.html
======
Lightbody
I _love_ Google Sheets. It is so much more powerful than 99.9% of the users
appreciate. Here is how I use it at my day job as head of PM at New Relic:

    
    
      - Fetch deals closed and lost hourly from Salesforce
      - Fetch for each of our 14k+ paid accounts usage metrics using our Insights product
      - Pull both items into a nice color-coded business dashboard that is near realtime
      - Send said dashboard out as a PDF to a bunch of stakeholders daily/weekly
      - Save PDF snapshot into Google Drive so I can easily pull up historical reports
    

If you haven't tried out Google App Script and supercharged your spreadsheets,
give it a shot. It's also basically the only way the engineer in me gets to
code much these days, so I have fun with it too :)

PS: Google Sheets is definitely much slower for pure spreadsheets than Excel,
no question about that. But by using cloud-based JavaScript + custom code, I
can often work around that issue by writing functions that process the data
quickly and report on what I need. Ultimately I end up getting much more out
of it than Excel.

~~~
rebootthesystem
I get all the good things Google Apps do. It's wonderful. Yet, until Google
has a "contract with our users" that includes a level of customer service and
human conflict resolution I would not touch them with a ten foot pole.

Why? Because they could take it all away --and I do mean ALL-- overnight.
Email, apps, etc.

I've seen many people run into the Google ban for reasons not more complicated
than not being experts and using one of Google's tools in a way that triggers
an algorithmic ban. And this doesn't have to have anything to do with fraud or
scams.

Had one client, probably ten years ago, who moved all of his 200+ domains to a
service Google introduced at the time called "Google for Domains". A domain
parking service with ads inserted into the parked domains auto-magically by
Google. Prior to that all of his domains were parked at GoDaddy, who used
Google to stuff ads into the domains. As soon as the service was offered he
figured it was a good idea to remove GoDaddy as the middle-man in that
equation.

He transferred all of his domains. There was an approval process. All domains
were approved overnight. Done deal. Right?

Wrong! Two days later Google sent him an email informing him that all of his
Google accounts were now permanently suspended (aka: closed) with no recourse.
Reason given: Unusual click activity on some of the domains parked with Google
for Domains.

This guy ran (runs) a multi-million dollar manufacturing business. The very
idea that he would sit there clicking on ads to earn a quarter of a cent per
click is, well, stupid.

Anyhow, this one event cost him all of his Google tools. His email, calendar,
contacts, apps and whatever else he was using at the time. No recourse. Not
conversation with a human being. Nothing. Done. Puff. Evaporated.

Since that one event I have been of the opinion that using any of these Google
tools for a business is, to put it plainly, stupid and dangerous. You could be
betting the farm on a petulant algorithm and no customer service of any kind
to protect you from it.

I still have Excel files from decades ago that I can open and manipulate
today. Microsoft could go out of business and I could still open these files.
And the software has never lacked functionality, either directly, through add-
ins or programmability.

The bottom line for me is that if something is business-critical it isn't a
good idea to rely on a service that could be yanked overnight (I do mean that
literally). If Google had customer service and a human (and humane) process to
deal with issues it would be a very different story. I don't want free stuff.
We have tens of thousands of dollars invested in all kinds of software. I want
stuff I can rely on because my businesses depend on it.

The only way you are going to have to security is if you pay for well
supported software that has a team behind it who understand how important it
is for a business to have the ability to wake up every morning knowing that
the tools they come to rely on for their daily work won't evaporate overnight.

I truly don't care how many shinny new things, candy and chocolate Google
throws on the table. Until they prove they understand this one point they
represent a business-killing risk nobody should be willing to accept.

BTW, this is a problem with nearly all Internet giants. Facebook, Amazon and
others are horrible companies from the standpoint of how they deal with their
business customers.

~~~
victorhooi
In terms of your anecdote about your client's experience - I can't speak to
this, as I'm not directly familiar with this "Google for Domains" service and
I don't have enough details from your post to comment.

However, I can assure you that Google definitely has a customer support
department - I work with them every day =).

Also - it's important to distinguish between the free products we offer - and
the paid products - where you actually give us money. In the latter case,
there is nearly always a customer support hotline, and in many cases, a
binding SLA agreement.

I work in Google Cloud, on the support organisation side of things - and I can
confirm there is definitely phone support (along with chat and email), we do
have SLAs on all of our products and you are definitely speaking to a human.
It's not particularly hard to reach a person - either go through your admin
panel ([https://admin.google.com](https://admin.google.com) \- click the big
question mark), or all of our 24/7 support numbers for each country are right
there on the website:

[https://support.google.com/a/table/3247295?hl=en](https://support.google.com/a/table/3247295?hl=en)

That's on the business side. On the consumer side, the phone numbers shouldn't
be too hard to find either on a search e.g.:

[https://support.google.com/googleplay/answer/7100415?hl=en&r...](https://support.google.com/googleplay/answer/7100415?hl=en&ref_topic=3364260)

My own anecdote - several years ago before I started with Google, I subscribed
to Google Play Music - I accidentally un-favourited a track once, and I
couldn't find a way to find it again. So on a whim I rang Google Play Music
support, and went straight to a person within seconds. She was very friendly,
and tried to help me find the history - unfortunately it wasn't there, but the
customer service was pretty damn good - a heck of a lot better than all of my
experiences with several un-named mobile phone and broadband companies. (Hint:
I'm in Australia)

Disclaimer: I work for Google.

~~~
rebootthesystem
You might not know the answer to this question. If you don't, it might be
interesting to see if you can get to the answer internally.

Let's say I setup a site and have AdSense place ads on it. I don't touch any
of the ads. I never click any of them. Yet, for some reason, the ads see a
bunch of click activity. The reasons can be many, from sheer randomness to a
competitor using it as an attack vector (to deny revenue and more).

I have seen this precise scenario lead to the absolute shutdown of a Google
account. And that means every single service, including email and docs. Gone.
Done. No recourse. No way to even recover the data.

Has this changed?

Do you have a way for people affected by such algorithmic issues to engage in
a conversation with something that does not resemble a totalitarian regime?

The paid vs. free part is immaterial. People are relying on your products and
offerings. Free is a strategy for dominance. I can understand free products
not having a human in the loop. What I can't understand is the
dictatorial/totalitarian relationship with your users, who, free or not, have
come to rely on these tools.

~~~
victorhooi
I believe you're referring her to Adwords clickfraud - is that right? I don't
work on that team, so can't comment on their policies (and even if I did, I
probably can't comment publicly).

However, my understanding is normally AdWords accounts are suspended for
billing/fraud related reasons (e.g. you don't pay your bills) - e.g.:

[https://support.google.com/adwords/answer/2375414?hl=en-
AU](https://support.google.com/adwords/answer/2375414?hl=en-AU)

If you AdWords account does get suspended, there's a form to appeal it here:

[https://support.google.com/adwords/contact/pf_suspended](https://support.google.com/adwords/contact/pf_suspended)

I haven't seen it lead immediately to the actual Google login itself being
suspended. However, if your the actual Google account was suspended - there is
a set procedure you can follow to un-suspend it - there should have been a
link in the notification email.

Also - paid or free isn't immaterial - it actually does make a difference.

If it's a free account (E.g. @gmail.com), you can fill in the online forms to
appeal the suspension.

If it's a paid account (i.e. part of a GSuite domain - or it's old name,
Google for Work), then what often happens are individual accounts are
suspended, and you talk to your own domain administrator to get that resolved.
There are ways to get a domain suspended (e.g. you don't pay your bills) but
they're usually fairly obvious.

(Disclaimer: I work for Google, but the above comments are my own opinions).

~~~
rebootthesystem
You are confusing your own products. AdSense. Not AdWords.

The various forms Google makes available for appealing suspensions are pretty
much useless.

I urge you not to believe what I am saying here as well as not feeding within
your own internal echo chamber. Use your own search product to search the 'net
for the thousands of stories of various forms of algorithmic account
suspensions, the damage they cause and how frustrating it is to get anywhere
with Google.

Based on what I've witnessed personally and what I've read online the idea of
relying on Google products for anything business critical terrifies me.

You --Google-- needs to truly care for it's clients and, as I said in a prior
post, issue a guarantee. I am NOT talking about a guarantee of uptime for a
service, that's irrelevant here.

What I am referring to are guarantees of service longevity as well as
protection and recourse from algorithmic account suspensions. A business
person needs to have the ability to address and discuss misunderstandings or
problems and not have their entire Google-provided infrastructure evaporate
overnight with nowhere to go.

To me the matter of free vs. paid is immaterial. Google and companies like
Google use free services to gather and monetize audiences. For example, nobody
would pay for search. Nobody would pay for a Facebook account. So you use free
to bait the hook and capture audiences. This is a technique as old as the
Internet, with browsers such as Netscape and earlier being the first land-
grab-through-free-products.

If you are going to have a virtual monopoly by resorting to free products you
also have to have the responsibility of not causing your user base irreparable
damage by pulling those product either by early termination of the services or
termination or suspension of account without recourse. At the moment you are
large enough to not have to care about this one bit and nobody has challenged
you in court for this terrible issue.

Start here and read through a few pages of links:

[https://www.google.com/webhp?sourceid=chrome-
instant&ion=1&e...](https://www.google.com/webhp?sourceid=chrome-
instant&ion=1&espv=2&ie=UTF-8#q=google+suspended+my+account)

~~~
victorhooi
You ask for guarantees that an account will never be suspended, or that a
service will never be discontinued.

I don't think that's even possible.

For one - abuse is a real thing.

Just ask Amazon about abuse and spam on their AWS platform. There is a reason
that you can't just type in any credit card, and spin up 100 EC2 instances, or
send 100,000 emails - depending on signals, Amazon seem to manually verify
some accounts. But on top of that, they take proactive steps to suspend
accounts acting in suspicious ways.

We do much the same.

It is a hard problem, and undoubtedly there are false positive and false
negatives. That is regrettable - but I don't think allowing AWS or GCP to
become a free haven for spammers or abusers is the answer.

You are correct that we need good procedures to deal with appeals of these
things.

Regarding never deprecating a product - Google has started _many_ products,
and some of those have been shutdown. I know there's a lot of angst on
HackerNews about Google Reader in particular - I myself used it a lot in fact.
However, taking off my Google hat - of all companies, they are usually the
best about letting you export your data, or migrate it away. When a product
gets shutdown, you usually have 6 months, a year etc. to export your data.
Even before I started there, they had Google Takeout which basically lets you
export your data from any Google product in portable formats e.g. mbox, JSON
files etc. Open standards are important to us - e.g. see our work on
Kubernetes.

If there's a specific are you think we're failing on - please let me know, and
I'll see if there's anything I am able to share, or if there's somebody I can
link you up with.

------
georgeaf99
A friend who works at the Dallas Animal Shelter (a non-profit) asked me to
help automate their process of generating reports from data gathered via
Google Forms, which they were doing by hand every month... As a developer who
is used to high technical investment into complex systems, I had to take a
completely different approach to storing their information.

Google Forms + Spreadsheet + Python works perfectly for use cases like this:
it is free, scaling isn't a concern, users can view and modify data at any
time, and users can create Google Forms at any time. If you are trying to help
a non-profit and aren't planning on maintaining their systems long-term, this
is a great approach.

Code for the project: [https://github.com/georgeaf99/das-care-contact-
forms](https://github.com/georgeaf99/das-care-contact-forms)

~~~
fragmede
Really curious in the "low" technical investment answer: where/how does the
Python end of this system get run?

~~~
jotux
>which they were doing by hand every month

I would assume they just installed python on whatever machine they were
originally using to hand-generate reports and the job of that person now is to
just run the python script and wait for it to finish.

~~~
tyingq
Google's AppEngine would likely be free or extremely low cost in this
situation. Gspread appears to work there fine:
[https://github.com/burnash/gspread/issues/39](https://github.com/burnash/gspread/issues/39)

------
btown
[https://gspread.readthedocs.io/en/latest/](https://gspread.readthedocs.io/en/latest/)
is great, but it's limited in what kinds of things it can set (formatting,
notes, etc.). One other option is to create an endpoint on script.google.com
which can access a much richer SpreadsheetApp API:
[https://developers.google.com/apps-
script/reference/spreadsh...](https://developers.google.com/apps-
script/reference/spreadsheet/spreadsheet-app) . You can POST to it using an
auth token from the same service account oauth creds (though you need to add
drive and drive.scripts to your scopes), and it can run arbitrary JS to
translate reads and writes from the JSON payload/response into API calls.

As another note, we realized that far more useful than _using_ Google
Spreadsheets as the canonical backing database, was to be able to
bidirectionally synchronize it with our primary database. That way, users who
wanted to annotate entities in spreadsheet form could do so in GSheets, always
working with up-to-date data, and keeping track of "I updated a.x in the
spreadsheet, but a.y was updated upstream, so merge the two." Here were the
semantics of our integration:

    
    
        Returns a list of updates between last_synced_data and sheet.
        Subsequently, if upstream_data is provided, then load it into the sheet,
        adding rows on the end as needed, or merging if there is a match in the merge_key column
        (note that any updates to the live sheet data since the last sync
        override any upstream data, and those live updates are returned without changing the live sheet).
    

The caller would then be responsible for taking the returned list of updates
and cleaning it for the database, as well as maintaining a record of what the
state of the last sync was. Essentially we maintain enough information to do a
three-way merge. We've since built internal applications that allow real-time
spreadsheet-like interactions in a much more domain-specific manner, but it
definitely did the job for quite a while.

If there's interest in seeing open-source code for all of this, we could
definitely extract from our corporate repo (we're
[https://www.belstone.com/](https://www.belstone.com/) ). Let me know!

~~~
arthurdenture
One thing to note is that the new version (v4) of the Sheets API can access
the same fancy functionality that Apps Script can. So if gspread moves to that
(which e.g.
[https://github.com/burnash/gspread/issues/435](https://github.com/burnash/gspread/issues/435)
alludes to), some of those limitations will go away.

(And the sync thing is neat!)

~~~
j_s
[https://github.com/nithinmurali/pygsheets](https://github.com/nithinmurali/pygsheets)
uses APIv4; someone couldn't wait!

------
cyberpanther
The big downside I've found using this a lot in the past is that Google has no
SLA for sheets. Sure its Google, so it has high availability but things can
quite often fail with no support from Google. So just be careful of building
anything mission critical with this. The API can be wonky every once in a
while. At one point it even took them like 6 months to fix an issue I had with
the Apps Script API. If my business relied on that functionality, I would have
been screwed.

The Jupyter Notebook use case sounds great but if you move to something that
is more critical use a real data store.

~~~
victorhooi
Regarding your claim about SLAs - there actually is an SLA for all of the paid
Google Cloud products - e.g. the following would apply for GSuite, which
covers Google Sheets:

[https://support.google.com/work/answer/6056635?hl=en](https://support.google.com/work/answer/6056635?hl=en)
[https://gsuite.google.com/terms/reseller_sla.html](https://gsuite.google.com/terms/reseller_sla.html)

If you mean the _free_ products e.g. Google Sheets with a consumer account -
AFAIK, there isn't a contractual SLA per-se, but the uptime is pretty good =).

You can also see a status dashboard with historical status here:

[https://www.google.com/appsstatus#hl=en&v=status](https://www.google.com/appsstatus#hl=en&v=status)

RSS feed link is at the bottom of that page.

(Disclaimer: I work for Google Cloud, on the Drive/Docs/Sheets side of
things).

~~~
cyberpanther
But does that cover the API and if devs break something during a release?
That's where I see the problem often. New API gets released that breaks
something but the broken thing doesn't get fixed quickly.

~~~
victorhooi
Yes, the SLA does cover the API - these fall under the main product.

The status dashboard
([https://www.google.com/appsstatus#hl=en&v=status](https://www.google.com/appsstatus#hl=en&v=status))
lists what's covered or not covered - table at the top is all covered services
(e.g. GMail, Google Drives/Docs/Sheets/Slides, Google+, Google Groups,
Hangouts, Calendar etc.)

The table at the bottom lists things that _aren 't_ covered (e.g. Blogger,
Google Voice, Google Analytics, Google Realtime API) by the SLA.

I believe you can find the list of covered/non-covered service on the support
site as well ([https://support.google.com/](https://support.google.com/)).

And what do you mean by devs break something during the release?

Do you mean if we introduce a bug in the code, and it affects the availability
of the API? Yes, that is covered as well.

You mention you've seen this before - I can't promise anything, but if you're
hitting any issues here, or roadblocks, and you're a GSuite customer (i.e. you
are paying us some kind of money, even if it's $5 a month), I can certainly
try looking into it for you - ping me your details and I can reach out.

------
rickcnagy
Having run an (internal) app that is deeply integrated with Google Sheets for
about a year, I think that Sheets is good and bad. It's _extremely_ powerful
for allowing business users to easily get access to and manipulate data
manually. Downside is that it really struggles past a few thousand rows when
you've got more than 20 or so columns. Definitely good for a quick and dirty
way to expose data to internal users, but it essentially can't scale.
Engineers need to be ready to move off of it if the system works.

~~~
startupdiscuss
If anyone from Google is reading this: please add a database (yes, like a
cloud version of MS ACCESS) in the Google Docs/Drive suite.

Has anyone looked at "Fusion Tables?"

A google search reveals that is what it is supposed to be.

~~~
bognition
They already have one its called CloudSQL
[https://cloud.google.com/sql/](https://cloud.google.com/sql/)

~~~
skj
That's not part of the drive/docs suite.

~~~
ghostly_s
Yep, this should be accessible to users. Someone needs to bridge that gap from
spreadsheets to real apps. One click to convert your Spreadsheet into a
database. Or hell, just silently migrate it when (NUMROWS + NUMCOLUMNS) >
BIGNUMBER .

~~~
tomlock
Honestly, if clippy suggested to excel users something similar, I'd be
extremely happy.

------
stephen-mw
I never published it, but I actually wrote a service that uses google sheets
to manage SSH access on your servers. Users simply submit a google form with
their SSH key, and it's automatically pulled by a daemon on each host. It
comes complete with admin/sudo access toggling.

[https://github.com/stephen-mw/gdoc_ssh_manager](https://github.com/stephen-
mw/gdoc_ssh_manager)

------
f_allwein
I think there's a bit of a gap in the software ecosystem today in that there's
no tool that lets semi-technical people like myself create simple
applications, e.g. to read and write to databases. Bit like MS Access used to
do.

Google Spreadsheets actually goes a long way (I've done some stuff using the
IF function etc. in it), but obviously has its limits. So combining it with
Python sounds interesting. Not sure if it's what I'm looking for, but I'll
check it out.

~~~
simonw
Have you seen AirTable? It's definitely the best modern alternative to Access
I've seen - easy to create tables, great web UI and a phenomenal iOS mobile
app.

[https://www.airtable.com](https://www.airtable.com)

~~~
vijayr
Is there a downloadable version of this, paid or free?

~~~
simonw
No, it's entirely cloud based. You can install their iOS and Android apps but
they'll only work if you have an internet connection.

------
agildehaus
Just because you can doesn't mean you should.

You're putting the entire Internet between you and your "database". You'll
receive all the latency and general reliability problems that go with doing
that.

In the v3 API, the first blank row terminated the column data set. Which meant
you couldn't access rows after that blank row, which may have been
accidentally inserted by a user. They may have addressed this in v4, not sure.

You have almost no ability to restrict the values the user enters. They can be
literally any string and your app has to handle every possibility. Restricting
type on a Sheet is not really possible.

What if a user is in the middle of editing the spreadsheet when your app
attempts to access it? Since the sheet is constantly saving itself, and the
user may not have completed their edits, are you getting incomplete or
inaccurate data?

------
kleebeesh
It's worth mentioning this service that turns Google Sheets into a RESTful API
for you: [https://sheetsu.com/](https://sheetsu.com/)

Also sheetrock, which is JS-specific:
[http://chriszarate.github.io/sheetrock/](http://chriszarate.github.io/sheetrock/)

I'm not the creator and have only used these for trivial tinkering.

------
xelathebela
Potentially, putting the python function into a AWS Lambda function (or any
other serverless function) + attaching an API gateway, you could make a
RESTful endpoint for all the CRUD operators for google sheets. Could be a
extremely light weight way of storing data and exposing it through REST :)

------
stpe
In a project I worked on the implementation of a very complex form (insurance
company) with lots of conditions. Given the number of stakeholders and people
involved having a say (legal, product owner, sales people, customer care,
copywriter, etc) normal iteration (gathering feedback => implement => deploy
=> repeat) would simply take too long.

I very successfully used Google Spreadheets as a backend for this. Putting all
text and the conditional logic in the spreadsheet, and allowing the form to be
built as a web page based on the Google Spreadsheet data for instant preview.
This allowed the different members in the project to even work in parallel
(thanks to collaboration features) with stakeholders - and implementing the
feedback immediately themselves DURING the meeting - to see if the results was
what they expected.

I wrote a small blog post about this, unfortunately in Swedish, but hey -
there's a video at least :) [http://www.rebelandbird.com/hyperiterativ-
prototypning-med-g...](http://www.rebelandbird.com/hyperiterativ-prototypning-
med-google-spreadsheets/)

Another powerful way I use personally is to use Google Spreadsheet as a data
backend for Jekyll based static websites. Here is a Grunt plugin I did to deal
with this [https://github.com/stpe/grunt-gss-to-
json](https://github.com/stpe/grunt-gss-to-json) \- example usage; my retro
games collection [http://games.stpe.se/](http://games.stpe.se/)

------
jonknee
Google Forms got a big re-do in the fairly recent past and it's an excellent
product. My partner uses it a lot and she calls the results her "apps" becaues
they are saved to her homescreen on iOS. She has some pretty sophisticated
forms going because there's a "Go to section based on answer" feature that
lets you build conditions.

If you haven't checked it out in a while it's worth a shot.

------
classybull
I'm going to have to be a contrarian on this one.

On my team, we absolutely loathe Google Spreadsheets with a passion. Not
necessarily the product itself. It actually is a truly capable and nice
spreadsheet program. However, it encourages some extremely bad uses.

Because of the low friction of creating a spreadsheet versus, say, a database,
business users have started using it as a substitute for something that should
probably live in a database. For instance, I'm thinking of a particular
portion of our sales organization that kept their business hierarchy in a
Sheet, ie x person is on y team. Now, when you want to do analysis on the data
you now have to use something similar to this library to retrieve the data and
return it in a dataframe, which then pipes into the rest of your analysis and
you end up with a finished product, be it a dashboard or some job.

What always happens is that you write your analysis, set it up to run
regularly, and all of the sudden, three weeks later, you start receiving
exception emails on the script. When you investigate it, inevitably some
jackass business user upstream altered the "schema" of the document, breaking
your downstream analysis.

In addition, once you have hundreds or thousands of these scripts running
repeatedly, all reaching out to the Google API and in some cases retrieving
100s of MBs of data, Google very quickly rate limits you and you end up with
dozens of scripts that broke.

Sheets is great for analysis and one offs, but you have to push back on your
business users who will constantly try to use it as an operations platform
because they don't know any better.

\- "I need to see an analysis of xyz data."

\- "Ok, where does the data live?"

\- "Well, all of the facts come from this SQL table, and we pull from this web
service, and finally we run it through this lookup table we maintain in Google
Sheets."

\- "Nope. Come back to me when you put that data somewhere else. Spreadsheets
are not databases."

------
eob
Cloudstitch founder here (YC S15).

We wrap Google Sheets and Excel 365 in an API you can use to GET/PUT data
along with a number of other goodies:

    
    
       - security policies (e.g., row-based auth)
       - file upload support
       - email triggers upon upload
       - a notion of "frozen" releases versus live dev data
         (released but yet undocumented -- email hello@ for details)
    

Very happy to provide support for anyone looking to use us as an API into
their sheets.

------
ecesena
I build HasGluten [1], fully backed by a spreadsheet. Code is at [2],
unfortunately not very up to date... it was my first react project.

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

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

~~~
marcc
You have a small typo in the link to your site. [1] should be
[http://hasgluten.com](http://hasgluten.com)

------
mixmastamyk
Interesting to choose this over simpler storage, such as sqlite. I suppose you
get offsite backups for free.

~~~
gregorymichael
(author here) Main advantage in our usecase is getting the CUD interface for
free.

~~~
jwmoraes
For free ? Where would the python code run ?

~~~
andrewguenther
He said just the CUD part (Create Update Delete). The assumption is that your
data can be entered manually through the Sheets UI and your code only needs to
do the Read part.

~~~
mixmastamyk
How would the django/flask admin app compare?

------
vgt
Google Sheets fully integrates with Google BigQuery as well! [0]

You can create a BigQuery table that's powered by a Google Sheet, or export
data straight to Sheets for quick and dirty data wrangling. Some folks use
Google Forms or Google Sheets to update ledgers, SKUs, or what have you, with
data flowing straight to BigQuery for analysis against clickstream data,
server logs, Google Analytics data, weather, etc.

Love AppScript - so many possibilities for a "serverless" framework. How about
ODBC?

[0] [https://cloud.google.com/blog/big-data/2016/05/bigquery-
inte...](https://cloud.google.com/blog/big-data/2016/05/bigquery-integrates-
with-google-drive)

(worked on BigQuery, work in Google Cloud)

------
szopa
Huh. I bet that in the Docs SRE team at Google there's a senior SRE who's
having a "I felt a great disturbance in the Force moment." Google Sheets is
awesome, people at Google are smart, but they are not capable of magic.

In the case of Sheets, the most reasonable sharding scheme for the database
backing it would be based on some collection of spreadsheets (in the extreme
case it would be just one spreadsheet, but that probably wouldn't be
practical). The important assumption here is that all the traffic pertaining
to one spreadsheet will ultimately go to one server. So, if there's too much
traffic coming related to _one_ spreadsheet, you cannot really scale
horizontally by adding more servers – you have to give it more resources. A
single Google Sheet is sometimes used by lots of users at the same time, but
they are able to limit it from the frontend (for example by making it degrade
gracefully).

If the traffic comes from the API, it's going to be a little bit more tricky –
especially that IIRC the current rate limiting for the API uses a daily quota,
leaving room for some really nice spikes.

So, as soon as a website built using this hack get somehow popular someone is
going to have a really rotten day with a lot of pages.

(Of course, this is pure speculation on my side, I have no idea how Google
Sheets is _actually_ built – I would be very curious if there's a smart way of
overcoming this sort of issue.)

~~~
nl
One of the good things to come out of Google Wave was a better understanding
of how to scale using (eg) operational transform. Even without that, scaling a
mostly read only spreadsheet backend isn't as hard as you are making out.

~~~
szopa
Well, a spreadsheet used as intended is mostly read-only, that's no
necessarily true if it's backing a website :)

~~~
nl
Based on the read-write characteristics of every website I've ever built I'd
say "mostly read-only" is a pretty good characterization.

Even things like forums have many, many page loads than new posts.

------
rob-olmos
Like another commenter said, the built-in Apps Script is pretty powerful as it
is that it could probably handle requirements more easily and portably.

For example, I've created a simple Apps Script for a Google Spreadsheets that
uses OAuth to sync the list of client accounts from QuickBooks Online allowing
us to keep track of more structured notes and segmentation, which QBO is still
sorely lacking. The sync allows someone to easily update the spreadsheet with
any new clients not in the spreadsheet.

However, if I was going to be doing any in-depth calculations and/or a lot of
data then I'd probably go the same route with using Python externally.

Forgot to mention: The only thing I'm wary of, and have seen other comments in
other threads about, is using it for anything more crucial like a core
Excel/Access app because of the potential future change that'll break things.

------
dy
I went pretty far down the path of trying to build applications with Google
Spreadsheets and Apps Scripts (we still have some major business processes
running on it). It's definitely made me feel the need for something between
Spreadsheets and custom development or Salesforce - something like what I
imagine MS Access solved in the earlier days.

Here's what it'd need:

\- Spreadsheet like UI \- Validations and field data types (major liability of
spreadsheets) \- Good forms integration \- Custom code like Apps Script
capability \- Something around workflow and business logic

I think Airtable gets pretty close but I haven't been willing to switch cause
of some limitations but I do like where they're going. I'm considering pulling
the trigger for Salesforce but keep hearing that it's really easy to shoot
yourself in the foot.

~~~
boulos
Depending on what you're doing, App Maker
([https://developers.google.com/appmaker/](https://developers.google.com/appmaker/))
might be what you're after.

Disclosure: I work on Google Cloud, but not App Maker.

------
rohanprabhu
Back at my previous company, which was a travel startup, there was a list of
curated vacation packages that were created for every weekend. So, the
itineraries team used to spend 5 days designing the packages and getting
vendors on board, and then they used to fill up a google sheet with the
details in it and the devs used to run a script, which used to populate this
rows in the database and make any other updates required and it used to show
up on the website. The reasons why this worked so well for us:

1\. There were at the most 30-40 rows. Google Spreadsheets works amazing for
small table sizes (although not as small as 30-40, I'd assume upto 1500-5000
should be fine as well).

2\. All non-dev teams were EXTREMELY comfortable with Google Spreadsheets. For
people with non-engineering backgrounds, a spreadsheet is an amazing, low-
barrier entry to structured data which I believe is what made this solution
amazing.

3\. The dev team was completely removed as a dependency, and we had staging
environments where they would run it first to ensure it was working properly,
so a dev could be engaged only if their spreadsheet run wasn't working as
expected or due to some other issue. The previous method was a huge email sent
out to the devs, who would handcraft it into a JSON, which would then be
passed to a script and then written to the database. This required every run
to be effectively final.. there were only so many times one could engage a
dev.

4\. While we had to engage a dev to execute the script, it actually is very
easy to integrate a menu option within the google sheets interface itself (if
you are using Google Apps) for your domain, which would say something like
'Deploy to Staging', although we never got around to actually building this.

For startups where product turnaround time is required to be short, this works
as an amazing solution as it makes so easy for non-tech guys to input data
into the system. While there is no doubt that a fully developed panel for
these operations would be the best solution, one doesn't always have the
luxury of time.

------
maciekmm
I wrote about it a year ago as well: [https://mionskowski.pl/using-google-
apps-scripts-for-a-simpl...](https://mionskowski.pl/using-google-apps-scripts-
for-a-simple-backend)

------
tjdalaska
I am interested in the opposite use case where Google Spreadsheets/Google Apps
Script calls an existing python script... possibly using Google Compute
Engine? Anyone have any experience and/or suggestions?
[https://developers.google.com/apps-
script/articles/appengine...](https://developers.google.com/apps-
script/articles/appengine#section4) is the closest thing I have found
searching myself but it states that it is no longer maintained.

------
adam-p
I used Google Sheets as the DB backend for a membership management site for my
local neighbourhood organization. So far it has worked great (for few users,
but that was part of the spec).

Here's the project, and there's more rationale/investigation in the README:
[https://github.com/adam-p/danforth-east](https://github.com/adam-p/danforth-
east)

(And... it looks like I need to update from Sheets API v3 to v4.)

------
hackdroid
With google spreadsheet appscript, i've built a dashboard with bootstrap,
implemented a database with dopost and doget, downloaded 8000+ webpages for
data scraping overnight. You can overcome the 5 minutes limit by using this
[http://patt0.blogspot.in/2014/08/continuous-batch-library-
up...](http://patt0.blogspot.in/2014/08/continuous-batch-library-update-
for.html)

------
eldavido
I'm using Python and Google Sheets to manage my wedding invitation list. Lob
for cards, google contacts for contact data, gsheets is the main db. It works
well.

------
dpweb
Did a NodeJS read only Sheets recently no auth needed. Gets array of objects,
way simple.

[https://runkit.com/58a77f7a86c06300158dfcf5/58a77f7a86c06300...](https://runkit.com/58a77f7a86c06300158dfcf5/58a77f7a86c06300158dfcf6)
[https://github.com/digplan/gsheet-web](https://github.com/digplan/gsheet-web)

------
kawsper
I would love if I could use Ruby in Excel to program styling and transform
cells, I wonder why I haven't seen anything like that.

~~~
mistermann
There's something about Excel, I think there are certain aspects of it
(programmability, object model) that I think Microsoft is just never going to
improve - not that they lack manpower, or that users don't desperately want
improvements, it feels more like a matter of principle.

My best guess is that they don't want it to be _too_ good lest it cannibalize
their other product lines; I would say the very exact thing was the downfall
of MS Access.

Of course this sounds like a silly conspiracy theory, but if you're very
familiar with Excel, and especially the pile of smoldering crap that is VBA,
I'd be surprised if you don't see at least a glimmer of truth.

That google now has a modern, capable language that can be used with google
sheets, perhaps now MS will actually wake up and begrudgingly give their
faithful users something they've been asking for, for literally over a decade.

------
gmisra
I'm also a big fan of this approach, especially for sharing data
responsibilities with non-technical colleagues. If you work in nodejs, the
node-google-spreadsheet library is pretty good:
[https://www.npmjs.com/package/google-
spreadsheet](https://www.npmjs.com/package/google-spreadsheet)

~~~
alexjv89
thanks.. this is useful.. tried it out and perfectly matches my needs.

------
ristic
I have built something similar that works with LibreOffice Calc as the backend
instead of Google Sheets:

[https://github.com/robsco-git/spreadsheet_server](https://github.com/robsco-
git/spreadsheet_server)

------
Keverw
I remember seeing people in Second Life talking about using Google
Spreadsheets as database using LSL so people didn't have to run external
databases. Pretty interesting stuff even though I don't think it's really
meant for this use case.

------
nithinm
For a port of gspread which uses API v4 - supports new features like formats,
notes etc. Checkout
[https://github.com/nithinmurali/pygsheets](https://github.com/nithinmurali/pygsheets)

~~~
relinqmost
how long the maintenance of this library will last after gspread moves to v4?

------
Entangled
Exactly for cases like this I came up with DataText, a simple text format for
tables with an editor just like an excel sheet.

[https://github.com/kuyawa/DataText](https://github.com/kuyawa/DataText)

------
samwillis
We use google spreadsheets and gspead/Python for analysis of our AdWords. The
AdWords api is a PITA and so we just have a sceduled export to a google
spreadsheet that we can then easily access. Works very well!

~~~
boulos
Have you tried exporting to BigQuery
([https://developers.google.com/adwords/scripts/docs/solutions...](https://developers.google.com/adwords/scripts/docs/solutions/bigquery-
exporter)) instead? Depending on your analysis (and analysts!) it's probably
easier to write SQL. As a reminder, there's also a generous free-tier for
BigQuery!

Disclosure: I work on Google Cloud.

------
sasaram
Theres one easy to use google spreadsheet api here built on gspread
[https://github.com/shkr/GoogleDocsAPI](https://github.com/shkr/GoogleDocsAPI)

------
NicoJuicy
I have a CMS where the client adjusts their website in Google Spreadsheets.

Easy and simple. Multiple auths and roles and it's cached, so it's not queried
every minute ( pressing a link clears the cache)

~~~
foxhound6
I use something similar, but not quite as involved to let a restaurant update
their daily specials on their website.

------
SnowingXIV
The problem with google sheets is row limits. Excel has them too but it's
more. Eventually only manageable way to deal with larger datasets is in SQL
and managing CSVs with bash to split.

------
tonylucas
Worth also looking at Airtable here as well, becoming a big fan of it.

------
bredren
I did a major integration with Google sheets API recently and found the
documentation sorely lacking. Docs and Sheets could be so much better.

------
z3t4
you could also store the data in a json file an let a static web app consume
it

------
zachruss92
This is super cool. I will probably use this in the future when sketching out
ideas!

------
untilHellbanned
Cool but it's a non-starter if you go beyond a few thousand rows.

------
migoden
nice

------
johnsmith21006
Our school district has moved to Google sheets for kids grades and I am blown
away how well it fits with this use case.

