Solved

view results of google SQL query

Posted on 2016-10-18
9
53 Views
Last Modified: 2016-10-19
hi,
With google sheet I can query any data sheet with one formula, eg:

=query(importRange(SourceDataKey, "regular!A:Z"),"SELECT * where upper(Col1)='F61' and upper(Col2)='IN'", true)

The output is an array of data.

I would like to be able to view this output automatically from a URL. I would generate a URL in google sheets and clicking it would show the results on a separate sheet.

myURL/... SourceData=.... Sheet=.... SQL= .....

Any suggestions welcome. I'm new to scripts but this might be an option too.

Thanks
0
Comment
Question by:xenium
  • 9
9 Comments
 

Author Comment

by:xenium
ID: 41849018
0
 

Author Comment

by:xenium
ID: 41849030
From the developer documentation, this appears to be possible but I've only just seen this and not yet made it work:

https://developers.google.com/chart/interactive/docs/querylanguage#setting-the-query-in-the-data-source-url
0
 

Author Comment

by:xenium
ID: 41849041
Here's a working example URL that returns a query result:

https://docs.google.com/spreadsheets/d/1YemYMnvCewfTAIj6uqWQ8tGwMf-uQfsmXj1jRwIBO_c//gviz/tq?tq=select%20*%20where%20A%20%3C%2010

How can i have this display as a page? instead of downloading as a txt file which is in some kind of coded format

thanks
0
 

Author Comment

by:xenium
ID: 41849195
Here is a URL giving an HTML view of a google spreadsheet, but without any query:

https://spreadsheets.google.com/tq?tqx=out:html&range=A1:B20&key=1YemYMnvCewfTAIj6uqWQ8tGwMf-uQfsmXj1jRwIBO_c

I'm looking to combine this with a query
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Accepted Solution

by:
xenium earned 0 total points
ID: 41849211
0
 

Author Comment

by:xenium
ID: 41849215
How can i specify a different sheet to query from. THe default appears to be the first sheet.
0
 

Author Comment

by:xenium
ID: 41849671
The following is a great overview, though the current URL syntax may have changed (see above for current examples)

http://acrl.ala.org/techconnect/post/query-a-google-spreadsheet-like-a-database-with-google-visualization-api-query-language

To specify a sheet, set the GID (visible in the URL when viewing that sheet)
0
 

Author Comment

by:xenium
ID: 41850044
I will close this question shortly with a summary and hopefully a demo doc
0
 

Author Comment

by:xenium
ID: 41850160
Template with examples here: https://docs.google.com/spreadsheets/d/1YemYMnvCewfTAIj6uqWQ8tGwMf-uQfsmXj1jRwIBO_c/edit#gid=1468195056

Note this is editable so please only add constructive input.

Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cloud file services can fill many different roles for your business. Often, the use of cloud file services begins with employees using consumer products, like Dropbox, to share files with customers and each other. While sync-and-share can be an effe…
The choice of selecting a perfect productive suite for one’s organization is a critical decision that an IT Manager or a CIO has to make. And when it comes to choosing a cloud-based productivity suite, the choice gets all the more difficult. This is…
This Micro Tutorial demonstrates in Google Analytics how to create a custom report that shows you traffic over time using the month of year dimensions. There are also instructions on how to fix Google's odd month of year formatting, which Microsoft …
This Micro Tutorial will demonstrate common damaging and frequent mistakes I see in most analytic audits. Most of them are campaign tagging mistakes, so this video will break it down into simple steps.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now