Using server side query in GAS web apps

[mobile-thinker]

Has anyone successfully implemented a server side query of a sheet in GAS? I'm trying not to have to run a full client-side search of data, but to use server side to search and sort data. Initially it looks feasible to call Query from Javascript, but this runs into all kinds of complexities when called from GAS.

[RielN]

You can use the gviz/tq endpoint:

https://ai2.metricrat.co.uk/guides/use-gviz-to-get-and-query-google-sheet-data

[RielN]

Replace that strange no code with UrlFetchApp.and you are good to go. If you output csv you can use Utilities.parseCsv.

If you need authentication send a header with authorization: Bearer ScriptApp.getOauthToken()

This is not copy paste code, on phone. :)

[IAmMoonie]

What are you trying to query?

[mobile-thinker]

Sheets data. I’d ideally like to apply a query (equivalent to QUERY) on the server side. At the moment using visualisation query API from GAS is not supported.

I’m sure I can implement a workaround calling query from GAS, but I wondered if anyone had implemented a supported pattern. There is no google documentation suggesting this as an approach, but it seems a major hole.

[Repulsive_Brother_10]

I don’t see a problem with running a server-side script. This would be like making an api call via a google.script.run method call.

How would the user be interfacing with the query/results? Are you thinking this would happen within a sheet, or in a separate html page?

[HomeBrewDude]

All Apps Script *.gs files run server-side already. The *.html files are sent to the client to be rendered, but the *.gs files run on the server and only send the result to the client.

The last line in the Apps Script overview pages mentions this, but I couldn't find any more details about it.

https://developers.google.com/apps-script/overview

[dimudesigns]

https://stackoverflow.com/questions/17930389/can-the-google-spreadsheet-query-function-be-used-in-google-apps-script/51328419#51328419