Forced migration to Google Sheets API v4
gmass.co
gmass.co
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.
Or maybe some shared secret challenge/response to get a time limited ticket?
It won't get exposed like a URL parameter and doesn't misuse the body/POST.
There is very probably a way to (ab)use HTTP Auth for this such that it's still possible to test GETs with simple URL strings or links in a browser.
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.
OAuth works just fine for machine-to-machine too, see f.ex https://developers.google.com/identity/protocols/OAuth2Servi...
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.
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...
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.
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.
However, Office 365 is the real threat and moves like this are not helping...
Google is the new ‘00s-Microsoft, they just don’t care.
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.
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.
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?
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.
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
Nothing is free to maintain.
Not only with software, with life. Everyone should consider this when evaluating anything in their life.
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.)
2. I build on FooCorp anyway because it's free and easy and I can't think of any other way to build an MVP.
3. I never bother to upgrade my MVP to a stable platform or add a fallback system.
4. Free easy system is killed.
5. Whine on Medium/Twitter about how I'll never trust FooCorp.
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.
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.
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.)
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.
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.
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.
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.
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.
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.
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).
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.
Guess I have a job to do now. :(
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