Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

view results of google SQL query

Posted on 2016-10-18
9
64 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
How our DevOps Teams Maximize Uptime

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

 

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
 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Explore the encryption capabilities built into Google Apps and how these features can help you meet privacy policy and regulatory compliance, but are not a full solution. Understand and compare the most popular email encryption services for Google A…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial will demonstrate the easy use of Gmail embedding images in your email so the recipient of your email can view them in context.

839 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