Skip to content

Instantly share code, notes, and snippets.

@strburst

strburst/gtroommatequery.html

Last active August 29, 2015 14:00
Show Gist options
  • Save strburst/91a4b6473ec935761edb to your computer and use it in GitHub Desktop.
Save strburst/91a4b6473ec935761edb to your computer and use it in GitHub Desktop.
Download ZIP
Quickly search the responses to a Georgia Tech roommate survey with GQL. Live demo: https://googledrive.com/host/0BxSLWV_DSAQifmU2bkZhOVlGR2JBSTRTb21aSzFuZW82S0FhWHNpTExZN2d1LWpCV05NS2s/gtroommatequery.html
Raw
gtroommatequery.html
<!DOCTYPE html>
<html>
<head>
<meta name="author" content="Allen Zheng">
<title>Georgia Tech Roommate Query Tool</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
<script type="text/javascript" src="scripts.js"></script>
</head>
<body>
<h1>Georgia Tech Roommate Query Tool</h1>
<h2>Instructions</h2>
<p>Hi there! This tool allows you to query <a href=
"https://docs.google.com/spreadsheet/ccc?key=0AuOJ1qBo4DTedFhpenFvS2ZvV3ZVQ
VNtX0tSRmFTUXc#gid=0">the responses</a> to Xinyang Chen's <a href=
"https://docs.google.com/forms/d/1NRoVVjzLFx9cl2tMHVX-xRVp-Jyb1wVmOKIQHnX9V
zA/viewform">roommate survey</a>. Simply type your search terms in one or
more boxes below, and those columns will be searched to see if they
contain your entries. It uses Google Query Language (GQL), an SQL-like
query language.</p>
<p>CS majors: feel free to examine the source code and suggest changes.
I've tried making things pretty, adding comments here and there, and
avoiding ugly hacks, as far as my limited HTML/Javascript knowledge
allows. According to RescueTime, the automated time tracker, I've spent
about 3.5 hours working on this.</p>
<h3>Limitations</h3>
<ul>
<li>You can only search for one thing at a time in each column. Try
opening multiple tabs.</li>
<li>All searches are case sensitive. However, this script will search
for both the original and all lowercase versions of search terms. For
example, "No" will match "no", "Probably not" and "Nope", but not
"Never".</li>
<li>Be aware that there may be false positives/negatives. For example,
searching "No" in the column <code>Drugs</code>, would match "No, but
marijuana is fine" and "Yes, but no meth". <code>Gender</code> and
<code>Preferred Campus</code>, however, are guaranteed to work well.</li>
</ul>
<h2>Main Form</h2>
<form name="main">
<table>
<tr>
<th>Name</th>
<th>Contains</th>
</tr>
<tr>
<td>
<input type="checkbox" name="ax" />
<code>Timestamp</code>
</td>
<td>
<input type="text" name="a" size="40"
onkeyup="handleCheckBox('ax')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="bx" />
<code>Name</code>
</td>
<td>
<input type="text" name="b" size="40"
onkeyup="handleCheckBox('bx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="cx" />
<code>Gender</code>
</td>
<td>
<input type="text" name="c" size="40"
onkeyup="handleCheckBox('cx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="dx" />
<code>Preferred Campus</code>
</td>
<td>
<input type="text" name="d" size="40"
onkeyup="handleCheckBox('dx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="ex" />
<code>Hometown</code>
</td>
<td>
<input type="text" name="e" size="40"
onkeyup="handleCheckBox('ex')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="fx" />
<code>Major</code>
</td>
<td>
<input type="text" name="f" size="40"
onkeyup="handleCheckBox('fx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="gx" />
<code>Greek Life</code>
</td>
<td>
<input type="text" name="g" size="40"
onkeyup="handleCheckBox('gx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="hx" />
<code>Drinking</code>
</td>
<td>
<input type="text" name="h" size="40"
onkeyup="handleCheckBox('hx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="ix" />
<code>Smoking/Drugs</code>
</td>
<td>
<input type="text" name="i" size="40"
onkeyup="handleCheckBox('ix')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="jx" />
<code>Know People at Georgia Tech?</code>
</td>
<td>
<input type="text" name="j" size="40"
onkeyup="handleCheckBox('jx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="kx" />
<code>Social Plans</code>
</td>
<td>
<input type="text" name="k" size="40"
onkeyup="handleCheckBox('kx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="lx" />
<code>Study Habits</code>
</td>
<td>
<input type="text" name="l" size="40"
onkeyup="handleCheckBox('lx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="mx" />
<code>Roommate Relationship</code>
</td>
<td>
<input type="text" name="m" size="40"
onkeyup="handleCheckBox('mx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="nx" />
<code>Sleeping Habits</code>
</td>
<td>
<input type="text" name="n" size="40"
onkeyup="handleCheckBox('nx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="ox" />
<code>Privacy</code>
</td>
<td>
<input type="text" name="o" size="40"
onkeyup="handleCheckBox('ox')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="px" />
<code>Other People Staying Overnight?</code>
</td>
<td>
<input type="text" name="p" size="40"
onkeyup="handleCheckBox('px')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="qx" />
<code>Preferred Dorm Temperature</code>
</td>
<td>
<input type="text" name="q" size="40"
onkeyup="handleCheckBox('qx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="rx" />
<code>Introvert/Extrovert</code>
</td>
<td>
<input type="text" name="r" size="40"
onkeyup="handleCheckBox('rx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="sx" />
<code>Music</code>
</td>
<td>
<input type="text" name="s" size="40"
onkeyup="handleCheckBox('sx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="tx" />
<code>TV</code>
</td>
<td>
<input type="text" name="t" size="40"
onkeyup="handleCheckBox('tx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="ux" />
<code>Hobbies</code>
</td>
<td>
<input type="text" name="u" size="40"
onkeyup="handleCheckBox('ux')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="vx" />
<code>Organizations</code>
</td>
<td>
<input type="text" name="v" size="40"
onkeyup="handleCheckBox('vx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="wx" />
<code>Food</code>
</td>
<td>
<input type="text" name="w" size="40"
onkeyup="handleCheckBox('wx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="xx" />
<code>Bad Habits</code></td>
<td>
<input type="text" name="x" size="40"
onkeyup="handleCheckBox('xx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="yx" />
<code>Additional/Miscellaneous Comments</code>
</td>
<td>
<input type="text" name="y" size="40"
onkeyup="handleCheckBox('yx')" />
</td>
</tr>
<tr>
<td>
<input type="checkbox" name="zx" />
<code>Social Media Link</code>
</td>
<td>
<input type="text" name="z" size="40"
onkeyup="handleCheckBox('zx')" />
</td>
</tr>
<tr>
<td>
<button type="button" onclick="handleForm()">Submit</button>
</td>
</tr>
</table>
</form>
<h2>Submit Raw GQL</h2>
<p>The GQL being generated by the script upon submission will be placed
below. You can also edit and submit GQL directly from here. To learn more
about Google's query language, see <a href=
"http://acrl.ala.org/techconnect/?p=4001"> this blog post</a> and <a href=
"https://developers.google.com/chart/interactive/docs/querylanguage">this
documentation page</a>.</p>
<textarea id="rawgql" rows="8" cols="50">SELECT *</textarea><br />
<button type="button" onclick="handleGql()">Submit</button>
<div class="smallEnd">
<p><em>Created by Allen Zheng on May 5, 2014.</em></p>
<a rel="license" href="http://opensource.org/licenses/MIT">This page is
distributed under the MIT license.</a>
</div>
</body>
</html>
Raw
scripts.js
/**
* Automatically check a checkbox if the user entered text in the
* associated textbox.
*/
function handleCheckBox(str) {
if (document.forms["main"][str.charAt(0)].value!="") {
document.getElementsByName(str)[0].checked=true;
} else {
document.getElementsByName(str)[0].checked=false;
}
}
/**
* Called upon form submission. Find the rows that are checked and
* filled in, make the url, and open it.
*/
function handleForm() {
var submitCol = new Array(26);
for (var i = 0; i < submitCol.length; i++) {
var colName = String.fromCharCode(97+i); // Iterate a-z
var isChecked = document.forms["main"][colName+"x"].checked;
var isEmpty = document.forms["main"][colName].value=="";
submitCol[i] = isChecked && !isEmpty;
}
var sql = makeSQL(submitCol);
document.getElementById("rawgql").innerHTML = sql;
var url = buildUrl(sql,
"0AuOJ1qBo4DTedFhpenFvS2ZvV3ZVQVNtX0tSRmFTUXc", 0);
window.open(url);
}
/**
* Use user's raw GQL to query.
*/
function handleGql() {
var sql = document.getElementById("rawgql").innerHTML
var url = buildUrl(sql,
"0AuOJ1qBo4DTedFhpenFvS2ZvV3ZVQVNtX0tSRmFTUXc", 0);
window.open(url);
}
/**
* Build an SQL query from the indicated columns.
*/
function makeSQL(submitCol) {
// Get the reference of the last true value in submitCol.
for (var lastFilled = submitCol.length-1;
!submitCol[lastFilled] && lastFilled > 0; lastFilled--);
if (lastFilled == 0) {
return "SELECT *";
}
var query = "SELECT * WHERE ";
for (var i = 0; i <= lastFilled; i++) {
if (submitCol[i]==true) {
var colName = String.fromCharCode(65+i);
var searchText = document.forms["main"][colName.toLowerCase()].value;
// Include both upper and lowercase versions.
query += "(" + colName + " CONTAINS '" + searchText + "' OR " +
colName + " CONTAINS '" + searchText.toLowerCase();
if (i < lastFilled) {
query += "') AND "
} else {
query += "')";
}
}
}
return query;
}
/**
* Build a valid Google Sheets url from the given components.
*/
function buildUrl(sql, key, sheetNum) {
return "https://spreadsheets.google.com/tq?tqx=out:html&tq=" +
encodeURIComponent(sql) + "&key=" + key + "&gid=" + sheetNum;
}
Raw
styles.css
.smallEnd {
text-align: center;
}
body {
margin-left: 20%;
margin-right: 20%;
margin-bottom: 40px;
text-align: justify;
}
th {
text-align: left;
}
td {
padding-right: 20px;
}
em {
text-align: center;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment