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?

score 7 · Accepted Answer · answered Jan 30 '18 at 00:01

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!) — Lucien S., Jan 30 '18 at 15:24

Parse a Google spreadsheet into a Javascript array

1 Answers1

Linked