Parse a Google spreadsheet into a Javascript array

Question

I have a Google spreadsheet (https://docs.google.com/spreadsheets/d/e/2PACX-1vRc8Lx0N-wf3f1xAAXkNFUqQjaWPFcde3YjK02gCBqGpUrULwHC6NC0sndeLJBvOyKkA88hvtH335pR/pubhtml) 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:

<html>
  <head>
  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
  <script>
    google.charts.load('current', { 'packages': ['map'] });
    google.charts.setOnLoadCallback(drawMap);

    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);
  };
  </script>
  </head>
  <body>
    <div id="chart_div"></div>
  </body>
</html>

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>
<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);
	  //console.log(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/1TTeG6mp2rb61Yxi5KO3GFmZ3qQ3RAMlB9bisLciuj-M/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>

...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?

Z-Bone · Accepted Answer · 2018-01-30 00:01:44Z

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 = 'https://spreadsheets.google.com/feeds/cells/1TTeG6mp2rb61Yxi5KO3GFmZ3qQ3RAMlB9bisLciuj-M/1/public/values?alt=json-in-script&callback=doData';


// 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.gs$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  
            newRow.push(text);

            // store the new row array in the final results array
            results.push(newRow);

            // Increment the previous row, since we added a new row to the final results array
            previousRow++;
        } else {
            // This cell is in an existing row we already added to the results array, add text to this existing row
            latestRow.push(text);
        }

    }

    handleResults(results);
}

// Do what ever you please with the final array
function handleResults(spreadsheetArray) {
    console.log(spreadsheetArray);
}



// Create JSONP Request to Google Docs API, then execute the callback function doData
$.ajax({
    url: spreadsheetUrl,
    jsonp: 'doData',
    dataType: 'jsonp'
});

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>

This works perfectly, thanks so much @Z-Bone! (and for the comments as well, wow!)

Collectives™ on Stack Overflow

Parse a Google spreadsheet into a Javascript array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related