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.
> 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.
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.
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.
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?
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.
> 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,
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.
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.
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.
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.
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.
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.
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?
> 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.
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.
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.
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).
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.)
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.
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.
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.
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.
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.
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.)
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?
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!".
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.
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.
>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.
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.
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.
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.
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."
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.
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.
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.
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).
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.
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.
Comments like this are similar to this comment at the bottom of the article: "TBH, using a spreadsheet like a database enables developers to use spreadsheets for databases. Which is not what spreadsheets are made for. If you need a database, pay for a database."
Basically, "shut up and stop complaining".
For a company like Comcast, sure, they are horrible and always were - so there is nothing new to share. But google used to be AWESOME and I was a huge google fanboy back when Yahoo and Altavista dominated the search space. Over time, Google got stale, then monopolistic, then careless, then evil. It's something to talk about, and central to our tech world, so I won't shut up about it! Nor will tens of thousands of others that are pissed off that Google continues to make bad decisions as an entity.
Might I suggest being the change you wish to see in the world? If you would like to see more positive stories and comments on Google you should go ahead and post some.
> So there is literally no point talking about anything positively here
Point being there is no one to argue against or defend google here in our echo chamber. Most here agree about it already, maybe tell someone in your family and try to convince them.
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.
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.