Spreadsheet query

[1dadavies]

I want to query a google spreadsheet by using a url that looks like this:

https://docs.google.com/spreadsheets/d/1cFNMJamVRJ_wk7Z7SiRZxWu8U03UDYL2VoX9WOEIgbU/gviz/tq?tqx=out:html&tq=select%20B%20where%20A%20contains%20MY_SEARCH_STRING

When I use the spreadsheet plugin, there's no option allowing me to specify the query. Only all rows or last row, but not an actual query.

It would be great if the plugin could allow the user to specify the url of the gsheet using variables. This would allow one to query their gsheet by controlling "select", "where", and "contains" portions of the url to perform any gsheet query.

If my question isn't clear, there's a very good link here of an example showing how to adjust the gsheet url, providing the query capability: http://www.bohyunkim.net/blog/archives/2831#.WGp-soMrKXI

Doing the query like this is beautiful, but I think the plugin needs this enhancement added to achieve it. If anyone knows otherwise, please let me know.

[Peter Unold]

Hi - I'm the author of the Spreadsheet plugin.

When Google(less than a year ago) upgraded their Sheets API to version 4 - some features from version 3 was removed:

"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."

This affects the plugin - since I upgraded the code to use the new API. So any filtering would have to be done client-side. This is very doable, but not ideal if you have large sheets.

Anyway I've put it on my idealist: https://trello.com/b/FZCk9QTI/tasker-spreadsheet-plugin

Another way of obtaining a query, would be to create special sheet which contains the result of a query. The query-sheet could be updated every time the data sheet was updated. I have some ideas of how to do this, if you're interested.

Best regards Peter

[1dadavies]

Thanks very much for your reply Peter.
The google sheet I want to query is too large to retrieve, so I really need a way to query it.
Yes, I think I can make your idea work to create a query to put on another sheet and get the result from it, however,
my application requires the lowest possible latency when triggered, so even 1 or 2 seconds delay from unnecessary spreadsheet operations is not desirable.
I would prefer, if it's possible, to add java code that would handle authentication, send the query url and get the response with possibly http commands.
From reading, it seems to involve OAuth2.0 but I have been unable to find a simple example of it.
I started following a tutorial about the google api, Android Developer Studio, Gradle, building the app, etc. It seemed like too much effort and there must
be an easier way.
If I have pre-login to my google account using Chrome for example, before running the query, that's fine.
Any suggestions appreciated.
Regards,
David

[Peter Unold]

Just an idea. How about storing your data both in the sheet and locally in sqlite? There is a Tasker Sqlite plugin and it would give you optimal performance.

[1dadavies]

I got it working using your first suggestion and it's okay performance-wise. The special query sheet is updated with appropriate lookup and match, then readback the row below it (last line) for the query result.
Sometimes, however, the update isn't happening. I'm still trying to get a clue why. I'll give more details after more debug.

[1dadavies]

I have more details about the problem. Sometimes the Cell Data shows up in the special query sheet with "%Cnum" instead of the data.
The cell data I have in the plugging is below. It's strange because it does this intermittantly.

CELL DATA:
=IF(ISBLANK(VLOOKUP(%Cnum,contacts!A:B,2,False)),"no info",VLOOKUP(%Cnum,contacts!A:B,2,False));=IF(ISBLANK(VLOOKUP(%Cnum,contacts!A:C,3,False)),"First call",VLOOKUP(%Cnum,contacts!A:C,3,False));=MATCH(%Cnum,contacts!A:A,0);=IF(ISBLANK(VLOOKUP(%Cnum,contacts!A:D,4,False)),0,VLOOKUP(%Cnum,contacts!A:D,4,False))

[Raphael Awoseyin]

On Saturday, 7 January 2017 21:41:40 UTC+1, Peter Unold wrote:

Just an idea. How about storing your data both in the sheet and locally in sqlite? There is a Tasker Sqlite plugin and it would give you optimal performance.

A pluggin may not even be required. Recently, I also needed to query a large volume of data I had on a spreadsheet. I exported the spreadsheet to a .CSV file. I created an empty SQLite database (using aSQLiteManager) and a small task that populates a table in the database with data read from the .CSV file. The latest Tasker already has SQL built in and I query the database on-the-fly.

By the way, the .CSV file resides both on my Google Drive and on the phone, and I have FolderSyncLite to update the one on the phone whenever the GDrive version is updated. When the copy on the phone gets updated, my Tasker app notices it next time it is run and updates the database table accordingly.

[1dadavies]

Hi Peter,

I am still trying to make the Tasker Spreadsheet plugin work reliably. I'm seeing an issue that maybe you're aware of.

The plugin will stop working after a while, where a while varies but is roughly 30-60 minutes.

The failure can be accelerated by powering off the phone and back on and then the spreadsheet access will not work. Never again will it work .

until going into the action and click "configuration" (there will be a popup from the plugin saying it's reading the spreadsheet).

Don't click save or test. Just get out of the action and then it will work again until I don't do any spreadsheet operations for 30-60 minutes, after which time it stops working again.

Very very repeatable.

If you can fix it, that would be great.

Thanks,

David

[Peter Unold]

Hi David,

Thank you - that's a very good description of the issue. Sounds like the oauth access token to the sheets service is expiring. By entering the plugin's edit activity the access toking is renewed.

I cannot reproduce it on my main device, but I have an idea. Will post again in a day.

Best regards Peter

[Peter Unold]

I've pushed an update to the beta channel of the plugin:

https://play.google.com/apps/testing/com.uitcon.spreadsheettaskerplugin

I've tested on a Nexus 5X and an old Galaxy S2(running 4.1):

Created a Tasker profile that every 2nd minute updates the spreadsheet. I've let it run for a couple of hours and restarted the devices. Everything was alright.

One thing I noticed was that the 5X stopped updating after a while. I assume it went into Doze mode. As soon is I touched the screen the missing updates were coming through. Notice that this version of the plugin also implements full background updates(offline mode=true) for 5.0+ devices using the JobScheduler API.

I'll keep testing but will be happy for any help.

Best regards Peter