Create a gist now

Instantly share code, notes, and snippets.

@oodavid /index.html
Last active Dec 18, 2015

What would you like to do?
Convert Google Spreadsheeets to JSON
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Google Spreadsheet to JSON...</title>
<script src="http://cdnjs.cloudflare.com/ajax/libs/jquery/2.0.2/jquery.min.js"></script>
<script src="http://jquery-csv.googlecode.com/files/jquery.csv-0.71.min.js"></script>
<script>
// Which file to load?
var csv = 'https://docs.google.com/spreadsheet/pub?key=0AmqTMmxb4MxEdE9vUHVPdWs1WjlJR1JlbFJubi14VkE&single=true&gid=0&output=csv';
// How many rows to skip (if you have anything ABOVE the column definitions)
var skip = 2;
// Map your spreadsheet columns to JSON keys
var column_keys = [
{ 'column': 'Date', 'key': 'date' },
{ 'column': 'Meal', 'key': 'meal' },
{ 'column': 'Notes', 'key': 'comments' }
];
// When your ready, load the CSV...
$(document).ready(function(e){
$.get(csv, function(data){
// Format the data into arrays
data = $.csv.toArrays(data);
// Skip columns
for(var s=0; s<skip; s++){
data.shift();
}
// We translate the columns into json keys
// The remote columns must match exactly to make sure that the structure is exactly as we expect it to be!
for(var t=0, tl=column_keys.length; t<tl; t++){
if(column_keys[t]['column'] != data[0][t]){
alert('== Error - Spreadheet Titles ==\nLOCAL "' + column_keys[t]['column'] + '" != REMOTE "' + data[0][t] + '"');
return false;
}
}
// Splendid, remove the remote titles
data.shift();
// Loop the data...
var rows = [];
for(var d=0, dl=data.length; d<dl; d++){
var datum = data[d];
// If we hit the "DO NOT IMPORT" columns we're done...
if(datum[0] == "DO NOT IMPORT" || !datum[0] || datum[0] == ""){
break;
}
// Format it into a structured object
var row = {};
for(var t=0, tl=column_keys.length; t<tl; t++){
row[column_keys[t]['key']] = datum[t];
}
// Set the row
rows.push(row);
}
// And output
$('textarea').text('var rows = ' + JSON.stringify(rows, null, '\t') + ';');
});
});
</script>
<style>
body {font-family: sans-serif; text-align: center;}
textarea {width: 600px; height: 400px; display: block; margin: 10px auto; white-space: pre; word-wrap: normal; overflow-x: scroll;}
</style>
</head>
<body>
<h1>Google Spreadsheet to JSON...</h1>
<p>Turns <a href="https://docs.google.com/a/oodavid.com/spreadsheet/ccc?key=0AmqTMmxb4MxEdE9vUHVPdWs1WjlJR1JlbFJubi14VkE#gid=0" target="_blank">this spreadsheet</a>... into this JSON object...</p>
<textarea></textarea>
<p>See this <a href="http://oodavid.com/2013/06/18/converting-google-spreadsheeets-to-json.html">oodavid.com article</a> for more info</p>
</body>
</html>

hueitan commented Mar 4, 2015

This doesn't work for me =(

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