patlachance/GoogleSpreadsheet.html

## README.md

      
    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

  
## 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>
	<!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>