Re: Visualizing a single number from a google spreadsheet

asgallant

unread,

Oct 15, 2012, 6:40:55 PM10/15/12

to google-visua...@googlegroups.com

You can call the DataTable#getValue method to pull whatever you want out of the DataTable. You can insert that data into your page however you like, it doesn't have to be via one of the packaged visualizations.

On Monday, October 15, 2012 6:17:33 PM UTC-4, Billy Bones wrote:

Hi folks, I know this is child's play to most of you. I am using google docs forms to populate google docs spreadsheets which populate a dashboard of google visualization charts on my website. There are times when I would just like to take one number from a spreadsheet and post it to my site in a customized way. Ideally, I could use large "flip chart" style numbers to display the number in my google docs spreadsheet. I am able to place a table on my site to get the information there, but I would like to be able to draw individual numbers from my spreadsheet in stylized colors, sizes, or fonts. Any ideas for me?

Billy

Billy Bones

unread,

Oct 16, 2012, 1:05:57 PM10/16/12

to google-visua...@googlegroups.com

Thanks ASG!

I'm in over my head. I am using chart wrappers to draw the charts. I am giving them a containerId at this time. Then I am creating a div that it sits inside of. Do I scrap that to call the DataTable#getValue method or is that something I do inside of the chart wrapper? When I set up the chart wrapper to call the data table, it will import numbers into my dashboard, but I have a flip number as a jpg associated with each real number, and I was trying to bring those over from the spreadsheet. I imagine that there is a much better way to do what I am trying to do.

<!DOCTYPE html>

<html>

<head>

//Load the Visualization API and the ready-made Google table visualization

google.load('visualization', '1', {'packages':['corechart']});

</script>

function drawF() {

// Define the chart using setters:

var wrap = new google.visualization.ChartWrapper();

wrap.setChartType('Table');

wrap.setDataSourceUrl('https://docs.google.com/spreadsheet/ccc?key=0ArzcNGBduYipdDc3VUtSQUQ4dUNRdnVRZUYzTGRjZVE&range=A1');

wrap.setContainerId('flipnumbers');

wrap.setOptions({'title':'Total Clients','width':480,'height':410,});

wrap.draw();

wrap.getChart();

}

function drawVisualization() {

drawF ();

}

google.setOnLoadCallback(drawVisualization);

</script>

</head>

<body>

</div>

</body>

</html>

asgallant

unread,

Oct 16, 2012, 1:58:02 PM10/16/12

to google-visua...@googlegroups.com

Ahh, ok. So basically, the implementation of the ChartWrappers using query's is a bit quirky, so there isn't actually any way to get the DataTable back out when doing that (at least, not when I last checked, which was admittedly a long time ago). What you should do here is switch to using a regular query, something like this:

google.load('visualization', '1', {'packages': ['corechart']});

google.setOnLoadCallback(drawVisualization);

function drawVisualization() {
  var query = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=0ArzcNGBduYipdDc3VUtSQUQ4dUNRdnVRZUYzTGRjZVE&range=A1');
  query.send(function (response) {
  if (response.isError()) {
  alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
  return;
  }

  var data = response.getDataTable();
  var flipNumber = data.getValue(0, 0);
  // do something with flipNumber
  });
}

I'm not certain what will happen when you query a cell with an image in it; that's not something I've ever seen done before. You should be fine if it returns an <img> tag with the appropriate url, though.

Billy Bones

unread,

Oct 16, 2012, 3:36:12 PM10/16/12

to google-visua...@googlegroups.com

Thanks for this!

where you have var flipNumber = data.getValue(0,0); - is that my column and row that I am after info from? In which case, I could drop the "&range=A1" from the query url?

where you say "do something with flipNumber" do you mean that the above function goes and gets the value, and now I can do something with it?

I'm sorry that the questions are very beginner. Do I need to create a div to do something with the flipNumber? Do I do something inside the body? Can you give me a simple piece of code that would display whatever was in that cell?

Billy

Billy Bones

unread,

Oct 16, 2012, 3:40:28 PM10/16/12

to google-visua...@googlegroups.com

This was me trying to do what you had there

<!DOCTYPE html>

<html>

<head>

//Load the Visualization API and the ready-made Google table visualization

google.load('visualization', '1', {'packages':['corechart']});

</script>

function drawVisualization() {

var query = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=0ArzcNGBduYipdDc3VUtSQUQ4dUNRdnVRZUYzTGRjZVE&range=A1');

query.send(function (response) {

if (response.isError()) {

alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

return;

}

var data = response.getDataTable();

var flipNumber = data.getValue(0, 0);

// do something with flipNumber

});

}

google.setOnLoadCallback(drawVisualization);

</script>

</head>

<body>

</body>

</html>

asgallant

unread,

Oct 16, 2012, 5:11:35 PM10/16/12

to google-visua...@googlegroups.com

Yes, the 0, 0 are the row, column coordinates of the cell to get the value from. You could drop the range from the URL, but you would then likely have to add a select statement to the query, so it doesn't make much difference either way.

The variable "flipNumber" has the value of whatever is returned by that query, so you can do whatever you like with it from there. Assuming it is an <img> tag, you could insert it into your HTML inside the "flipnumbers" div, for example:

document.getElementById('flipnumbers').innerHTML = flipNumber;

Billy Bones

unread,

Oct 18, 2012, 7:14:46 PM10/18/12

to google-visua...@googlegroups.com

Thanks for your help ASG, I am not getting anywhere, can you tell me what I'm doing wrong here trying to implement your idea.

<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></meta>
  <title>Home Page</title>
  
  <script type="text/javascript" src="http://www.google.com/jsapi"></script>
  <script type="text/javascript">
  //Load the Visualization API and the ready-made Google table visualization
  google.load('visualization', '1', {'packages':['corechart']});
  </script>

  <script type='text/javascript'>

function drawA() {

// Define the chart using setters:
var wrap = new google.visualization.ChartWrapper();

  wrap.setChartType('PieChart');
  wrap.setDataSourceUrl('https://docs.google.com/spreadsheet/ccc?key=0ArzcNGBduYipdDFLMGM1SEZ1ZVhWdHUyOEl3QmRodEE#gid=0');
  wrap.setContainerId('numberofclients');
  wrap.setOptions({'title':'Number of Clients','width':410,'height':310,'chartArea':{width:"90%",height:"100%"}});
  wrap.draw();
  wrap.getChart();
  }

function drawB() {

  var query = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=0ArzcNGBduYipdDc3VUtSQUQ4dUNRdnVRZUYzTGRjZVE&range=A1');
  query.send(function (response) {
  if (response.isError()) {
  alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
  return;
  }

  var data = response.getDataTable();

  var flipNumber = data.getValue(1, 1);
  });
}

  function drawVisualization() {
drawA();
  drawB();
  }

  google.setOnLoadCallback(drawVisualization);
  </script>
  </head>
  <body>
<a href="http://www.wardell.biz/pelican">Are you a Pelican, or a Peli-can't?</a>
<div id="container" style="width:1300px;height:455px;">
<div style="position:relative;width:100%">

<div id="clientnumbers" onclick="location.href='http://www.wardell.biz/billyclients';"style="cursor:pointer; position:absolute;left:10px;top:0px;border:5px black solid;border-radius:25px; -moz-border-radius:25px;height:420px;width:500px;padding:6px;">
<div id="numberofclients"; onclick="location.href='http://www.wardell.biz/billyclient1';"style="cursor:pointer; position:relative;left:6px;top:10px;height:220px;width:410px;"></div>

</div>
<div id="flipnumbers"style="cursor:pointer; position:absolute;left:550px;border:5px red solid;border-radius:25px; -moz-border-radius:25px;height:420px;width:500px;padding:6px">

<document.getElementById('flipnumbers').innerHTML = flipNumber;>

asgallant

unread,

Oct 18, 2012, 7:22:58 PM10/18/12

to google-visua...@googlegroups.com

You almost had it. Just two things:

1) the flip number value you want is in (0, 0) in the data, not (1, 1)

2) you needed to put the line document.getElementById('flipnumbers').innerHTML = flipNumber; in the script after you assign the value to flipNumber

See it working here: http://jsfiddle.net/asgallant/XdHD8/

Billy Bones

unread,

Oct 18, 2012, 7:32:56 PM10/18/12

to google-visua...@googlegroups.com

Thank you so much! I'm amazed I was that close. When it doesn't work, it just doesn't work, and I have no idea why.

Billy

Billy Bones

unread,

Oct 22, 2012, 6:51:46 PM10/22/12

to google-visua...@googlegroups.com

Can I ask a follow up question? How would a guy go about putting a title on this?

Billy

On Thursday, October 18, 2012 4:22:58 PM UTC-7, asgallant wrote:

asgallant

unread,

Oct 22, 2012, 6:59:24 PM10/22/12

to google-visua...@googlegroups.com

A title on the page? Use the <title> tag in the page's <head>

Project Pelican

unread,

Oct 22, 2012, 7:16:41 PM10/22/12

to google-visua...@googlegroups.com

Sorry, I mean a title above the flipnumbers that shows what information the numbers pertain to. I don't know if I should be putting that in the function or in the div, or when I draw it.

Billy

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/6MdQ5sqoyCAJ.

To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

asgallant

unread,

Oct 22, 2012, 7:34:34 PM10/22/12

to google-visua...@googlegroups.com

Put it in the drawing function. You can add whatever you need via javascript.

Billy

To unsubscribe from this group, send email to google-visualization-api+unsub...@googlegroups.com.

Reply all

Reply to author

Forward