This is one example how to use Google Spreadsheet as searchable database - although there is lack of automation in its display output process but it shows the possibility of using Google Spreadsheet as database. I couldn't really recalled where I've code the coding but the simplicity of this piece of work open the ideas to utilize the Google Spreadsheet to a greater capability.
In this example we are making a database of postal codes - as i said earlier the search box capability is not so automated that you have to type specific keywords with case sensitive words. This can be modified if you are good in programming to make it automated - and it uses the json to deliver the data being search.
You can use the coding and change this https://spreadsheets.google.com/a/google.com/tq?key=1WDoiQGwoFTxPwQsOBSsCs-B0R4FiW1kaikYVbpEBPak part to retrieve your own Google Spreadsheet data. Note that the Google Spreadsheet key "1WDoiQGwoFTxPwQsOBSsCs-B0R4FiW1kaikYVbpEBPak" is the most important to replace according to your own spreadsheet key. You can use it anyway you want it or modify according to your need.
Try the search functionality here: Malaysia Postal Codes Database
THIS IS THE CODES:
<h3>
<u>How to use the Postal Search!</u></h3>
<ul>
<li>Search the codes by states with the first letter is capital i.e. Sarawak</li>
<li>Use the "Filter by City and Filter by Street" - it will automatically narrow down search results</li>
<li>Use the following keywords to search Malaysia Postal Codes by States</li>
</ul>
<div>
Johor | Kedah | Kelantan | Kuala Lumpur | Labuan | Melaka | Negeri Sembilan | Pahang | Putrajaya | Perlis | Pulau Pinang | Perak | Sabah | Selangor | Sarawak | Terengganu |</div>
<hr />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js" type="text/javascript"></script> <script src="https://www.google.com/jsapi" type="text/javascript"></script> <script type="text/javascript"> google.load('visualization', '1.0', {packages:['controls']}); </script> <style type="text/css"> #fd_main p {font: 13px/16px Arial; margin-bottom:10px !important; padding:0;} #fd_main h4 {font-family:Arial; font-weight:bold; text-transform:uppercase;text-align:left; padding:0 0 5px 0; margin:0;} #fd_main label {font: bold 13px/24px Arial; padding: 0 5px 0 0; float:left; vertical-align: middle;} #fd_main input, textarea, select, button {font: 13px/16px Arial; color:#959595; vertical-align: middle;} .fd_note{font-family:Arial;color:#5E5E5E; font-size:.8em; border:none; padding:0;} div.search_widget{background:#E1E1E1;background:-webkit-gradient(linear,left top, left bottom,from(#FFF),to(#E1E1E1));background:-webkit-linear-gradient(top,#FFF,#E1E1E1);background:-moz-linear-gradient(top,#FFF,#E1E1E1);background:-ms-linear-gradient(top,#FFF,#E1E1E1);background:-o-linear-gradient(top,#FFF,#E1E1E1);border:2px solid #E1E1E1;padding:20px;-moz-border-radius:10px;-webkit-border-radius:10px; margin-bottom:20px;} div.control{margin-bottom: 15px; color:black;height:26px;} div.chart1{float: left;} .header {background-color: #5B5B5B; color: #FFFFFF; font-family:Arial, sans-serif; text-align:left; font-size:0.8em; text-transform:uppercase; font-weight:600; letter-spacing:1px; line-height:1.4em; border:none;} .row { background-color: #FFFFFF; color: #3F3F3F; font-family:inherit; font-size:0.9em;} .oddRow { background-color: #F5F5F5; color: #3F3F3F; font-family:inherit; text-align:left; font-size:0.9em;} .hoverRow { background-color: #CDCDCD; font-family:inherit; text-align:left; font-size:0.9em; } .selectedRow { background-color: #DCDCDC; color: #3F3F3F; text-align: center; font-family:inherit; text-align:left; font-size:0.9em; } .cell, table {padding:2px;border:none;} .headCell {padding:0;} .goog-menu {font-family:Arial;} .goog-menuitem-content {color: #3F3F3F;} .goog-menuitem-highlight,.goog-menuitem-hover {background-color: #F0F0F0;border-color: #F0F0F0;} .goog-menu-button-focused .goog-menu-button-outer-box,.goog-menu-button-focused .goog-menu-button-inner-box {border-color: #B1B1B1; margin} .goog-menu-button-inner-box {font: 13px/18px Arial, sans-serif; margin:2px 0; padding:0 5px;} .goog-menu-button-hover .goog-menu-button-outer-box,.goog-menu-button-hover .goog-menu-button-inner-box {border-color: #B1B1B1!important;} .goog-menu-button-active,.goog-menu-button-open {background-color: #F0F0F0;border-color: #B1B1B1;background-position: bottom left} .goog-menu-button-focused .goog-menu-button-outer-box,.goog-menu-button-focused .goog-menu-button-inner-box {border-color: #B1B1B1} .google-visualization-controls-slider-horizontal {border: 0px; background-color: #DBDBDB; border-radius: 5px; -moz-border-radius: 5px; outline:none; height:8px;} .google-visualization-controls-slider-thumb {background-color: #616161; border: none;width: 12px; height: 12px; } .google-visualization-controls-slider-horizontal .google-visualization-controls-slider-handle {height: 8px;} .google-visualization-controls-slider-horizontal .google-visualization-controls-slider-thumb {top: -2px; left-margin: 8px; border-radius: 2px; -moz-border-radius: 2px;} .google-visualization-controls-slider-handle {background-color: #616161; opacity: .6; height: 4px} .google-visualization-controls-rangefilter-thumblabel {font: 13px/24px Arial; color: #3F3F3F;padding: 0 0.5em} #page-loader { position: absolute; top: 0; bottom: 0%; left: 0; right: 0%; background-color: white; z-index: 99; display: none; text-align: center; width: 100%; padding-top: 25px; } </style> <script charset="utf-8" type="text/javascript"> $.extend({ getUrlVars: function(){ var vars = [], hash; var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&'); for(var i = 0; i < hashes.length; i++) { hash = hashes[i].split('='); vars.push(hash[0]); vars[hash[0]] = hash[1]; } return vars; }, getUrlVar: function(name){ return $.getUrlVars()[name]; } }); </script> <script type="text/javascript"> anyquery = false; fdnq = decodeURIComponent($.getUrlVar('fdnq')); fdop = decodeURIComponent($.getUrlVar('fdop')); fdtq = decodeURIComponent($.getUrlVar('fdtq')); fdall = decodeURIComponent($.getUrlVar('fdall')); if (fdall != 'undefined') { fdall = true; } else { fdall = false; }; if (fdtq != 'undefined' || fdnq != 'undefined' || fdall == true) { anyquery = true; google.setOnLoadCallback(drawVisualization()); } else { fdtq = 'Enter case-sensitive text'; fdnq = 'Enter a number'; }; function drawVisualization() { var search_url = 'https://spreadsheets.google.com/a/google.com/tq?key=1WDoiQGwoFTxPwQsOBSsCs-B0R4FiW1kaikYVbpEBPak'; if (fdtq == 'undefined') { fdtq = ''; }; if (fdnq == 'undefined') { fdnq = ''; }; if (fdall) { var querystring = "select *"; } else { var querystring = "select A,B,C,D where "; if (fdtq != '' && fdnq == '' ) { querystring = querystring + "D like '%" + fdtq + "%'"; } else if (fdtq == '' && fdnq != '' ) { querystring = querystring + "A" + fdop + fdnq; } else { querystring = querystring + "D like '%" + fdtq + "%'" + " and " + "A" + fdop + fdnq; }; }; querystring = encodeURIComponent(querystring); search_url = search_url + "&tq=" + querystring; search_url = search_url.replace(/#&tq/,'&tq'); var query = new google.visualization.Query(search_url); query.send(handleQueryResponse); } function handleQueryResponse(response) { if (response.isError()) { console.log('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage()); return; } var data = response.getDataTable(); var control1_use = new google.visualization.ControlWrapper({'controlType': 'StringFilter','containerId': 'control1','options': {'filterColumnLabel': 'City', 'matchType':'any', 'ui': {'label':'Filter by City', 'cssClass' : 'custom-stringfilter', 'allowMultiple': false,'allowTyping': false}}}); var control2_use = new google.visualization.ControlWrapper({'controlType': 'StringFilter','containerId': 'control2','options': {'filterColumnLabel': 'Streets', 'matchType':'any', 'ui': {'label':'Filter by Streets','cssClass' : 'custom-stringfilter', 'allowMultiple': false,'allowTyping': false}}}); var classes = {headerRow: 'header', tableRow: 'row', hoverTableRow: 'hoverRow', oddTableRow: 'oddRow', selectedTableRow:'selectedRow', tableCell:'cell', headderCell:'headCell' }; var table = new google.visualization.ChartWrapper({ 'chartType': 'Table', 'containerId': 'chart1', 'options': {'height': '400px', 'cssClassNames': classes, 'width': '460px','allowHtml':'true' } }); var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard')). bind([control1_use , control2_use ], [table ]). draw(data); $('div#dashboard').show(); }; </script>
<div id="container">
<div id="fd_main" style="width: 416px;">
<div class="search_widget" id="full_search" style="float: left; width: 416px;">
<h4>
Search postal codes </h4>
<div class="fd_note">
Text search is case sensitive. Leave fields blank to see the entire database (may cause longer load times).</div>
<label for="search_text">States includes </label> <input id="search_text" onfocus="changeSearchButton1()" value="" />
<div id="num_search_bit">
</div>
<div id="name" style="display: none;">
<input id="range_num" value="" /></div>
<span class="fd_note" style="float: left;"> </span> <input id="submit_button" onclick="fd_refresh()" style="color: black; float: right;" type="submit" value="SEARCH" /> </div>
<div id="page_loader">
</div>
<div id="dashboard" style="float: left; font-family: Arial, sans-serif; width: 460px;">
<h4 style="font-size: 14px !important;">
Explore your results </h4>
<b>Instructions:</b> Use the filter(s) below to customize your search results. Use the tool above to perform a new search.
<div class="control" id="control1">
</div>
<div class="control" id="control2">
</div>
<div class="fd_note" style="margin-bottom: .3em; width: 100%;">
Click on a column label to resort the table.</div>
<div class="chart1" id="chart1">
<div style="color: #772327; font-size: 1.3em; padding: 60px 0 20px; text-align: center !important;">
Fetching data... Thank you for waiting. </div>
<div style="color: #3f3f3f; padding: 20px 0; text-align: center !important;">
Searches with a large number of results may take longer to load.</div>
</div>
</div>
</div>
</div>
<script charset="utf-8" type="text/javascript"> if (anyquery == false) { $('div#dashboard').hide(); }; $('input#search_text').val(fdtq); $('input#range_num').val(fdnq); function changeSearchButton1() { document.getElementById('submit_button').value = 'SEARCH'; }; function fd_refresh() { var fdop = $('#sheet_op').val(); var fdnq = $('#range_num').val(); var fdtq = $('#search_text').val(); var sURL = window.location.href; sURL = sURL.replace(/(fdtq=.*&|fdtq=.*$)/gi,''); sURL = sURL.replace(/(fdnq=.*&|fdnq=.*$)/gi,''); sURL = sURL.replace(/(fdop=.*&|fdop=.*$)/gi,''); sURL = sURL.replace(/(fdall=.*&|fdall=.*$)/gi,''); sURL = sURL.replace(/&$/,''); var qs = ''; var found = sURL.search('\\?'); if (found == -1) { qs = "?"; }; if (fdnq == "Enter a number") {fdnq = ''}; if (fdtq == "Enter case-sensitive text") {fdtq = ''}; if (fdnq == '' && fdtq == '') { var allrecs = true; }; if (allrecs) { qs = qs + "fdall=true" ; } else { qs = qs + "&fdop=" + encodeURIComponent(fdop) + "&fdnq=" + encodeURIComponent(fdnq) + "&fdtq=" + encodeURIComponent(fdtq); }; sURL = sURL + qs; window.location.replace(sURL); } </script> <script charset="utf-8" type="text/javascript"> $(document).ready(function(){ $('input#range_num').focus(function() { if($(this).val() == 'Enter a number') $(this).val(''); }).blur(function() { if( $(this).val() == '') $(this).val('Enter a number'); }); $('input#search_text').focus(function() { if($(this).val() == 'Enter case-sensitive text') $(this).val(''); }).blur(function() { if( $(this).val() == '') $(this).val('Enter case-sensitive text'); }); if (anyquery == false) { document.getElementById('submit_button').value='SEE ALL RECORDS';} else{ document.getElementById('submit_button').value='SEARCH'; }; }); </script>
<u>How to use the Postal Search!</u></h3>
<ul>
<li>Search the codes by states with the first letter is capital i.e. Sarawak</li>
<li>Use the "Filter by City and Filter by Street" - it will automatically narrow down search results</li>
<li>Use the following keywords to search Malaysia Postal Codes by States</li>
</ul>
<div>
Johor | Kedah | Kelantan | Kuala Lumpur | Labuan | Melaka | Negeri Sembilan | Pahang | Putrajaya | Perlis | Pulau Pinang | Perak | Sabah | Selangor | Sarawak | Terengganu |</div>
<hr />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js" type="text/javascript"></script> <script src="https://www.google.com/jsapi" type="text/javascript"></script> <script type="text/javascript"> google.load('visualization', '1.0', {packages:['controls']}); </script> <style type="text/css"> #fd_main p {font: 13px/16px Arial; margin-bottom:10px !important; padding:0;} #fd_main h4 {font-family:Arial; font-weight:bold; text-transform:uppercase;text-align:left; padding:0 0 5px 0; margin:0;} #fd_main label {font: bold 13px/24px Arial; padding: 0 5px 0 0; float:left; vertical-align: middle;} #fd_main input, textarea, select, button {font: 13px/16px Arial; color:#959595; vertical-align: middle;} .fd_note{font-family:Arial;color:#5E5E5E; font-size:.8em; border:none; padding:0;} div.search_widget{background:#E1E1E1;background:-webkit-gradient(linear,left top, left bottom,from(#FFF),to(#E1E1E1));background:-webkit-linear-gradient(top,#FFF,#E1E1E1);background:-moz-linear-gradient(top,#FFF,#E1E1E1);background:-ms-linear-gradient(top,#FFF,#E1E1E1);background:-o-linear-gradient(top,#FFF,#E1E1E1);border:2px solid #E1E1E1;padding:20px;-moz-border-radius:10px;-webkit-border-radius:10px; margin-bottom:20px;} div.control{margin-bottom: 15px; color:black;height:26px;} div.chart1{float: left;} .header {background-color: #5B5B5B; color: #FFFFFF; font-family:Arial, sans-serif; text-align:left; font-size:0.8em; text-transform:uppercase; font-weight:600; letter-spacing:1px; line-height:1.4em; border:none;} .row { background-color: #FFFFFF; color: #3F3F3F; font-family:inherit; font-size:0.9em;} .oddRow { background-color: #F5F5F5; color: #3F3F3F; font-family:inherit; text-align:left; font-size:0.9em;} .hoverRow { background-color: #CDCDCD; font-family:inherit; text-align:left; font-size:0.9em; } .selectedRow { background-color: #DCDCDC; color: #3F3F3F; text-align: center; font-family:inherit; text-align:left; font-size:0.9em; } .cell, table {padding:2px;border:none;} .headCell {padding:0;} .goog-menu {font-family:Arial;} .goog-menuitem-content {color: #3F3F3F;} .goog-menuitem-highlight,.goog-menuitem-hover {background-color: #F0F0F0;border-color: #F0F0F0;} .goog-menu-button-focused .goog-menu-button-outer-box,.goog-menu-button-focused .goog-menu-button-inner-box {border-color: #B1B1B1; margin} .goog-menu-button-inner-box {font: 13px/18px Arial, sans-serif; margin:2px 0; padding:0 5px;} .goog-menu-button-hover .goog-menu-button-outer-box,.goog-menu-button-hover .goog-menu-button-inner-box {border-color: #B1B1B1!important;} .goog-menu-button-active,.goog-menu-button-open {background-color: #F0F0F0;border-color: #B1B1B1;background-position: bottom left} .goog-menu-button-focused .goog-menu-button-outer-box,.goog-menu-button-focused .goog-menu-button-inner-box {border-color: #B1B1B1} .google-visualization-controls-slider-horizontal {border: 0px; background-color: #DBDBDB; border-radius: 5px; -moz-border-radius: 5px; outline:none; height:8px;} .google-visualization-controls-slider-thumb {background-color: #616161; border: none;width: 12px; height: 12px; } .google-visualization-controls-slider-horizontal .google-visualization-controls-slider-handle {height: 8px;} .google-visualization-controls-slider-horizontal .google-visualization-controls-slider-thumb {top: -2px; left-margin: 8px; border-radius: 2px; -moz-border-radius: 2px;} .google-visualization-controls-slider-handle {background-color: #616161; opacity: .6; height: 4px} .google-visualization-controls-rangefilter-thumblabel {font: 13px/24px Arial; color: #3F3F3F;padding: 0 0.5em} #page-loader { position: absolute; top: 0; bottom: 0%; left: 0; right: 0%; background-color: white; z-index: 99; display: none; text-align: center; width: 100%; padding-top: 25px; } </style> <script charset="utf-8" type="text/javascript"> $.extend({ getUrlVars: function(){ var vars = [], hash; var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&'); for(var i = 0; i < hashes.length; i++) { hash = hashes[i].split('='); vars.push(hash[0]); vars[hash[0]] = hash[1]; } return vars; }, getUrlVar: function(name){ return $.getUrlVars()[name]; } }); </script> <script type="text/javascript"> anyquery = false; fdnq = decodeURIComponent($.getUrlVar('fdnq')); fdop = decodeURIComponent($.getUrlVar('fdop')); fdtq = decodeURIComponent($.getUrlVar('fdtq')); fdall = decodeURIComponent($.getUrlVar('fdall')); if (fdall != 'undefined') { fdall = true; } else { fdall = false; }; if (fdtq != 'undefined' || fdnq != 'undefined' || fdall == true) { anyquery = true; google.setOnLoadCallback(drawVisualization()); } else { fdtq = 'Enter case-sensitive text'; fdnq = 'Enter a number'; }; function drawVisualization() { var search_url = 'https://spreadsheets.google.com/a/google.com/tq?key=1WDoiQGwoFTxPwQsOBSsCs-B0R4FiW1kaikYVbpEBPak'; if (fdtq == 'undefined') { fdtq = ''; }; if (fdnq == 'undefined') { fdnq = ''; }; if (fdall) { var querystring = "select *"; } else { var querystring = "select A,B,C,D where "; if (fdtq != '' && fdnq == '' ) { querystring = querystring + "D like '%" + fdtq + "%'"; } else if (fdtq == '' && fdnq != '' ) { querystring = querystring + "A" + fdop + fdnq; } else { querystring = querystring + "D like '%" + fdtq + "%'" + " and " + "A" + fdop + fdnq; }; }; querystring = encodeURIComponent(querystring); search_url = search_url + "&tq=" + querystring; search_url = search_url.replace(/#&tq/,'&tq'); var query = new google.visualization.Query(search_url); query.send(handleQueryResponse); } function handleQueryResponse(response) { if (response.isError()) { console.log('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage()); return; } var data = response.getDataTable(); var control1_use = new google.visualization.ControlWrapper({'controlType': 'StringFilter','containerId': 'control1','options': {'filterColumnLabel': 'City', 'matchType':'any', 'ui': {'label':'Filter by City', 'cssClass' : 'custom-stringfilter', 'allowMultiple': false,'allowTyping': false}}}); var control2_use = new google.visualization.ControlWrapper({'controlType': 'StringFilter','containerId': 'control2','options': {'filterColumnLabel': 'Streets', 'matchType':'any', 'ui': {'label':'Filter by Streets','cssClass' : 'custom-stringfilter', 'allowMultiple': false,'allowTyping': false}}}); var classes = {headerRow: 'header', tableRow: 'row', hoverTableRow: 'hoverRow', oddTableRow: 'oddRow', selectedTableRow:'selectedRow', tableCell:'cell', headderCell:'headCell' }; var table = new google.visualization.ChartWrapper({ 'chartType': 'Table', 'containerId': 'chart1', 'options': {'height': '400px', 'cssClassNames': classes, 'width': '460px','allowHtml':'true' } }); var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard')). bind([control1_use , control2_use ], [table ]). draw(data); $('div#dashboard').show(); }; </script>
<div id="container">
<div id="fd_main" style="width: 416px;">
<div class="search_widget" id="full_search" style="float: left; width: 416px;">
<h4>
Search postal codes </h4>
<div class="fd_note">
Text search is case sensitive. Leave fields blank to see the entire database (may cause longer load times).</div>
<label for="search_text">States includes </label> <input id="search_text" onfocus="changeSearchButton1()" value="" />
<div id="num_search_bit">
</div>
<div id="name" style="display: none;">
<input id="range_num" value="" /></div>
<span class="fd_note" style="float: left;"> </span> <input id="submit_button" onclick="fd_refresh()" style="color: black; float: right;" type="submit" value="SEARCH" /> </div>
<div id="page_loader">
</div>
<div id="dashboard" style="float: left; font-family: Arial, sans-serif; width: 460px;">
<h4 style="font-size: 14px !important;">
Explore your results </h4>
<b>Instructions:</b> Use the filter(s) below to customize your search results. Use the tool above to perform a new search.
<div class="control" id="control1">
</div>
<div class="control" id="control2">
</div>
<div class="fd_note" style="margin-bottom: .3em; width: 100%;">
Click on a column label to resort the table.</div>
<div class="chart1" id="chart1">
<div style="color: #772327; font-size: 1.3em; padding: 60px 0 20px; text-align: center !important;">
Fetching data... Thank you for waiting. </div>
<div style="color: #3f3f3f; padding: 20px 0; text-align: center !important;">
Searches with a large number of results may take longer to load.</div>
</div>
</div>
</div>
</div>
<script charset="utf-8" type="text/javascript"> if (anyquery == false) { $('div#dashboard').hide(); }; $('input#search_text').val(fdtq); $('input#range_num').val(fdnq); function changeSearchButton1() { document.getElementById('submit_button').value = 'SEARCH'; }; function fd_refresh() { var fdop = $('#sheet_op').val(); var fdnq = $('#range_num').val(); var fdtq = $('#search_text').val(); var sURL = window.location.href; sURL = sURL.replace(/(fdtq=.*&|fdtq=.*$)/gi,''); sURL = sURL.replace(/(fdnq=.*&|fdnq=.*$)/gi,''); sURL = sURL.replace(/(fdop=.*&|fdop=.*$)/gi,''); sURL = sURL.replace(/(fdall=.*&|fdall=.*$)/gi,''); sURL = sURL.replace(/&$/,''); var qs = ''; var found = sURL.search('\\?'); if (found == -1) { qs = "?"; }; if (fdnq == "Enter a number") {fdnq = ''}; if (fdtq == "Enter case-sensitive text") {fdtq = ''}; if (fdnq == '' && fdtq == '') { var allrecs = true; }; if (allrecs) { qs = qs + "fdall=true" ; } else { qs = qs + "&fdop=" + encodeURIComponent(fdop) + "&fdnq=" + encodeURIComponent(fdnq) + "&fdtq=" + encodeURIComponent(fdtq); }; sURL = sURL + qs; window.location.replace(sURL); } </script> <script charset="utf-8" type="text/javascript"> $(document).ready(function(){ $('input#range_num').focus(function() { if($(this).val() == 'Enter a number') $(this).val(''); }).blur(function() { if( $(this).val() == '') $(this).val('Enter a number'); }); $('input#search_text').focus(function() { if($(this).val() == 'Enter case-sensitive text') $(this).val(''); }).blur(function() { if( $(this).val() == '') $(this).val('Enter case-sensitive text'); }); if (anyquery == false) { document.getElementById('submit_button').value='SEE ALL RECORDS';} else{ document.getElementById('submit_button').value='SEARCH'; }; }); </script>
No comments:
Post a Comment