Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save hayatbiralem/eb25df49899cd1f97616ce72253724e0 to your computer and use it in GitHub Desktop.
Save hayatbiralem/eb25df49899cd1f97616ce72253724e0 to your computer and use it in GitHub Desktop.
Parse published (public) Google Spreadsheet HTML data into Javascript array
// Thanks to this gist: [Sample HTML/JS to parse a Google Spreadsheet](https://gist.github.com/terrywbrady/a03b25fe42959b304b1e)
var htmlEndPoint = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vREXBtuL2uKofq9ufXsIlytbONUR0-q_tf1Ucm14JpeE5KAdry97CCwvivf3e5NkCAnZ1Xg4qYa0RCo/pubhtml';
(function($){
var parseHtmlTableToJson = function($table){
var data = [];
var $headers = $("tr:first-child td", $table);
var headers = [];
$headers.each(function(){
headers.push($(this).text().trim());
});
var $rows = $("tr:first-child ~ tr", $table).each(function(index) {
data[index] = {};
$(this).find("td").each(function(cellIndex) {
data[index][headers[cellIndex]] = $(this).html();
});
});
return data;
};
var parseGoogleSpreadsheetHTMLIntoArray = function (response){
var sheets = {};
var bodyHtml = /<body.*?>([\s\S]*)<\/body>/.exec(response)[1];
console.log(bodyHtml);
var $bodyHtml = $(bodyHtml);
$bodyHtml.find('#sheet-menu a').each(function(index){
sheets[$(this).text().trim()] = parseHtmlTableToJson($bodyHtml.find('table').eq(index));
});
return sheets;
};
$(document).ready(function(){
$.ajax( htmlEndPoint )
.done(function(response) {
// console.log(response);
var sheets = parseGoogleSpreadsheetHTMLIntoArray(response);
console.log(sheets);
});
});
})(jQuery);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment