CyberDav CyberDav - 3 months ago 26
Javascript Question

How to use Google visualization API to retrieve google spreadsheet cell value?

I'm trying to get the value of a cell on a google spreadsheet but it doesn't seem to work.

Here is my javascript code:

<html>





<script>
google.load('visualization', '1', {'packages':['corechart']});
google.setOnLoadCallback(loadChart);

function loadChart() {

var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1sA7M5kG6Xo8YScD1Df38PIA_G0bvhGRdqoExXg0KJTs/edit#gid=0');

query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}

var datatable = response.getDataTable();
var result = datatable.getValue(0,0);

document.getElementById('output1').value = result;
document.getElementById('output2').innerHTML = result;
document.getElementById('output3').textContent = result.toString();
}

</script>
</head>

<body>

Test 1: There are <input id="output1"> value <br>

Test 2: There are <p id="output2"> value </p>

<p> Test 3: There are <a id="output3"> </a> value </p>

</body>




The Spreadsheet url google spreadsheet

Answer

This is what I came up with:

<html>
<head>
    <script type='text/javascript'>
        window.onpageshow = function () {
            google.load('visualization', '1', {
                callback: function () {
                    var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1sA7M5kG6Xo8YScD1Df38PIA_G0bvhGRdqoExXg0KJTs/gviz/tq?tqx=out:html&tq?gid=0&headers=0&range=A1:C');
                    query.send(displayData);
                }
            });

            function displayData(response) {
                numval = response.getDataTable().getValue(0,0);
                document.getElementById('data').innerHTML = numval;
            }
        }
    </script>  
</head>
<body>
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <div id="data"></div>
</body>
</html>
Comments