Skip to content

Instantly share code, notes, and snippets.

@drbobbeaty
Created September 25, 2012 13:57
Show Gist options
  • Save drbobbeaty/3782074 to your computer and use it in GitHub Desktop.
Save drbobbeaty/3782074 to your computer and use it in GitHub Desktop.
Example of creating a CSV download from Google Visualization Table
<html>
<head>
<title>Unpinned Merchants</title>
<script type='text/javascript' src='https://www.google.com/jsapi'></script>
<script type='text/javascript' src='zingchart/resources/jquery.min.js'></script>
<script type='text/javascript'>
google.load('visualization', '1', {packages:['table']});
google.setOnLoadCallback(reload_executions);
// set up the fixed locations and paths for this metric visualization.
// we need to be able to pick the server (prod, uat, dev).
var view_loc = '_design/pinning/_view/unpinned';
var opts = '&reduce=false'
// get the date a week ago formatted as YYYY-MM-DD
var when = new Date();
when.setDate(when.getDate() - 7);
var weekAgo = when.getFullYear()+'-'
+('0'+(when.getMonth()+1)).substr(-2,2)+'-'
+('0'+when.getDate()).substr(-2,2);
// these will be the data sets we can get from the selected database
var divisions = new Array();
var runtimes = new Object();
// this is the Google DataTable we'll be creating each time
var dtable = null;
// the Google Table needs to have a few config parameters to make it
// look like we want it to look.
var table_config = {
showRowNumber: true,
width: '700px',
height: '503px'
};
// we can use this format spec to format the sales value column once
// we have loaded up the table.
var sv_format = {
prefix: '$',
pattern: '#,###.00'
};
// This method looks at the selected data set and loads that into
// a new table for the target div and redraws it.
function render(tbl) {
// save this data table for later
dtable = tbl;
// now create a Google Table and populate it with this data
var dest = document.getElementById('table_div');
var table = new google.visualization.Table(dest);
table.draw(tbl, table_config);
}
// this downloads the current data table as a CSV file to the client
function toCSV() {
var data = dtable;
var csvData = [];
var tmpArr = [];
var tmpStr = '';
for (var i = 0; i < data.getNumberOfColumns(); i++) {
// replace double-quotes with double-double quotes for CSV compatibility
tmpStr = data.getColumnLabel(i).replace(/"/g, '""');
tmpArr.push('"' + tmpStr + '"');
}
csvData.push(tmpArr);
for (var i = 0; i < data.getNumberOfRows(); i++) {
tmpArr = [];
for (var j = 0; j < data.getNumberOfColumns(); j++) {
switch(data.getColumnType(j)) {
case 'string':
// replace double-quotes with double-double quotes for CSV compat
tmpStr = data.getValue(i, j).replace(/"/g, '""');
tmpArr.push('"' + tmpStr + '"');
break;
case 'number':
tmpArr.push(data.getValue(i, j));
break;
case 'boolean':
tmpArr.push((data.getValue(i, j)) ? 'True' : 'False');
break;
case 'date':
// decide what to do here, as there is no universal date format
break;
case 'datetime':
// decide what to do here, as there is no universal date format
break;
case 'timeofday':
// decide what to do here, as there is no universal date format
break;
default:
// should never trigger
}
}
csvData.push(tmpArr.join(','));
}
var output = csvData.join('\n');
var uri = 'data:application/csv;charset=UTF-8,' + encodeURIComponent(output);
window.open(uri);
}
// This function takes the data coming from CouchDB and formats it
// into a series of nice DataTable objects for Google's tools.
// There will be one set per run (execution_tag), and we'll organize
// it that way for easy retrieval.
function parse_series(data) {
var table = new google.visualization.DataTable();
table.addColumn('string', 'Merchant');
table.addColumn('string', 'Category');
for(var i in data.rows) {
table.addRow([data.rows[i].value.name,
data.rows[i].value.taxonomy.category]);
}
return table;
}
// This method simply hits the selected database (on the server)
// for the proper CouchDB view, and then processes it into a series
// of ZingCharts data sets that we then render the first one.
function reload() {
// hit CouchDB for the view we need to process
var svr_opt = document.getElementById('server_opt');
var div_opt = document.getElementById('division_opt');
var run_opt = document.getElementById('run_opt');
var et = run_opt.value + '-' + div_opt.value;
var url = svr_opt.value + '/' + view_loc + '?' +
'startkey=' + encodeURI(JSON.stringify([et])) +
'&endkey=' + encodeURI(JSON.stringify([et,{}])) + opts + '&callback=?';
$.getJSON(url, function(data) {
var series = parse_series(data);
render(series);
});
}
// When we change divisions we need to update the available run times
// for the new division, and in order to do that, we have this method.
function set_runs_for_division(division) {
division = (typeof(division) !== 'undefined' ? division : document.getElementById('division_opt').value);
runtimes[division].sort();
runtimes[division].reverse();
var run_opt = document.getElementById('run_opt');
run_opt.options.length = 0;
for (var i in runtimes[division]) {
var tag = runtimes[division][i];
run_opt.options[run_opt.options.length] = new Option(tag, tag);
}
// at this point, call back to the the data we need, and then render it
reload();
}
// This function takes the list of executions currently loaded on the database
// and parses their 'execution_tag's into divisions and times and places them
// in the datastructre to make it much easier to manipulate.
function parse_execution_tags(data) {
divisions = new Array();
runtimes = new Object();
for(var i in data.rows) {
// get the execution_tag and exclude the very early ones
var exec_tag = data.rows[i].key;
if (!/-\D+$/i.test(exec_tag) || (exec_tag.substring(0,10) < weekAgo)) {
continue;
}
// now get the timestamp and division from the execution_tag
var runtime = exec_tag.replace(/-\D+/g, '');
var division = exec_tag.replace(/^.*\.\d\d\d-/g, '');
if (typeof(runtimes[division]) == 'undefined') {
runtimes[division] = new Array();
divisions.push(division);
}
runtimes[division].push(runtime);
}
// sort the divisions and create the contents of the drop-down
if (divisions.length > 0) {
divisions.sort();
var div_opt = document.getElementById('division_opt');
div_opt.options.length = 0;
for (var d in divisions) {
div_opt.options[div_opt.options.length] = new Option(divisions[d], divisions[d]);
}
}
// given the default division, load up the run times we just parsed
set_runs_for_division(divisions[0]);
}
// When we change a database, we need to reload all the known run (executions)
// that exist on that database. Then, we can populate the 'division' and 'run'
// in a nested datastructure so that it's each to update the run times for a
// given division.
function reload_executions() {
// hit CouchDB for the view of all executions it knowns about
var svr_opt = document.getElementById('server_opt');
var url = svr_opt.value + '/_design/general/_view/executions?descending=true&callback=?';
$.getJSON(url, function(data) {
parse_execution_tags(data);
});
}
</script>
</head>
<body>
<p align="center">
Database:
<select id="server_opt" onchange="reload()">
<option value='/db/production' selected="selected">Production</option>
<option value='/db/uat'>UAT</option>
<option value='/db/dev'>Dev on UAT</option>
</select>
Division:
<select id="division_opt" onchange="set_runs_for_division(this.value)">
</select>
Run:
<select id="run_opt" onchange="reload()">
</select>
</p>
<div id='table_div' style="width:700px; margin-top:10px; margin-left:auto; margin-right:auto;"></div>
<p align="center">
<input type="button" id="toCSV" value="Click to download data as CSV" onclick="toCSV()" />
</p>
</body>
</html>
@fatso83
Copy link

fatso83 commented Oct 3, 2013

This fails in Chrome. Not sure why jQuery balks, but this is the result "Unexpected token <"

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