“These two functions are the essence of how I personally use the Google Sheets API”
Developers are up in arms over a forced migration to version four of Google Sheets in March 2020, saying the migration breaks numerous functionalities.
Google Sheets is a web-based software tool for creating and storing spreadsheets. The service uses Google Drive and can be integrated across G-suite. Sheets allows team members to edit and collaborate on spreadsheets in real-time.
A key feature used by programmers in Google Sheets v3 was the ability to run structured queries on Sheets that returned only matching rows.
This ability to run a complex search, which involved the filtering process happening inside the Sheets API, helped to save client bandwidth as only the rows pertinent to the search are sent back by the API. This function is lost with the migration.
He noted: “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.”
This means that if a spreadsheet has 50,000 rows and you want to return information on 300 hundred rows that contain your chosen keyword then the API is forced to retrieve all 50,000 rows just to pull out the 300 you want.
Goel states that: “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.”
Other developers have taken to social media vent.
They're killing the regular JSON endpoint of every Google Sheet. Instead complicating everything with OAuth permissions. The whole usability was being able to drop in a sheet URL (you set as public) and using it on another site.
— (@levelsio) January 15, 2020
Google Sheets API
A second omission from v4 of Sheets is the ability that allowed you to list all Google Sheets in a user’s account via one specific endpoint within the https://spreadsheets.google.com/feeds scope.
Now Goel notes that: “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:”
This involves the user reading the files from the user’s Google Drive which entailed the system asking for additional permissions, as it wants to read all files in the Google Drive not just the Sheets ones. Not only could this create a security risk further down the line, but Goel believes that it will actually scare off less tech savvy users as they will be required to go through an ominous OAuth login process.
Needless to said Goel is frustrated to the extreme by what seems to be an unnecessary destruction of functionality in Sheets that is been forced on users as they have to migrate to version 4. As he puts it: “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.”
Google has been contacted for comment.