Skip to content

Instantly share code, notes, and snippets.

@psychemedia
Last active July 22, 2023 17:52
Show Gist options
  • Save psychemedia/7f8d350b742697919ad7 to your computer and use it in GitHub Desktop.
Save psychemedia/7f8d350b742697919ad7 to your computer and use it in GitHub Desktop.
Query form for Google spreadsheets
<html><head><title>Google Spreadsheet Explorer</title>
<script type="text/javascript" src="//www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {'packages':['table', 'scatterchart', 'linechart','piechart', 'barchart','columnchart']});
function removeChildrenFromNode(node) {
if(node.hasChildNodes()) {
while(node.childNodes.length >= 1 ) {
node.removeChild(node.firstChild);
}
}
}
var key='0AirrQecc6H_vdEZOZ21sNHpibnhmaEYxbW96dkNxZGc';
function preview() {
if (document.getElementById('gsKey').value!='') key=document.getElementById('gsKey').value;
if (/key=([^&]*)/.test(key)) key=/key=([^&]*)/.exec(key)[1];
var sheet=document.getElementById('gsSheet').value;
if (sheet!='') key+='&gid='+sheet;
var url='http://spreadsheets.google.com/tq?tq=select%20*where%20A=false&key='+key;
var query = new google.visualization.Query(url);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
visualization = new google.visualization.Table(document.getElementById('headings'));
visualization.draw(data, null);
columnList(data);
}
function drawViz() {
var gq='SELECT '+document.getElementById('gql').value;
document.getElementById('gqpreview').innerHTML=gq;
gq=encodeURIComponent(gq);
var url='http://spreadsheets.google.com/tq?tq='+gq+'&key='+key;
var purl='http://spreadsheets.google.com/tq?tqx=out:html&key='+key+'&tq='+gq;
var l=document.getElementById('htmlout');
l.innerHTML="<a href='"+purl+"'>HTML preview URL</a>";
purl='http://spreadsheets.google.com/tq?tqx=out:csv&key='+key+'&tq='+gq;
l=document.getElementById('csvout');
l.innerHTML="<a href='"+purl+"'>CSV URL</a>";
var query = new google.visualization.Query(url);
query.send(handleQueryResponse2);
}
function createCell(str){
var el=document.createElement('td');
el.innerHTML=str;
return el;
}
function columnList(data){
var t=document.getElementById('colLookup');
removeChildrenFromNode(t);
var labels=document.createElement('tr');
var headings=document.createElement('tr');
for (var i=0;i<data.getNumberOfColumns();i++){
var cell=createCell(data.getColumnId(i));
labels.appendChild(cell);
cell=createCell(data.getColumnLabel(i));
headings.appendChild(cell);
}
t.appendChild(labels);
t.appendChild(headings);
}
function handleQueryResponse2(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
removeChildrenFromNode(document.getElementById('preview'));
var typ;
var sel=document.getElementsByName('opType');
for (var i = 0; i < sel.length; i++)
if (sel[ i ].checked) typ=sel[i].value;
switch (typ){
case 'table': tablechart(data);break;
case 'linechart': linechart(data);break;
case 'scatterchart': scatterchart(data);break;
case 'piechart': piechart(data);break;
case 'barchart': barchart(data);break;
case 'columnchart': columnchart(data);break;
default:;
}
}
function tablechart(data){
new google.visualization.Table(document.getElementById('preview')).draw(data, null);
}
function scatterchart(data){
new google.visualization.ScatterChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
}
function linechart(data){
new google.visualization.LineChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
}
function piechart(data){
new google.visualization.PieChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
}
function barchart(data){
new google.visualization.BarChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
}
function columnchart(data){
new google.visualization.ColumnChart(document.getElementById('preview')).draw(data, {width: 800, height: 400});
}
</script>
</head>
<body>
<h1>Google Spreadsheet Explorer</h1>
<h2>Preview</h2>
<p>STEP 1: Enter a Google spredsheet key (and optional sheet ID) and hit preview to preview the column headings</p>
<form name="config">
Google spreadsheet key (e.g. <em>rvWgEEGK9xuUQBR1EFcxHWA</em> ) or spreadsheet URL <input type="text" size=30 id='gsKey' /> Sheet number* (optional): <input type="text" size=3 id='gsSheet' /> <input type="button" value="Preview table headings" onclick="preview()" /><br/>
<p>* The number of the first sheet in a spreadsheet (which is used by default) is 0. To select the second sheet, add: 1; for the third sheet, add 2, and so on.</p>
<p>If you are prompted to sign in, wait a few seconds and then try again.</p>
<div id="statrep"></div>
</form>
<hr/>
<div id="headings"></div>
<hr/>
<h2>Go fish...</h2>
<p>STEP 2: Once you have previewed the column headings, you can write your query. [<a href="https://developers.google.com/chart/interactive/docs/querylanguage">Google Query Language reference docs</a>]. Use the column letters shown above in your query (eg <tt>SELECT A,B LIMIT 10</tt>).</p>
<p>Try out some visualisation queries here...</p>
<table id='colLookup' border='1'></table>
<br/>
<form name='qform'><p><tt>SELECT</tt> <span id='vql'></span> <input type="text" id="gql" size=200 /></p>
<p>Display as:
| Table <input type="radio" name="opType" value="table" checked />
| Scatter chart <input type="radio" name="opType" value="scatterchart"/>
| Line chart <input type="radio" name="opType" value="linechart"/>
| Pie chart <input type="radio" name="opType" value="piechart"/>
| Bar chart <input type="radio" name="opType" value="barchart"/>
| Column chart <input type="radio" name="opType" value="columnchart"/>
|
<input type="button" value='Go Fish' onclick="drawViz()"/>
</p>
</form>
<div>So you are asking: <em id='gqpreview'></em></div>
<div>Here is the URL for that query: <span id='htmlout'></span>, <span id='csvout'></span></div>
<div id="preview"></div>
<div id="linechart"></div>
<div id="scatterchart"></div>
<div id="piechart"></div>
</body>
</html>
@richardARPANET
Copy link

Perfect thanks. You can also do something similar without a little less code using https://sheet2api.com/website-form-to-google-sheets/ . Although maybe that route has it's own limitations.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment