<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 if they still want the ability to list all of the user’s spreadsheets.

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

Listing all the Google Sheets in a user’s account is now complicated

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

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. As Google stated in my “approval” email notification, changing scopes in my Cloud Console and using them right away will cause users to see the “unverified app” screen.

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

In Google’s defense, however, simply adding an unverified scope to your OAuth Consent Screen does not automatically switch your users back to the Unverified App screen. Only if you request that scope in your OAuth flow code, will users see the Unverified app screen. This means that you can safely add the “drive” or “drive.readonly” scope to your Cloud Console Project, request it to be verified, and in the meantime while the OAuth Team is reviewing your request, avoid adding the scope to your OAuth flow code. Then, when the OAuth team verifies the new scope, you can finally add the scope to your flow, and that way your users will never have to see the Unverified app screen.


After you add a new scope, you’ll see it with a yellow warning triangle in your Consent Screen Settings. As long as your code doesn’t use the scope though, users will still see the Verified app screen if you’ve previously been Verified.

An Alternative Solution: The File Picker API

If you don’t want to bother with any of this complexity, you can also switch to the File Picker API, which is an API that launches a Google UI that lets your user pick a Sheet, or any file really, from Google Drive, without granting you the ability to see all the files in Drive. If your app is doing anything with Sheets, the main reason for needing to list all of a user’s Sheets is to get the Google “id” value of the Sheet. The File Picker API will retrieve the “id” of a Sheet for you, without exposing all the files to your code. It requires a different scope, https://www.googleapis.com/auth/drive.file, that is neither sensitive nor restricted. It just launches in a separate window and might break the flow of your own UI. In my Chrome extension, the user clicks a button within the Gmail UI and is then presented with a list of his Sheets.


Letting the user choose from a list of Sheets.

After the user chooses a Sheet, the list of Worksheets is then displayed. If I were to switch this to the File Picker API, then I’d first have to launch the Picker, let the user choose a Sheet, and then display a separate window to display the Worksheets inside the chosen Sheet, along with other metadata about the Worksheet. Thankfully, the File Picker API does let you filter the view to show only the user’s Sheets rather than all the files. If that wasn’t possible, it would add insult to injury, making you launch Google’s own UI, and then forcing the user to search for his Sheets.


The File Picker API lets you set it to only show a user’s Sheets.

TL;DR: The v3 API made it much easier to list a user’s Sheets and let them pick one. Migrating to v4 will scare your users and force you to go through an unpleasant OAuth re-verification process. You could switch to the File Picker API, but that won’t provide as seamless of a user experience as you’re used to.

You can’t query a Google Sheet like a database anymore

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.

The easy way to port your code

My backend is .NET, and using the .NET library for the v3 Sheets API, retrieving all the data in a spreadsheet results in an iEnumerable of CellEntry. The .NET library for v4, however, retrieves all the data in a new data type called ValueRange.

//The v4 way of getting all the cell data. CellsNew is a ValueRange type.
var CellsNew = service.Spreadsheets.Values.Get("128Etx8HZMtsF1mB2BGD0b6qXA4m5Qn-rVTRPku4nw4Y", "A1:C25000").Execute();

//The v3 way of getting all the cell data. CellsOld is an IEnumerable<CellEntry> type.
var CellsOld = new SheetsHelper(token).GetCells("128Etx8HZMtsF1mB2BGD0b6qXA4m5Qn-rVTRPku4nw4Y/private/full/od6");

Since most of my code deals with analyzing the cell data in a Sheet, rather than migrate all of my code to use the ValueRange type, I made a strategic decision to keep using the old data type of iEnumerable<CellEntry>, and simply wrote a conversion function to convert the new type of ValueRange to the old type of iEnumerable<CellEntry>.

public static Google.Apis.Sheets.v4.Data.GridRange ToGridRange(string a1range)
{
    // trim off sheet name
    if (a1range.Contains("!"))
    {
        a1range = a1range.Split('!').Last();
    }
    string[] vals = a1range.Split(':');
    var c1 = ToRowColumn(vals[0]);
    var c2 = ToRowColumn(vals[1]);

    return new Google.Apis.Sheets.v4.Data.GridRange
    {
        StartColumnIndex = c1.Item1,
        StartRowIndex = c1.Item2,
        EndColumnIndex = c2.Item1,
        EndRowIndex = c2.Item2
    };
}

public static Tuple<int, int> ToRowColumn(string a1cell)
{
    string ALPHABET = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    string first = string.Empty;
    string second = string.Empty;

    CharEnumerator ce = a1cell.GetEnumerator();

    while (ce.MoveNext())
        if (char.IsLetter(ce.Current))
            first += ce.Current;
        else
            second += ce.Current;

    int i = 0;
    ce = first.GetEnumerator();
    while (ce.MoveNext())
        i = (26 * i) + ALPHABET.IndexOf(ce.Current) + 1;

    string str = i.ToString();
    return new Tuple<int, int>(i - 1, Int32.Parse(second) - 1);
}

public static IEnumerable<CellEntry> ToCellEntries(Google.Apis.Sheets.v4.Data.ValueRange valueRange)
{

    var gr = ToGridRange(valueRange.Range);

    uint rowIndex = (uint)gr.StartRowIndex.Value;

    foreach(var row in valueRange.Values)
    {
        uint colIndex = (uint)gr.StartColumnIndex.Value;
        foreach (var cell in row)
        {
            yield return new CellEntry
            {
                Row = rowIndex + 1,
                Column = colIndex + 1,
                Value = "" + cell,
            };
            colIndex++;
        }
        rowIndex++;
    }
}

Now I can just use this line to convert from the new datatype to the old:

var cellsNewToOld = ToCellEntries(CellsNew).ToList();

This makes my migration much easier, since now all I have to do is change the code that pulls the data, not the code that analyzes the data.

In conclusion, 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. Still though, I’ll slog through the changes, and the world will go on.

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.

13 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, Hobo, that’s just not viable given what a spreadsheet is. A spreadsheet IS a database behind its user interface — albeit a very rudimentary one. The value of a spreadsheet is in the user experience of working with structured data. In many cases for various applications, databases simply are not necessary when a simple 2D data table will do.

  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

  5. Whole heartedly agree with your stance here. It’s a disservice what Google is doing to developers who have built robust apps on their API’s previous version. The OAuth experience and security aren’t the big issues for our app, though — it’s the lack of querying with simple SQL. Why did they remove it, I have no idea. Check out my rant about this on Github, about midway down in the comments:

    https://github.com/googleapis/google-api-nodejs-client/issues/759

    It’s incredibly frustrating to say the least, and am happy to have stumbled upon this post blog in my research — it’s definitely validating.

  6. Another difference between the V3 and V4 API that is particularly annoying is the fact that the column header names do not get returned in the result set. On needs to use the getBatch call to pull the first row with the column names and any other rows you want to retrieve and write a routine to combine the two data sets into something that resembles the data that the V3 API used to return.

    What I find so unforgivable about this V4 API change is that there has been no effort on Google’s part to provide any kind of path or suggestions to the developer community on alternatives to not lose the functionality we have been relying on for so many years. There must be some business reason to make this change. If they want us to pay to use one of their other cloud services, at least point us in that direction.
    The only purpose of an API is to encourage developers to build software on top of Google’s cloud platform. We did exactly this because we assumed it was mutually beneficial. We went further and introduced our clients to Google’s cloud platform and generated business from these solutions we created. Google has shown a total disregard for the trust we have given them. If Google does not want us to use Google Sheets as a free low budget database, then all they need to do is provide a clear path to a paid solution where we can not lose the functionality we rely on. I have my credit card out and I would gladly pay for commercial solution but they haven’t suggested a viable alternative. As a result we are scrambling to find an alternative before we lose clients. It is really unforgivable in my opinion.

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