Google Spreadsheet

 
Google spreadsheet is the equivalent of Microsoft Excel except that it is available as a web application and can be used as a multi purpose web database.
 
It is one of the best tools for bootstraping Dashboards and streamlining lot of reporting and maintaining statistics.
 
InfoCaptor comes with an in-built data adapter for Google Spreadsheet.
 
Create Google Spreadsheet
 
Let us create a brand new google spreadsheet (NOTE: you can use any of your existing google spreadsheet as well)
 
 
In the above figure, we have a three column worksheet and we named it as "sales"
 
We have added some fictional data from a store that sells "tea", "coffee" and "snacks". (Yep, it could be one of your Starbucks Franchise)
 
So every month you maintain very high level sales metrics in this nice worksheet.
 
NOTE: We are using a Date column and date value is the first day of the month. This has multiple benefits which we will see later.
 
 
Make the Spreadsheet Public
 
We need to share the spreadsheet to the public so that we can integrate it with InfoCaptor.
 
When we make it public it is accessible by only those whom you share the URL with. The direct URL of the spreadsheet is not listed.
 
 
Click on share and then change the settings
 
 
You can choose the "Public on the web" if you really need your spreadsheet to be found.
 
Here is the direct link to the above spreadsheet and you can use it for your testing
 
 
Get Key from Google Spreadsheet
 
From the spreadsheet URL, grab the key portion
 
key= 0AgQUzbm48k2ydDlUUEd1SnJySXRtamszS3dQQXJ2LUE
 
 
Pull Spreadsheet data on Dashboard
 
  • Drag the Grid component to the center canvas
 
 
  • Right click on the Grid and select Data source
 
  • Add the key value
 
In the parameters section
1. Key = This is the key of the google spreadsheet
2. Columns  = It does not matter what you name your columns in the spreadsheet, you need to mention the column names as array of values
3. header_rows = This indicates that the first row is the header and needs to be skipped
 
  • Row Limit and SQL
 
We keep the row limit to 10 and leave the SQL portion blank (later we will see how to use SQL)
 
Click OK and it brings the data and populates the Grid
 
 
Use SQL query against Google Spreadsheet
 
Google Spreadsheet implements a subset of SQL language so if you are familiar with database you can easily follow.
 
 
 
Now let us try to restrict the columns and do some aggregation.
 
 
  1. When using SQL, we cannot refer them by the column header names we provided.
  2. We need to refer the columns by their index name such as "A", "B", "C" etc
 
Problem Statement: Find the sales distribution for products in the year 2008
 
SQL:
select B,sum(C)
where year(A)='2008'
group by B
 
 
 
We need to update the column header titles accordingly
 
Click OK
 
So the above figure represents sales for year 2008
 
Problem Statement : Show yearly sales trend
 
  • Drag a bar chart
  • Right click ,
  • select "Data Source",
  • It would be prepopulated from the previous widget we worked on
  • Change the SQL to below
 
select year(A),sum(C)
group by year(A)
 
 
 
Problem Statement : Show yearly sales trend and for each year show product distribution as stack bar
 
We have a very powerful function that allows us to create pivot tables from our result set.
 
To solve this requirement, we simply use the previous query sum the sales by year and then add a pivot clause by the column B i.e Product name
 
"key":"0AgQUzbm48k2ydDlUUEd1SnJySXRtamszS3dQQXJ2LUE&sheet=sales",
"columns":["Period","Coffee","Snacks","Tea"],
"header_rows":1
 
 
SQL
select year(A),sum(C)
group by year(A)
pivot B
 
 
 
To draw the stack bar chart we drag the stack bar from left panel
 
 
And we apply the same pivot query as above
 
 
Working with Multiple worksheets
 
If you have two or more worksheets within the same Google spreadsheet document then you can refer them by their worksheet names
 
By default if you don't speficy a worksheet name then it considers whichever worksheet is first in the order
 
We added another worksheet named "employees"
 
Since "sales" is the first in the order our SQL queries will work against that
 
What if we change the order of the worksheets
 
 
Now all our above queries will be against the employees worksheet.
 
This is not good and the results are unexpected
 
When we refresh the Chart it gives us an unexpected error
 
 
And similarly the Grid also errors out
 
 
Solution for multiple Worksheets
 
We can append "sheet=sales" as part of our key definition
 
"key":"0AgQUzbm48k2ydDlUUEd1SnJySXRtamszS3dQQXJ2LUE&sheet=sales",
"columns":["Period","Product"],
"header_rows":1
 
 
and our Grid comes back with data
 
Similarly our Bar chart comes back when we append "&sheet=sales"
 
 
 
 
Other Notes
 
  • Always use upper case letters for column names
  • lower case letter within function calls may not work e.g year(a) will not work, so use year (A) instead
  • You should be able to use spreadsheet functions within the SQL statements
  • CAUTION: If you provide more columns than what it is suppose to return then it won't bring any data back and certain widgets like Dial will show -999 indicating an error.
 
Here is the function reference
 
 
  • You can also directly test your SQL queries in the browser
 
Use online Web Dashboards in the cloud or download and host your own Dashboards