One of the powerful features of working with the Google API is the deep relationship between the product lines. In this recipe, based on the last recipe, we will create a Google Spreadsheet and then integrate it into our application.
Have a copy around you of the source files from the last recipe (Creating charts using the ChartWrapper).
The steps involved with creating a new Google document are simple, but are needed to be able to integrate our work; as such we will run through them quickly.
- Go to http://drive.google.com/ (formally known as Google Docs) and register/login.
- Create a new spreadsheet.
- Add data to the spreadsheet.
- Click on the Share button and set the view to public:
- Create an API URL based on the document ID:
- Now, it's time to get into our JavaScript file, and delete the current data source and replace it with a URL feed:
google.load('visualization', '1.0'), google.setOnLoadCallback(init); function init(){ var options = {'title':'Deaths, for the 15 leading causes of death: United States, 2008', 'width':800, 'height':600}; var chart = new google.visualization.ChartWrapper({ chartType:'BarChart', dataSourceUrl:"https://spreadsheets.google.com/tq?key=0Aldzs55s0XbDdFJfUTNVSVltTS1ZQWQ0bWNsX2xSbVE", options:options, containerId:'chart' }); chart.draw(); }
Amazing! See how little code we needed to create a rich and fully interactive chart:
This is really the amazing part about it. You just don't need to understand how it works, all you need to do is create your chart and use the steps provided in the preceding section, and you can convert any of your own spreadsheets into a Google Spreadsheet.
The most important step in the preceding steps is step 4. Notice that the URL that is generated through the Google Documents (Google Drive) is not the same as the URL that we need to hit when working in code. This is because the first URL is intended to be rendered as a visual page, while the second link generates a new Google data object. Don't forget that every page has its own unique ID.
If you have a bit of a background with working with databases, you can send simple SQL queries into the data source and only get the items that you want to view. Let's say in our example we want to get the items in a different order, exclude column B, and sort based on column D (by age):
SELECT A,E,D,C ORDER BY D
Our Select statement is listing out what we want to select. The ORDER BY statement is self-explanatory. Let's add it to our code:
var chart = new google.visualization.ChartWrapper({
chartType:'BarChart',
dataSourceUrl:"https://spreadsheets.google.com/tq?key=0Aldzs55s0XbDdFJfUTNVSVltTS1ZQWQ0bWNsX2xSbVE",
query: 'SELECT A,E,D,C ORDER BY D',
options:options,
containerId:'chart'
});When you refresh your code, column B will be missing and the data will be organized based on column D.
Last but not least, add this to your code:
var chart = new google.visualization.ChartWrapper({
chartType:'BarChart',
dataSourceUrl:"https://spreadsheets.google.com/tq?key=0Aldzs55s0XbDdFJfUTNVSVltTS1ZQWQ0bWNsX2xSbVE",
query: 'SELECT A,E,D,C ORDER BY D',
refreshInterval: 1,
options:options,
containerId:'chart'
});
chart.draw();Now go back to the public chart and change the data in it. You will see that it will automatically update the chart.