Skip to content

Instantly share code, notes, and snippets.

@terrywbrady

terrywbrady/GoogleSpreadsheet.html

Last active July 19, 2024 10:46
Show Gist options
  • Save terrywbrady/a03b25fe42959b304b1e to your computer and use it in GitHub Desktop.
Save terrywbrady/a03b25fe42959b304b1e to your computer and use it in GitHub Desktop.
Download ZIP
Sample HTML/JS to parse a Google Spreadsheet
Raw
README.md

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

Raw
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>
@hermionewy
Copy link

hermionewy commented Jan 25, 2018 •
edited
Loading

This works perfectly! Thanks!

@bosz
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
Copy link

123linda commented May 9, 2018 •
edited
Loading

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

@flathtml
Copy link

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

@CMOS82
Copy link

CMOS82 commented Jul 27, 2020 •
edited
Loading

Thanks a lot for your wonderful code. It is written in a way that you can understand the reason behind coding each line, even it is my first time to read the JQuey.

Thanks a lot.

@Smarttechex
Copy link

Thanks a lot. This is what I am looking for But if I try publishing another worksheet (like Sheet2!), the code doesn't works.. I assume I need to tweak the link but tried adding gid, however not worked. Can you help me..

@cb17666
Copy link

cb17666 commented Jan 24, 2021

code worked fine for me, thanks, I have been looking for something simmilar for a while. I can now reverse engener it and complete my database! Thanks so much! :)

@cb17666
Copy link

cb17666 commented Jan 24, 2021 •
edited
Loading

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

copy necessary code and change names of functions and stuff? might work, not sure.

@entenza
Copy link

entenza commented Jul 22, 2021

Congratulations... This worked well for me... I do apreciate this a lot.

@entenza
Copy link

entenza commented Jul 22, 2021

Thanks a lot for your wonderful code. It is written in a way that you can understand the reason behind coding each line, even it is my first time to read the JQuey.

Thanks a lot.

I agree with this...

@fm9610
Copy link

fm9610 commented Aug 18, 2021

The code doesn't work now. Is it because Google has ended support of Google sheet API v3 and json?

@Mendelyeev
Copy link

Mendelyeev commented Aug 21, 2021 •
edited
Loading

@fm916 I think so. I was using this code until today, when I have noticed that code isn't working anymore. When i put the example link on webbrowser:
https://spreadsheets.google.com/feeds/cells/1hbHqrnYa4oMUquZcq8WkTJk0kI0t9scGBwro-EH-ALA/1/public/values?alt=json-in-script&callback=doData returns this:
image

Not the json data anymore! :(

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