Google Spreadsheet connectivity

[parkera]

ive found the Google Spreadsheet API hints and tips page but what Id really like is an example of a connected worksheet as a real example so that we can all use google spreadsheets. This will increase as Cap Gemini etc sell more Google Desktop Enterprise deals.

[#2 · Accepted solution]

I second that...I think that as business apps move more into the cloud, this will become a more popular data source for QlikView. I'd love for someone to upload an example! I'm going to give it a shot using QV9 RC on Windows 7 RC

[#3]

OK, well here's a basic qvw app using Google Spreadsheets as a data source. This is a basic table of QlikView training centers in the U.S. The problem is that the data is published as a public webpage...not sure if this will help anyone, but here it is!

[parkera]

Jason,

This is perfect. Which wizard did you use? XML or HTML (URL)? The latter has user name and pw capability I think. So should work for pw protected fiiles. Which ours are. Now if you get five mins to make one of those that would be great.

It will be interesting to see the "load times" are like from our Google friends.

[#5]

I used the "Web Files" wizard and pasted the spreadsheet's published URL.

As for the password protection, how did you do that with your files? I piped this in from a consumer version of Google Docs, so I'm not familiar with the ability to publish spreadsheets yet still have them UN/PW protected.

[parkera]

Jason,

OK Ive done some prolonged investigations and the API from Google is not the best; it offers an RSS Atom feed at Row or Cell level, not columnar as a result it is not possible to pull back a table.

Check out the RSS feed off oyur public spreadsheet to see. It is possible to write script to access private (requiring authentication) worksheets - howeven no further lookups are possible.

Is is easy to parse / rebuild the data, even work off cell data to build a sheet / coumns.

These two links provide all the API notes.

Until we can resolve then the public html lookup will have to suffice - however that is public by definition and requires the sheet to be publicised making it not real time.

All support appreciated from you and anbody else.

http://code.google.com/apis/spreadsheets/docs/2.0/reference.html#Feeds

http://code.google.com/apis/spreadsheets/docs/2.0/developers_guide_protocol.html#CellFeeds

Cheers

Adrian

[parkera]

Final word for now, I think, 'published' spreadsheets can be interrogated via SQL eg

http://spreadsheets.google.com/tq?tqx=out:html&tq=select%20A%2CB%2CC&key=r7A-xWxMDqEcJ5W1n9ohZkw&gid...

For a headstart on what options are available, then this site is the best reference Ive found to date.

http://ouseful.wordpress.com/2009/05/20/making-it-a-little-easier-to-use-google-spreadsheets-as-a-da...

see also

http://ouseful.open.ac.uk/datastore/gspreadsheetdb1.php

It would appear that the only way to get data our from a non published (ie non html) spreadsheet (with ccc in the key as opposed to pub) is via RSS/Atom feed and this is restricted to

1 Sheet Listings
2 Row Listing - entires contents of a row in one record with colum titles-except colum A it owuld appear)
3 Individual Cell Listing - gives unique cell reference and data contents (probably can do more with this)

There are middle ware applications called Google Apps which will also do the job - I am sure for example the backup applicastions can be leveraged.

Further I noticed full calendar integration application between SalesForce.com and Google Desktop - very cool.

Google's take over of the planet has started, aided by Oracle - who now own most of the technology stack required by google..

So all of us freebie users of Goolgle are a little stuffed for now (as most apps only work on premium). We need to see if we can work off the

html feed for published data - a technically sound solution but impractiable as most data is sensitve

Non commercially sensitve non htmal data can be seen by query (just by sharing the key, it would appear)

eg http://spreadsheets.google.com/tq?tqx=out:html&tq=select%20*&key=r7A-xWxMDqEcJ5W1n9ohZkw&hl=en#

Should pullback a three line sheet with headings - this so far has been the best Ive seen for table display and worth exploring.

Cheers.

[chrisbrain]

Hi,

I recently posted a blog entry and associated QVW file which might be of interest to readers of this thread - it illustrates authenticating with the Google Spreadsheet API and using it to download a worksheet, save it to file and then load it into QlikView. You can see it here:
http://www.qlikster.com/post.aspx?id=7d5fdad7-4215-4433-9b59-9c040707409a

The above has now been superceded by a new solution we are developing for connecting QlikView to all sorts of web APIs and other difficult to load sources. It already contains a connector for Google Spreadsheets and Google Analytics as well as Facebook, LinkedIn, OData and MongoDB:

http://www.qvsource.com/wiki/Google-Spreadsheets-Connector.ashx

Message was edited by: chrisbrain

[#9]

This doesn't seem to be available yet, though. Do you have a timeframe for when it will be?

[chrisbrain]

We don't have a date set for release yet but hopefully will announce something soon. Currently we are gathering feedback, developing/improving the current connectors as well as working on new ones as well as the development SDK.

There is a fully functional beta which you can sign up for, linked from:

http://www.qvsource.com/wiki/