Analysis Top section : Rows, Columns and Values
Dimensions , Measures, Filters
Fixed Width Pivot
Vertical Group Bar
Vertical Stack Bar
Horizontal Group Bar
Horizontal Stack Bar
Bubble + line
Bar line dual
Bar Scatter dual
Chord / Circos
Circle Pack Hierarchical
Linear Tree Hierarchical
Custom Database Method
Menu and Options
General and Personal settings
Dashboard Page Properties
Chart Details [Dashboard Widgets]
Bar Chart (vertical)
Double or multi Bar chart (vertical)
Stacked Bar Chart (vertical)
Bar Chart (horizontal)
Dials and meters
Dashboard Prompts and Filters
Select / Combo box
Free Text Input
Drill down - inside and outside
Sharing and Collaboration
Embedding on Website
Share Live Analysis by URL
Share Static Analysis by URL
Tendulkar's Rise and Fall
Display Dashboards on TV
Dashboard Screenshots 1
Facebook Metrics Dashboard
Alexa Ratings Dashboard
Wordpress Metrics Dashboard
James Bond Box Office Stats
Other Data sources
API - Data Sourcing
Google Analytics API
Features and Benefits
Bells and Whistles
Visual Effetcs / Alerts
Motion Effects / Alerts
Sound Effects / Alerts
Install Dashboard on standalone computer or laptop
Install on a webserver
Setup PDF and Image Export
How to upgrade
< Previous page
Next page >
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
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.
When using SQL, we cannot refer them by the column header names we provided.
We need to refer the columns by their index name such as "A", "B", "C" etc
Find the sales distribution for products in the year 2008
group by B
We need to update the column header titles accordingly
So the above figure represents sales for year 2008
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
group by year(A)
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
group by year(A)
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
and our Grid comes back with data
Similarly our Bar chart comes back when we append "
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
< Previous page
Next page >
in the cloud or download and host your own