Google-api-nodejs-client: Sheets v4: structured queries

[tomasdev]

From the migration docs: https://developers.google.com/sheets/api/guides/migration

The Sheets API v4 does not currently have a direct equivalent for the Sheets API v3 structured queries. However, you can retrieve the relevant data and sort through it as needed in your application.

Is there a timeline for when there'd be a structured queries equivalent on v4? Downloading a 100k row spreadsheet via API to then do in-memory filtering seems overengineering. Are there alternatives on v4?

Thanks!

[ace-n]

cc @erickoledadevrel

[erickoledadevrel]

We're not working on that feature at the moment, and I would't expect to see it in the near term. The general feeling is that once you've reached a scale where that functionality is required you're also at a point where you should probably consider moving your data to a database. Spreadsheets are great for data visualization and manipulation, but are not meant to be a replacement for a database.

[ace-n]

Closing this issue for now - feel free to reopen if there's still something to discuss.

[ariestav]

@erickoledadevrel

Spreadsheets are great for data visualization and manipulation, but are not meant to be a replacement for a database.

That isn't an excuse for removing the useful feature of structured queries. By this logic, why was it supported for three versions of the API? In addition, why would a Google Sheet allow a spreadsheet to have hundreds of thousands, or millions, or rows. It's really a shame that this will be deprecated from v3 of the API.

The app I develop does uses Google Sheets as a "database" to help our users quickly ramp up with dealing with large amounts of data. Many users who first approach data as a "tool" so to speak have no experience with a database and they don't know even how to host one, query one, update records, etc. So, Google Sheets is a wonderful alternative because of the UI and the visual representation of the 2D array of data.

Please, can you add this feature? Where do I add a feature request?

[erickoledadevrel]

I understand your frustration, and I know you aren't alone. The surface of an API guides how it is used, and I believe the Sheets team wants to steer developers away from relying on Sheets as a database, even if spreadsheets can be forced to serve that role with moderate success.

I published my own thoughts on using Sheets as a database here:

https://medium.com/@eric_koleda/why-you-shouldnt-use-google-sheets-as-a-database-55958ea85d17

Regardless, it does seem unlikely that this feature will be supported any time soon, but I'll pass along your feedback to the Sheets team.

[ariestav]

Thanks for the link to the article. No developer is going to "rely on" or "abuse" Sheets as a database for their application. Developers know very well that Sheets is not the perfect place for storing persistent data for an application. That's not the point though. The point is that Sheets is a great starting place, so to speak, for end-users who know nothing about databases — the query feature separates Google implementation of a spreadsheet as opposed to MS Excel. Now that you are removing it, there's not much different between Sheets and Excel. By making the API less useful you'll lose developers building around it.

You are not going to support a feature because of "fear" that developers will abuse it as a database? That's an unfounded fear and you will likely see more hits to the Sheets API as a result of removing this feature in addition to larger payloads transported from your servers — I just thought that "scale" would be considered by engineers employed by Google, but I guess not?

How hard is it to really support the feature if it's already there and the logic is already mapped and implemented? I appreciate your well thought out arguments, but I still disagree with your points. As such, our company will likely be supporting and promoting another "user friendly" app like Airtable rather than Sheets due to your removal of this single feature. I'm glad to know I'm not the only one that is a bit baffled by this decision, but it's your team's decision to make.

[ariestav]

@erickoledadevrel It looks like our app's OAuth screen is now disabled on our app causing integration to have stopped working. Apparently Google is now requiring a "verification" of the OAuth consent screen. That's fine, bit it looks like it will be taking "several weeks" to verify. Our app has been functional since 2014 and we were not made aware of this issue. Who can I get in touch with at Google to expedite the verification of the scopes our app needs now that there is a verification step.

[erickoledadevrel]

Sorry for the delay, I was on vacation last week. Sorry to hear about the verification issues, have you made progress in the last 7 days? If you get stuck in the process you can email [email protected] which may be able to get the process moving again.

[ariestav]

Yes, after pushing it through premium support, we were able to get it resolved, thanks. Thank you for the email address, too.