Parse a Google spreadsheet into a Javascript array

I have a Google spreadsheet ( which I’d like to access in a webpage to use with Google Maps API.

As per Google API’S documentation, the script should look like this:

  <script type="text/javascript" src=""></script>
    google.charts.load('current', { 'packages': ['map'] });

    function drawMap() {
      var data = google.visualization.arrayToDataTable([
        ['Country', 'Population'],
        ['China', 'China: 1,363,800,000'],
        ['India', 'India: 1,242,620,000'],
        ['US', 'US: 317,842,000'],
        ['Indonesia', 'Indonesia: 247,424,598'],
        ['Brazil', 'Brazil: 201,032,714'],
        ['Pakistan', 'Pakistan: 186,134,000'],
        ['Nigeria', 'Nigeria: 173,615,000'],
        ['Bangladesh', 'Bangladesh: 152,518,015'],
        ['Russia', 'Russia: 146,019,512'],
        ['Japan', 'Japan: 127,120,000']

    var options = {
      showTooltip: true,
      showInfoWindow: true

    var map = new google.visualization.Map(document.getElementById('chart_div'));

    map.draw(data, options);
    <div id="chart_div"></div>

So my goal is to dynamically replace:

        ['Country', 'Population'],
        ['China', '1,363,800,000'],
        ['India', '1,242,620,000'],
        ['US', '317,842,000'],
        ['Indonesia', '247,424,598'],
        ['Brazil', '201,032,714'],
        ['Pakistan', '186,134,000'],
        ['Nigeria', '173,615,000'],
        ['Bangladesh', '152,518,015'],
        ['Russia', 'Russia: 146,019,512'],
        ['Japan', 'Japan: 127,120,000']

…with the content of the Google Spreadsheet.

I am new at JS, and I’m struggling to properly convert an exported JSON from Google Spreadsheets into a JS array. Searching around, I stumbled upon an interresting script:

<!doctype html>
<script src="//"></script>
<script type="text/javascript">
	var spData = null;
	function doData(json) {
			spData = json.feed.entry;
	function drawCell(tr, val) {
			var td = $("<td/>");
			return td;
	function drawRow(table, rowData) {
	  if (rowData == null) return null;
	  if (rowData.length == 0) return null;
	  var tr = $("<tr/>");
	  for(var c=0; c<rowData.length; c++) {
		  drawCell(tr, rowData[c]);
	  return tr;
	function drawTable(parent) {
	  var table = $("<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 = [];
			drawRow(table, rowData);

<script src=""></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 type="text/css" media="print">
	form {display: none;}
<h1>Parse Google Spreadsheet with JavaScript</h1> 
<div id="data"/>

…which fetches the spreadsheet, and turns it into a HTML table. However, I can’t seem to find a way to build an array from this.

After this long context, here’s my question: how can I fetch the Google spreadsheet to insert it as data in the drawMap function above?


The Google spreadsheet API you are using is supposed to be called with JSONP.

I used jQuery’s simple implementation of JSONP via the $.ajax function.

You can see my solution with line by line explanations:

var spreadsheetUrl = '';

// The callback function the JSONP request will execute to load data from API
function doData(data) {
    // Final results will be stored here	
    var results = [];

    // Get all entries from spreadsheet
    var entries = data.feed.entry;

    // Set initial previous row, so we can check if the data in the current cell is from a new row
    var previousRow = 0;

    // Iterate all entries in the spreadsheet
    for (var i = 0; i < entries.length; i++) {
        // check what was the latest row we added to our result array, then load it to local variable
        var latestRow = results[results.length - 1];

        // get current cell
        var cell = entries[i];

        // get text from current cell
        var text = cell.content.$t;

        // get the current row
        var row =$cell.row;

        // Determine if the current cell is in the latestRow or is a new row
        if (row > previousRow) {
            // this is a new row, create new array for this row
            var newRow = [];

            // add the cell text to this new row array  

            // store the new row array in the final results array

            // Increment the previous row, since we added a new row to the final results array
        } else {
            // This cell is in an existing row we already added to the results array, add text to this existing row



// Do what ever you please with the final array
function handleResults(spreadsheetArray) {

// Create JSONP Request to Google Docs API, then execute the callback function doData
    url: spreadsheetUrl,
    jsonp: 'doData',
    dataType: 'jsonp'
<script src=""></script>

Leave a Reply

Your email address will not be published. Required fields are marked *