Solved

view results of google SQL query

Posted on 2016-10-18
9
76 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
[X]
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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Google Forms is a great platform to easily create polls, questionnaires and surveys. The service allows you to receive data in real-time and is a simple way to conduct large-scale surveys that can be distributed to multiple people.   The platform …
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This Micro Tutorial will demonstrate how to use comments in Google Docs. You will learn how to create, edit, and delete a comment.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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