Forced migration to Google Sheets API v4

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.

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:

to this:

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.

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.

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 officialannouncement from the Sheets API Team.

The official migration guide.

Mylive update page on the exhausting OAuth verification process.