How to save response.getDataTable.getValues(); into a multi-dim array

1,552 views
Skip to first unread message

shumway

unread,
May 18, 2012, 10:47:28 AM5/18/12
to google-visua...@googlegroups.com
Hello,

I am trying to pull data from a google spreadsheet, put it into a multi-dim array, and then use the different elements of the array to populate drop boxes. I can't seem to  get it to work correctly. Specifically the issue seems to lie with in this statement:

var row = new Array();
  for (var i = 0; i < numRows; i++) {
 
for (var j = 0; j < numCols; j++) {

row[i][j] = response.getDataTable().getValue(i,j);
// alert(response.getDataTable().getValue(i,j));
}
} 

Here is the full script:

<html><head><title>1805</title>

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load('visualization', '1', {'packages':['table']});
  
  function onldQuery(){
  var url='https://spreadsheets.google.com/a/finance-in-motion.com/tq?tq= select A,B,C &key=XXXXXXXXXXXXXXXXXXXXXXXX';
   var query = new google.visualization.Query(url);

 query.send(handleOnldResponse);
  }
  
  function handleOnldResponse(response){
  
  if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }
 var data = response.getDataTable();
 
 var numRows = response.getDataTable().getNumberOfRows();
 var numCols = response.getDataTable().getNumberOfColumns();
      
var row = new Array();
  for (var i = 0; i < numRows; i++) {
 
for (var j = 0; j < numCols; j++) {

row[i][j] = response.getDataTable().getValue(i,j);
// alert(response.getDataTable().getValue(i,j));
}
} 
 alert(row); 
 var dd = document.getElementById("Username");
 var sb = document.getElementById("secondBox");

 
 for(var i = 0; i< row.length; i++){
 
    dd.innerHTML += '<option>'+row[i]+'</option>';
sb.innerHTML += '<option>'+row[i][1] +'</option>';
}
  
  }
  
  
  function queryGet(){

    var chose = document.getElementById('Username').value;
var select = 'select A,B,C where A contains ' + chose;  
var url='https://spreadsheets.google.com/a/finance-in-motion.com/tq?tq= ' + select + '&key=0AjV_Fr31Owm1dHJpVDVDYVNQM3pxVnBlUEtDR1RWb1E#gid=0';
var query = new google.visualization.Query(url);
query.send(handleQueryResponse);


} 
function handleQueryResponse(response) {
if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }
  var data = response.getDataTable();
      visualization = new google.visualization.Table(document.getElementById('queryDisplay'));
      visualization.draw(data, null); 
}


  
  
 // }
  </script>
</head>

<body onload="onldQuery()">
<form>



<select id="Username"></select>
<select id="secondBox"></select>
  





<input type="button" value="Submit" onclick="queryGet()"  />

</form>
<div id="queryDisplay"></div>
</body>

</html>

asgallant

unread,
May 18, 2012, 11:58:17 AM5/18/12
to google-visua...@googlegroups.com
I suspect the problem is that you need to create a sub array in each row of the "row" array, like this:

var row = new Array();
for (var i = 0; i < numRows; i++) {
    row[i] = new Array();
    for (var j = 0; j < numCols; j++) {

shumway

unread,
Jun 22, 2012, 7:45:41 AM6/22/12
to google-visua...@googlegroups.com
Thanks. How can I mark this as answered?

asgallant

unread,
Jun 22, 2012, 10:30:34 AM6/22/12
to google-visua...@googlegroups.com
You're welcome.

There isn't any "answered" status to set for the thread (at least, not insofar as I am aware).
Reply all
Reply to author
Forward
0 new messages