
Forced migration to Google Sheets API v4 - danso
https://www.gmass.co/blog/google-sheets-api-v4-bullshit/
======
davedx
One of the things inspiring me to start work on LightSheets was when I tried
to build an integration with Google Sheets API a couple of months ago and
quickly ended up down an OAuth rabbit hole for my non-interactive script.

For building integrations, OAuth is nice for enhanced security for end users,
but makes it harder for developers to build integrations. What I'm going to do
with LightSheets is begin with simple per-spreadsheet token based
authorization, so if you own a spreadsheet and want to build an integration
it's very easy to do stuff with a single request per query. (I'll also
implement a simple way to refresh the token in case it gets breached). Later
on I will consider adding OAuth _IF_ there's a use case for it.

Interested in feedback from others about whether this is a good idea or not.

~~~
janpot
> I'll also implement a simple way to refresh the token in case it gets
> breached

I'd advise to implemented this in a way that you can have at least 2 tokens
valid at any given time. So that you give your users the chance to rotate them
without downtime.

~~~
dugmartin
Or N tokens with a note field so that if you have multiple integrations you
can assign one to each or share them - much nicer when you want to invoke
access or roll tokens.

------
mikeappell
It's funny: we were required to do a Google Sheets integration recently where
we had to get a list of all the user's sheets as a part of the flow, and I
didn't even realize there was a way to do so using a non-drive endpoint.

This is because I was apparently only perusing the v4 api docs, which I'm glad
of since it would mean had I done this using the more appropriate v3 /feeds
endpoint I'd have a _really_ ugly migration ahead of me.

We've had users break their experience by refusing the 'drive' scope of access
because it's so permissive, and security-conscious users balk at it before we
explain that we're only using it to get a list of all the user's sheets rather
than download all their files. And even then, some still choose to not use
this feature. It's unfortunate, but as the api stands there's no way around
it.

Fyi, if you want to query a list of only the user's sheets files, the
following endpoint with this specific query param works:
[https://www.googleapis.com/drive/v3/files?q=mimeType='applic...](https://www.googleapis.com/drive/v3/files?q=mimeType='application/vnd.google-
apps.spreadsheet')

------
semireg
This situation is obviously a casualty of desktop computer security
abstractions (e.g. Apple’s sandboxing) leaking into web/oauth to the point of
utter user confusion. The picker/chooser idea is interesting because it’s also
mimicking how desktop sandboxing gives apps the ability to read any one file
at the users discretion.

My electron app supports importing XLS/CSV (bound by limitations of
sandboxing) and have had users request Google Sheets support. A few weeks ago
I built up a Sheets POC and I too was surprised with the v4 drive
requirements. I agree with op, it seems silly to request full read permission
when all I need is a list of metadata to integrate with the Sheets API. I’m
trying to think ... does any desktop have a file API that has the ability to
return only files of a certain kind (perhaps that app’s registered extension)
while presenting the user with a dialog that makes sense for that context?
It’s an interesting idea.

~~~
philprx
If I was Google, and I would be doing this, it would be not based on security
requirement but rather:

CYA: so that users can't sue Google in case of wrongful usage of data by a bad
app

Uniformization / homogenization of permissions names.

Reduction on the CPU load with removal of costly and non billable processing
(eg structured queries).

Ps: I've been a security guy for 30+ years.

~~~
tlogan
If there is no threat from Office 365 then yes.

However, Office 365 is the real threat and moves like this are not helping...

------
cprecioso
I am not claiming that this would be a perfect solution, but Google Sheets
(and Docs, Slides, Forms...) allow for [Apps
Scripts]([https://developers.google.com/apps-
script/](https://developers.google.com/apps-script/)).

Once you get the hang of it, and use tools like
[clasp]([https://github.com/google/clasp](https://github.com/google/clasp)),
you can create semi-complicated automated workflows; which I still find
delightful.

For one of my jobs, I created a form, that either on submit or on demand,
creates a PDF with the data based off a template made with Slides, and emails
it to me and to the person who filled it.

The code is as concise as the API allows, that is, not much. But it's saved us
the need to have a manual workflow, or setup a server just for this service.

------
Eikon
I absolutely understand the frustration of the author. Unfortunately, this is
what you get by making your product depend too heavily on a external product
you have no control on.

It's a huge risk to take and may end up pretty poorly if your goals starts to
missalign with the direction the product you depends on (I'm not speaking
specifically for this case, I don't know enough about their product).

Think about Github actions and the plethora of companies offering CI through
their marketplace. The out-coming was obvious.

I would argue that even if business models like that are pretty cool initially
as you benefit from a company community and exposure, it starts to fall apart
quickly as:

\- If your product is successful, the company you depend on is going to start
thinking about getting their share of the pie.

\- Your options to diverge from what the underlying platform is allowing are
very limited.

When your product is an outsourced feature as a service for someone else's
business, you're doomed.

~~~
tomrod
Your second part seems like an ethical justification for taxation,
incidentally. Should the producer not pay more for success because the
platform they build on got them there?

This being said, what was really wrong with V3? Is this simply someone's
career push ("newer is better, hello promotion!") and is there a su bstantive
improvement in quality or security?

~~~
throw_m239339
> Your second part seems like an ethical justification for taxation,
> incidentally. Should the producer not pay more for success because the
> platform they build on got them there?

By "taxation" you mean wealth redistribution from the wealthy to the
state/public right?

It has absolutely nothing to do with "taxation". Initially the reason why
Google, Facebook, Twitter and co offered a lot of free API was just growth and
user acquisition. Now that they have a significant audience, they don't need
third parties to make more money anymore, so no free lunch.

Ultimately these companies won't be much different from Oracle or IBM on the
long run.

~~~
tomrod
Right. They offer a platform for the business to connect with consumers,
operating a two-sided market.

Sorry, I didn't intend for this to be a political statement, I had a
conversation with a right-libertarian friend on the ethics of taxation (I see
a need for it) that is still buzzing around in my mind.

By taxation I don't mean wealth redistribution from the wealthy to the state
(and certainly not the public, as most of us in the non-wealthy segments pay
and don't observe benefit outside of defense and, when not tolled, roads), but
rather charge for platform access to citizen rights.

The discussion reminded me of a quote from the US President Obama a few years
ago: "If you've got a business—you didn't build that"[0]. His point was that
we all build these amazing marvels of technology and modernity on platforms
other people provide, be it the state, other companies, and so forth. And
because we build these successes on platforms, the platform needs to be funded
-- be that taxes, charging, etc.

The crux of Google's issue is that they change their platforms without much
warning, without much similarity, and often without prior version support.

Notes

[0]
[https://en.wikipedia.org/wiki/You_didn%27t_build_that](https://en.wikipedia.org/wiki/You_didn%27t_build_that)

------
draklor40
Google products are a clusterfuck. I see a great future for a Google
replacement (from apps, suites, to search) :)

~~~
michalu
Yes, but they are free ... I personally can't see the usability being painful
enough to create a space for paid service. I'm heavy user of both Drive, Gmail
for business and APIs ... few glitches here and there but still loving it.

~~~
draklor40
As a user yes. As a developer I would really think twice before building
something on top of google. God knows how many products were destroyed because
some engineer inside Gborg thought killing an API feature would help him
advance his career (source: Friends within Gborg).

~~~
Bartweiss
Normally this sort of thing is a mismatch between user preferences (easy,
free), and developer goals (stable, accessible). But the Google Graveyard is
getting so big that it might actually discourage users.

First, Google has moved beyond killing third party apps 'terminal' apps like
Plus to killing support apps like Cloud Print. That alone means that as a user
I'm scared to rely on integration-level apps.

Second, the rate of turnover is so high that it actually causes me usability
problems pretty often. Everyone hates Microsoft Office redesigns, but they're
infrequent enough to get used to. Between dead apps, changing permissions and
features, and _constantly_ being thrown in and out A/B tests, I basically
never feel at ease with Google products. If I have to look for instructions on
how to do something, they're almost always unusably out of date.

(Storytime: the incredibly useful "arrive/depart at" feature in Maps vanished
from my mobile app for a while. I got into a discussion about it with another
programmer who missed the feature and a Google engineer who thought we were
just looking in the wrong place. It quickly became clear that fragmentation or
long-term A/B testing meant each of us had _vastly_ different options and
interfaces.)

Third, killing third party ecosystems catches up in the long run. Normally
that comes when companies lock down services, but here it might come from
uncertainty. if developers shy away from Google products and I can't trust
trust Google-official services to say alive, how long before I look for more
robust options? (Answer: I already am, increasingly often.)

~~~
enzanki_ars
In terms of long term A/B testing, companies really need to start
understanding how harmful and confusing it can get for their user base. As
much as I absolutely hate Facebook, I still have to use it for certain groups.
Within the 4 people in my immediate family that use the app, we all had vastly
different interfaces for the longest time and could never help each other
navigate and find some specific option or feature.

------
todotask
There is one concern with Go tutorials for v4 are still mostly missing from
the documentation, it took me days to find the right code in StackOverflow and
got it work.

~~~
52-6F-62
Oh not just Go!

I just finished integration in a service (NodeJS) that makes occasional calls
to a shared Sheets doc in my employer's GSuite drives.

It was damned near impossible to understand from Google's documentation how to
actually implement using a Service Account. Every angle I looked at for using
a Service Account had me run into "here's how to use OAuth" which required
user interaction—not at all what I wanted.

I came to a similar result—except it took me digging through relevant Github
repos to find something close enough. And same—it took me days.

~~~
iudqnolq
I threw together a simple Sheets V4 integration in Node from the docs a few
days ago and it really wasn't that bad. Only pain point was that I tried on a
whim omitting the callback, and learned that that gives you a promise.
Presumably documented somewhere, but I didn't RTF the whole M. But I avoided
OAuth and instead created a service account and then required that the user
share the doc with the service account email.

------
mikelward
> See, edit, create, and delete your spreadsheets in Google Drive

Versus

> See and download all your Google Drive files

Personally I find the edit and delete permissions from the old dialog just as
scary.

But OAuth prompts always seem to ask for more than you want to give.

~~~
Bartweiss
I agree that the new permission doesn't seem worse as much as different. I
already think of Sheets/Docs/etc as basically being the filetype UIs over a
core of Drive. This change shrinks "see, edit, and delete" down to "see", at
the price of broadening to "all filetypes"

But I can definitely see issues with the _change_. Most obviously, forcing
users to re-grant causes alarm: they don't know about the version update so it
looks like the app is trying to behave differently. And even if an equal
percentage of users would grant each permission, they may not be the same
users with the same use cases; your existing users are all people who agreed
to the old version and may not continue to agree. Beyond that, "edit and
delete" is a major permission, but not necessarily that dramatic for an app
with other powerful permissions. Whereas it seems very possible someone who
trusts Drive might have boring app-friendly spreadsheets, alongside much more
private text documents or photos.

(And yeah, across the board OAuth seems to demand way more power than apps
actually want to use.)

------
tyingq
Is Google like this internally as well? If you own an API that's a dependency
for other teams, how politically difficult is it to hard-deprecate an
interface?

~~~
brainwad
Yes, it is. There is a comic that is internally shared that describes it well.
A Googler comes to a fork in the road. On the left, there is a road that is
worn out, with a sign saying "deprecated, don't even think about it"; on the
right there is a brand new road still being constructed with a sign "under
construction, danger!".

~~~
winrid
I've noticed this with browser related apis lately. Things being "deprecated"
with no replacement.

------
tehwebguy
Been really happy with AirTable lately. Row limits may be an extreme
dealbreaker for many.

------
danso
Not sure if the blog is keeling under traffic, but fwiw, here’s the
levelsio/nomadlist tweet thread that I saw it from:
[https://twitter.com/levelsio/status/1217333898985607168?s=21](https://twitter.com/levelsio/status/1217333898985607168?s=21)

------
alooPotato
Is there a reason you need access to all the users spreadsheets? This seems to
be the correct behaviour, a scary warning if you want access to all my drive
files. Imo, it wouldn't be any less scary to request access to just
spreadsheets. You might consider using the Drive Picker if your use case was
to let a user pick one of their spreadsheets for your tool:
[https://developers.google.com/drive/api/v3/picker](https://developers.google.com/drive/api/v3/picker).
Google handles the picking so you dont need all files access.

I started Streak (www.streak.com) and to be fair we are guilty of this as
well. We ask for all files access to "future" proof what features we want to
offer. I think we should change this and just ask for file specific scopes.

------
mikece
Does Office 365 have an API that does what Google Sheets v3 did? Google is
legendary for killing products -- even beloved and successful ones: RIP,
Reader -- while Microsoft has OCD about supporting APIs that are 30 years old.

I had been looking at using APIs and automation with G-Suite but was always a
little nervous to tie my apps to the whims of Google. This is another example
of why I shouldn't entertain that thought and use "old, boring technology"
like offline spreadsheets (like Libre Office docs) or Office 365 instead.

~~~
tyingq
I looked into the a while ago. There are some other options but the only ones
that appear to be fairly robust are manipulation from either Microsoft Flow or
Powerapps. Both of which are sort of "citizen development" visual type
environments.

There is a second choice that works more like the Google sheets API...the
Microsoft Graph API: [https://docs.microsoft.com/en-
us/graph/api/resources/excel](https://docs.microsoft.com/en-
us/graph/api/resources/excel)

When I last tried it, it was somewhat limited in functionality.

~~~
djsweet
Heads up on the Microsoft Graph API: it only works for certain styles of
Microsoft Office customer. In particular, you can only authenticate against it
with Azure AD, which completely chokes out the home market.

~~~
nu5500
That is not true. There are certain APIs only applicable to commercial users,
but the same Graph API calls (for example against OneDrive or Excel) can be
used across both.

~~~
tyingq
Is that new? The post I linked to, from 9/2019, says _" Support for workbooks
stored in OneDrive Consumer platform is still not available. At this time,
only the files stored in business platform is supported by Excel REST APIs."_

------
marsRoverDev
I guess the biggest takeaway here is caveat emptor. Google famously does not
support their services, and wields so much power that you are pretty much SoL
on this one.

I have sympathy for your situation - but next time, remember this and consider
whether supporting their platforms is a good business choice. I don't believe
a provider of a free API should be contractually or morally obliged to
maintain it into perpetuity.

------
bachmeier
Developers learn the importance of software freedom over and over.

~~~
tzs
What does this have to do with software freedom? If every bit of the Google
software in question was under GPLv3 I fail to see how the outcome would have
been different.

~~~
bachmeier
Software freedom is about more than the license. You give up your freedom when
you depend on someone else's cloud applications.

In bold, at the start of the post, is "not all functionality available in v3
is available in v4". That by definition cannot happen with free software.
Whether there is a practical business case is a different question (whether or
not it is profitable to build a business on top of Google Sheets is distinct
from the question of which freedoms you give up in order to do so).

------
anderspitman
Here's hoping we'll choose open protocols for the next 10 years.

------
iagovar
Do not count on google products. Do not count on google products.

I lost count on how many times this has been said in HN. Just yesterday I just
wondered if I used google sheets to upload some data and wrangle it through
API. Then I remembered what many wise people told me on HN: Do not depend on
google products. Then, despite my almost non-existent sysadmin skills I
installed Postgre and I'm happy since.

~~~
zamadatix
In general minimize 3rd party dependencies you have no control over unless
you're willing to incur either the cost/overhead to migrate from them at any
time or the risk that they'll disappear. Google is just the popular choice
because they have a lot of popular services.

------
W4ldi
Don't use Spreadsheets as databases?

~~~
tyingq
The GMASS product appears to use it for email merge. Customers doing that
expect a spreadsheet integration.

------
s_y_n_t_a_x
Another lesson not to use Google.

------
josteink
Oh. Google is making some of the APIs we rely on "restricted". Good job Google
telling us about it, right? Oh wait.

Guess I have a job to do now. :(

~~~
ricktdotorg
to be fair, they did launch the new API on June 15th 2016. that was over 3.5
years ago. so it's not like they suddenly sprang this on on anyone. a
migration path COULD have started in 2016 to interested parties.

i use the v4 API to create/populate automatically updated spreadsheets using
Google's Go libs, and as far as spreadsheet APIs go, it's fine. as TFA
described in detail, v4 is not a DB API. it sounds like they need to use
Datastore.

/shrug

