Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Sample HTML/JS to parse a Google Spreadsheet

Create a Google Spreadsheet:

The Spreadsheet URL will look like this: https://docs.google.com/a/georgetown.edu/spreadsheets/d/<ID>/edit#gid=0

Sample: https://docs.google.com/a/georgetown.edu/spreadsheets/d/1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA/edit#gid=0

Note that the id of this spreadsheet is 1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA

Share the spreadsheet if others will collaborate on the document

The sharing permissions affect the viewing/editing of the spreadsheet. To programmatically access the data in the spreadsheet, publish the spreadsheet. Note that published spreadsheet data is accessible regardless of the sharing settings.

Publish the spreadsheet

The Published URL will look like this: https://docs.google.com/spreadsheets/d/<ID>/pubhtml

Sample: https://docs.google.com/spreadsheets/d/1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA/pubhtml

Access the data in the spreadsheet as XML/RSS

The Published RSS URL will look like this: https://spreadsheets.google.com/feeds/cells/<ID>/1/public/values

Sample: https://spreadsheets.google.com/feeds/cells/1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA/1/public/values

Access the data as JSON feed

The Published JSON URL will look like this: https://spreadsheets.google.com/feeds/cells/<ID>/1/public/values?alt=json-in-script

Sample: https://spreadsheets.google.com/feeds/cells/1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA/1/public/values?alt=json-in-script

Add a JavaScript callback to process the JSON feed

The Published JSON URL + callback will look like this: https://spreadsheets.google.com/feeds/cells/<ID>/1/public/values?alt=json-in-script&callback=doData

Sample: https://spreadsheets.google.com/feeds/cells/1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA/1/public/values?alt=json-in-script&callback=doData

View the spreadsheet parsed by JavaScript

https://rawgit.com/Georgetown-University-Libraries/Georgetown-University-Libraries-Code/master/samples/GoogleSpreadsheet.html

Code example

https://github.com/Georgetown-University-Libraries/Georgetown-University-Libraries-Code/blob/master/samples/GoogleSpreadsheet.html

<!doctype html>
<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script type="text/javascript">
var spData = null;
function doData(json) {
spData = json.feed.entry;
}
function drawCell(tr, val) {
var td = $("<td/>");
tr.append(td);
td.append(val);
return td;
}
function drawRow(table, rowData) {
if (rowData == null) return null;
if (rowData.length == 0) return null;
var tr = $("<tr/>");
table.append(tr);
for(var c=0; c<rowData.length; c++) {
drawCell(tr, rowData[c]);
}
return tr;
}
function drawTable(parent) {
var table = $("<table/>");
parent.append(table);
return table;
}
function readData(parent) {
var data = spData;
var table = drawTable(parent);
var rowData = [];
for(var r=0; r<data.length; r++) {
var cell = data[r]["gs$cell"];
var val = cell["$t"];
if (cell.col == 1) {
drawRow(table, rowData);
rowData = [];
}
rowData.push(val);
}
drawRow(table, rowData);
}
$(document).ready(function(){
readData($("#data"));
});
</script>
<script src="https://spreadsheets.google.com/feeds/cells/1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA/1/public/values?alt=json-in-script&callback=doData"></script>
<style type="text/css">
table {border-collapse: collapse; width: 100%;}
th, td {border: thin solid black; padding: 3px;}
tr.head th, tr.head td {background-color: #EDEDED; border-bottom: 4px double black;}
span.linetitle {font-weight: bold;}
div.lineclass {font-style: italic;}
.title, .result {width: 80%;}
.notes {width: 15%;}
h1 {text-align: center;}
body {margin: 12px; font-size: 12px;}
</style>
<style type="text/css" media="print">
form {display: none;}
</style>
</head>
<body>
<h1>Parse Google Spreadsheet with JavaScript</h1>
<div id="data"/>
</body>
</html>
@cristacheda

This comment has been minimized.

Copy link

cristacheda commented Dec 12, 2015

How can I get two tables (two spreadsheets) on a single page?

@huzly

This comment has been minimized.

Copy link

huzly commented Mar 1, 2017

the above codes isnt working :(

@drabello

This comment has been minimized.

Copy link

drabello commented Mar 29, 2017

Nice man! Great script, working perfectly here.

@Tokinabo

This comment has been minimized.

Copy link

Tokinabo commented Aug 17, 2017

Thx. Nice work, works perfectly here too.

@hermionewy

This comment has been minimized.

Copy link

hermionewy commented Jan 25, 2018

This works perfectly! Thanks!

@bosz

This comment has been minimized.

Copy link

bosz commented Feb 12, 2018

Hi @terrybrady . Is there a way I can limit the the rows? Say i have A1 - A2000 and i want to extract only from A1990 - A2000 how can that be done please?

@123linda

This comment has been minimized.

Copy link

123linda commented May 9, 2018

Can anyone help me, how i can add a header row and manipulate the column thickness in %?

@flathtml

This comment has been minimized.

Copy link

flathtml commented Sep 27, 2018

Hi,
Thank you so much for creating this code. I was able to easily reuse it for reading Google sheet data

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.