Using DataSource URL with Google Visualization API and Spreadsheet

Spent a good chunk of the day figuring out how to work with the Google Visualization API DataSource URL from a Google Docs Spreadsheet.  It was a little tricky at first, but it’s not too bad once you figure it out.

First make a query of the URL using the built in Visualization API methods:

var query = new google.visualization.Query('<your cell ranges go here>&headers=-1&key=<your URL info goes here>&gid=17&pub=1'); 

ok, let’s break down the key variables in the URL above:

  1. tq?range=E2-G2Anything after the tq is a query written in the Google Visualization API Query Language.  It’s very similar to basic SQL.  In the case above it’s saying “Select the cells between the range of E2 to G2”.  A pretty basic selection, but you can make much more elaborate ones using the Query Language.
  2. &key<your URL info goes here> can be found in the URL of the spreadsheet that you’re pulling data from.
  3.  &pub=1Appending this variable with the value of 1 states that the data is public.  This is important if you want you data available to any web visitor.
You then make a call to the API using the send() function.
Then send method requires a callback function.  In this case we name it handleQueryResponse.
Below is a basic function that when called pulls a value from the spreadsheet and sets that value on a web page.
function handleQuery_dayResponse(response) {
  var data = response.getDataTable();
  var sampleText = document.getElementById('sample-text-div');
  sampleText.innerHTML = 'Enrollment  as <strong>Day ' + data.getValue(0,0) + '</strong>.';
This assigns the variable data with the value of the response formatted as a DataTable.  It then grabs the specific value from the DataTable, in this case there’s only one value returned so the column and row values are (0,0).  The getValue() function makes this really easy to do.

Check out a basic example.

3 replies on “Using DataSource URL with Google Visualization API and Spreadsheet”

Google visualization API supports also data source URL’s that are not spreadsheets.

You can create your own Google visualization compliant data source feed that can be read by Google visualizations, similar to the way you would create a RSS feed that can be read by RSS readers. This is great way to publish your data in a visual way.

One API that can help you with this task is GVStreamer by

Leave a Reply

Your email address will not be published. Required fields are marked *