Forced migration to Google Sheets API v4

https://www.gmass.co/blog/google-sheets-api-v4-bullshit/

by danso • 7 years ago

162 62 7 years ago

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.

danso• 7 years ago

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

Eikon• 7 years ago

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.

tomrod2• 7 years ago

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• 7 years ago

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.

ailideex• 7 years ago

This being said, what was really wrong with V3?

Nothing is free to maintain.

qrbLPHiKpiux• 7 years ago

making your product depend too heavily on a external product you have no control on.

Not only with software, with life. Everyone should consider this when evaluating anything in their life.

iagovar• 7 years ago

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• 7 years ago

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• 7 years ago

Don't use Spreadsheets as databases?

tyingq5• 7 years ago

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

themoat• 7 years ago

Who is this comment directed to? The author of the article, or their customers?

s_y_n_t_a_x• 7 years ago

Another lesson not to use Google.

mikece• 7 years ago

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.

tyingq5• 7 years ago

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

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

djsweet• 7 years ago

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.

toyg2• 7 years ago

Microsoft has OCD about supporting APIs that are 30 years old

That was the old Microsoft, for all its faults. The new Microsoft is much more Google-like: products are chucked out and then killed shortly after if they get no traction. They deprecate and remove services with ruthless regularity even when they are considered first-rate - like Lync/SkypeForBusiness.

ak217• 7 years ago

Last time I checked, Microsoft had nothing like the Sheets API. However, since then, as the sister comment points out, they have published the Graph API (https://docs.microsoft.com/en-us/graph/api/overview?toc=./re...), which seems to have a pretty decent set of spreadsheet API methods.

Google's spreadsheet API is very powerful, and contrary to the spirit of the OP, I think the update largely preserves existing functionality (the developer may need to adjust their thinking and be aware of other aspects/patterns of Google APIs). I'm actually very glad they came out with v4, as during the lifetime of v3 it was not entirely clear whether they would be updating it further.

Offline spreadsheets are completely unusable for most of the use cases that these APIs enable. The biggest value add of the online spreadsheet APIs is to be able to integrate with relevant online data sources to dynamically update the spreadsheet contents, provide typeahead suggestions/autocompletions, or sync data with another system. This can be very powerful because it immediately brings the full power of spreadsheets to the user interface of your data system, without having to build something equivalent to a spreadsheet interface yourself (which, contrary to some loud opinions out there, can be a huge win in business applications by enabling non-technical or partially technical users to be more flexible, more productive, and less dependent on other members of the organization to do their work).

Until the graph API, the closest thing to a spreadsheet API provided by a Google competitor was... VBA. Visual Basic for Applications. That is still the main API for desktop Office applications. Good luck using that. I just about gouged my eyes out the last time I tried to use it.

semireg• 7 years ago

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• 7 years ago

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• 7 years ago

If there is no threat from Office 365 then yes.

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

toyg2• 7 years ago

Nothing to do with abstractions, everything to do with “streamlining” the Google backend to save on maintenance and running costs (which will instead accrue on the integrators’ side). If, by doing so, they also lose a bunch of minor integrations who cannot afford the new burden, further freeing resources, it’s even better.

Google is the new ‘00s-Microsoft, they just don’t care.

tyingq5• 7 years ago

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• 7 years ago

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• 7 years ago

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

josteink• 7 years ago

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• 7 years ago

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

davedx3• 7 years ago

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.

tyingq5• 7 years ago

Maybe consider a configurable option for the token to only work as a POST payload, versus a url parameter. When they get passed around as urls in email, etc, they tend to get exposed pretty quickly. Breaks standard REST, but...

Or maybe some shared secret challenge/response to get a time limited ticket?

jmull• 7 years ago

I wonder if a header would be a good place for a token.

It won't get exposed like a URL parameter and doesn't misuse the body/POST.

davedx3• 7 years ago

I'll almost certainly have it only allowed in headers irregardless of the HTTP verb used. This does mean you won't be able to use your browser to test GET's, but this feature is squarely aimed at developers who will probably be using a Postman like tool to test with.

janpot• 7 years ago

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• 7 years ago

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.

davedx3• 7 years ago

Nice idea, thanks for the feedback.

magicalhippo• 7 years ago

That's just the way Google chooses to use OAuth when handling user data.

OAuth works just fine for machine-to-machine too, see f.ex https://developers.google.com/identity/protocols/OAuth2Servi...

RileyJames• 7 years ago

Good for developers, but no necessarily end users. Even if you intend this only for development purposes, or for technical users, without doubt, some developers will ship their integrations to non-technical end users with instructions to: log into the dev portal, generate a token, add the token here, upload certificate, etc, etc.

As simple as that is, it causes headaches non-technical users, which will end up in the support queue.

Hence OAuth.

But now you have an easy way, and a hard way, and the easy way is how every developer will begin their integration. You'll need to incentivize, or gatekeep, in order to have them complete their integration with OAuth.

draklor402• 7 years ago

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

minikites• 7 years ago

DuckDuckGo and Fastmail are great alternatives available now.

michalu• 7 years ago

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.

draklor402• 7 years ago

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

kerng• 7 years ago

Suites is particularly non-usable in an enterprise setting - Unless you have never used Office/O365, which by itself is not perfect.

tehwebguy• 7 years ago

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

todotask2• 7 years ago

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• 7 years ago

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.

todotask2• 7 years ago

I agreed with the experience, my previous stack was also Node on v3 which is easier with quickstart tutorial, I took a challenge to rewrite in Go for a static binary and embedded all assets.

iudqnolq• 7 years ago

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.

mikeappell• 7 years ago

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

mikelward• 7 years ago

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.

Bartweiss2• 7 years ago

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

alooPotato• 7 years ago

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

bachmeier2• 7 years ago

Developers learn the importance of software freedom over and over.

tzs• 7 years ago

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.

bachmeier2• 7 years ago

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

cprecioso• 7 years ago

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

Once you get the hang of it, and use tools like [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.

anderspitman• 7 years ago

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

Forced migration to Google Sheets API v4

Related Stories