I have a Google Sheet that I want to let users query in a Google Site by picking three options in select lists and to have it return the results as a table.
My sheet is here.
I have tried a few setQuery examples but not getting much luck since almost all examples return a chart which I do not want, just a table of what is in my sheet. Below is what I embed into my Google Site (minus the CSS). The function text() is just a test to make sure I was capturing people's choices. So as you will see this code does not have the Google functions in the script yet, that's what I am missing.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Search For Your Zodiac Twin</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<link href="https://fonts.googleapis.com/css?family=Open+Sans" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script>
function text(){
var s = document.getElementsByName('sun')[0];
var stext = s.options[s.selectedIndex].text;
var m = document.getElementsByName('moon')[0];
var mtext = m.options[m.selectedIndex].text;
var r = document.getElementsByName('rising')[0];
var rtext = r.options[r.selectedIndex].text;
document.getElementById("resulttext").innerHTML='Instagram users who match your signs are:';
document.getElementById("resulttable").innerHTML='Your sun sign is ' + stext + ', your moon is ' + mtext + ', and your rising is ' + rtext;
}
</script>
</head>
<body>
<div class="container">
<form action="">
<div class="form-group">
<label for="sun" style="color: #ffc107ff">Sun</label>
<br>
<select name="sun" class="form-control-sun" id="sun">
<option value="" selected disabled hidden>Choose Your Sun</option>
<option value="Aquarius">Aquarius</option>
<option value="Aries">Aries</option>
<option value="Cancer">Cancer</option>
<option value="Capricorn">Capricorn</option>
<option value="Gemini">Gemini</option>
<option value="Leo">Leo</option>
<option value="Libra">Libra</option>
<option value="Pisces">Pisces</option>
<option value="Sagittarius">Sagitarrius</option>
<option value="Scorpio">Scorpio</option>
<option value="Tarus">Taurus</option>
<option value="Virgo">Virgo</option>
</select>
<br>
<label for="moon" style="color: #2196f3ff">Moon</label>
<br>
<select name="moon" class="form-control-moon" id="moon">
<option value="" selected disabled hidden>Choose Your Moon</option>
<option value="Aquarius">Aquarius</option>
<option value="Aries">Aries</option>
<option value="Cancer">Cancer</option>
<option value="Capricorn">Capricorn</option>
<option value="Gemini">Gemini</option>
<option value="Leo">Leo</option>
<option value="Libra">Libra</option>
<option value="Pisces">Pisces</option>
<option value="Sagittarius">Sagitarrius</option>
<option value="Scorpio">Scorpio</option>
<option value="Tarus">Taurus</option>
<option value="Virgo">Virgo</option>
</select>
<br>
<label for="rising" style="color: #4caf50ff">Rising</label>
<br>
<select name="rising" class="form-control-rising" id="rising">
<option value="" selected disabled hidden>Choose Your Rising</option>
<option value="Aquarius">Aquarius</option>
<option value="Aries">Aries</option>
<option value="Cancer">Cancer</option>
<option value="Capricorn">Capricorn</option>
<option value="Gemini">Gemini</option>
<option value="Leo">Leo</option>
<option value="Libra">Libra</option>
<option value="Pisces">Pisces</option>
<option value="Sagittarius">Sagitarrius</option>
<option value="Scorpio">Scorpio</option>
<option value="Tarus">Taurus</option>
<option value="Virgo">Virgo</option>
</select>
<br>
<label style="color: #6a1b9aff;">Search</label>
<br>
<input type="button" value="Find My Twin" id="but" onclick="text()" />
<hr>
<p id="resulttext"></p>
<p id="resulttable"></p>
</div>
</form>
</div>
</body>
<html>
So for example, if someone selected sun Aquarius, moon Aries, and rising Cancer then when they hit the button, I would want it to return the full table where C = sun, D = moon, and E = rising.
I managed to figure it out yesterday. I added this to my head's script
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript" src="//www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {packages: ['table']});
</script>
<script type="text/javascript">
var visualization;
function drawVisualization() {
// This saves the user's selected options from the selects in the body
var sun = $('#sun').find(":selected").text();
var moon = $('#moon').find(":selected").text();
var rising = $('#rising').find(":selected").text();
//How to query from a Google Sheets doc via URL
var query = new google.visualization.Query(
'//docs.google.com/spreadsheets/d/1f5WBP_vizrCw27fSA6yc0EEN_twmdC-PoXZhm_PSNH0/edit?usp=sharing');
// The SELECT query where I search based on the user input variables, also created labels for the table headers
query.setQuery("SELECT B,C,D,E WHERE C = '"+sun+"' AND D = '"+moon+"' AND E = '"+rising+"' ORDER BY B LABEL B 'Instagram', C 'Sun', D 'Moon', E ' Rising'");
// Send the query with a callback function.
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('visualization'));
visualization.draw(data, {width: '500px'});
}
</script>
And this part here is the little snippet from the body of the HTML that triggers the above function
<input type="button" value="Find My Twin" id="but" onclick="drawVisualization()" />
<hr>
<div id="visualization"></div>