<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=1822615684631785&amp;ev=PageView&amp;noscript=1"/>

Forced migration to Google Sheets API v4 is bullshit

Recently, Google announced that the old version 3 of the Google Sheets API will be shut down in March of 2020, forcing developers to migrate to version 4 to ensure the continuity of their applications.

Google Sheets API Announcement
The official email announcing the end of v3 of the Google Sheets API

The changes pose several issues for developers, because not all functionality available in v3 is available in v4. You wouldn’t know it from reading Google’s migration page though, which states:

“The v4 version is JSON-based, has an easier-to-use interface, and adds a substantial amount of functionality that is not possible in the v3 version.”

That may be true, but it also removes some critical functionality that developers have been relying on for years in v3.

Additionally, OAuth scope changes between v3 and v4 will also force developers to go through a nasty OAuth re-verification process.

While there may be plenty more issues than I’ll highlight here, I’ve included the issues most relevant to my work.

Gone: The ability to list all Google Sheets in a user’s account

The v3 API made this easy, providing a specific endpoint the inside the https://spreadsheets.google.com/feeds scope. The v4 API makes this harder. You must have access to either the https://www.googleapis.com/auth/drive.readonly or https://www.googleapis.com/auth/drive scopes, and the method to retrieve spreadsheets is now based on a query against all Google Drive files:

https://www.googleapis.com/drive/v3/files?q=mimeType%3D'application%2Fvnd.google-apps.spreadsheet'

You have to read the files from the user’s Google Drive, which requires the additional scope, and requires permission to read all the files on the user’s Google Drive, not just Sheets. This not only poses a greater security risk for the user, but is likely to scare the user, resulting in abandonments of the OAuth login process. The warning that a user sees goes from this:

Google OAuth Screen
The OAuth permissions screen with v3 of the Google Sheets API

to this:

The much scarier OAuth permissions screen with v4 of the Google Sheets API

Finally, as detailed in Google’s Restricted API FAQ, the “drive” and “drive.readonly” scopes are becoming restricted scopes. That means that if your Cloud Console App previously did not require verification and a security assessment, the forced migration to v4 will require this, and will necessitate an expensive security assessment.

The list of Google’s “restricted” API scopes, showing that Drive access is restricted

And if your app was previously verified and passed the security assessment, you will now have to add the “drive” scope and submit for re-verification, and that itself is not a pleasant process. As Google stated in my “approval” email notification, changing my scopes in my Cloud Console will temporarily cause users to see the “unverified app” screen while they re-verify my new scopes.

The “approval” email from Google explaining that you can’t switch scopes without needing re-verification

TL;DR: Migrating from Sheets v3 to v4 API will scare your users and force you to go through an unpleasant OAuth re-verification process.

Gone: The ability to query a Google Sheet like a database

The v3 API allowed programmers to use “structured queries” to query a Sheet and return only the matching rows of the Sheet. This allowed for the complex data filtering to happen inside the Sheets API, and saved client bandwidth since only the relevant rows would be returned by the API. The concept of “structured queries” disappears in v4, and if you want rows matching a certain criteria, the only option is to return ALL ROWS and ALL COLUMNS of a Sheet and then filter the data in code on the client’s end.

For example, let’s say your spreadsheet has 100,000 rows. But you only want the 500 or so rows where the Column called “PurchaseYear” has a Row value of “2010”. Instead of retrieving just the 500 rows you want, your code has to retrieve all 100,000 rows and then find the 500 relevant rows on its own. To illustrate the absurdity of removing this feature, imagine if you Googled the phrase “best smartphone” while researching your next phone purchase. It would be like Google giving you all 1.4 billion search results on one page, unordered.

You might thing “Wait, this is wrong. I saw a way to do this in v4.” You’re probably thinking of the DataFilter type.

But that method only allows you to filter data by cell range, not by the values of the cells, which is what’s needed to be useful and to resemble an SQL query.

TL;DR: Removing support for “structured queries” removes the ability to query a spreadsheet’s data based on cell values. Developers must now retrieve a worksheet’s entire dataset rather than just the columns and rows they need.

Summary: I’m frustrated.

These two functions are the essence of how I personally use the Google Sheets API, and I’m shocked that Google is forcing a migration to v4 without making these two functions easy. But then again, I probably shouldn’t be, because Google has had a habit of making things increasingly harder for developers recently.

I haven’t yet started the migration for GMass yet, but when I do, it’s likely I’ll find even more problems. I’ll update this post with anything else I find.

Resources

The official announcement from the Sheets API Team.

The official migration guide.

My live update page on the exhausting OAuth verification process.

10 Comments
  1. Have you looked the Google picker api?

    You can use it to select a file from Google drive and will return the Id of that file. No need to scan the drive or ask for permission

    1. Yes, you are indeed correct. I was just recently turned on to the idea of the File Picker API. For my purposes though, the user experience isn’t nearly as smooth that way.

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

    1. He’s just trying to filter the rows by content, which seems like a very basic spreadsheet feature. If that is not appropriate, then why the hell are we using digital spreadsheets in the first place? I’ll head out to Office Depot now and buy some paper.

    2. I’m just saying that in v3, you could use a spreadsheet like a database with Structured Queries. In v4, you can’t. But I doubt this is a strategic move to get people to stop using Sheets and start using whatever Google’s database offering is.

  3. Sorry to be a stickler, but i see this so much lately, incorrect usage of the word ‘deprecated’:

    “Recently, Google announced that the old version 3 of the Google Sheets API will be deprecated in March of 2020, forcing developers to migrate to version 4 to ensure the continuity of their applications.”

    No, its already deprecated, in march its going to be REMOVED. It is already deprecated today! These two words do not mean the same thing! I keep hearing them used interchangeably in my day job and elsewhere.

  4. Ajay, all, would love your early input on how our API should work at Spreadsheet.com.

    We’re building a spreadsheet-database hybrid that is both a traditional standards-compliant spreadsheet (XLS/XLSX) and a relational database in one. I.e. every worksheet is also a relational table that can be linked with any other worksheet, in any workbook, visually through the UI, and soon programmatically via API.

    We’re in the early stages of thinking through our API and would love input from any interested early adopters to make sure we get things like this right for the long term. For example, we’ve implemented server-side filtering of worksheet Views, which will be queryable via API and only return matching rows.

    Matt

Leave a Reply

Your email address will not be published. Required fields are marked *

Try GMass today

It only takes 30 seconds to install it!

Install Now GMass requires Chrome
Share This