topic Re: Google Spreadsheet connectivity in QlikView

Google Spreadsheet connectivity

parkera — Mon, 25 May 2009 02:42:00 GMT

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.

Google Spreadsheet connectivity

Mon, 25 May 2009 07:53:23 GMT

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

Google Spreadsheet connectivity

Mon, 25 May 2009 08:16:48 GMT

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!

Google Spreadsheet connectivity

parkera — Mon, 25 May 2009 22:30:38 GMT

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.

Google Spreadsheet connectivity

Mon, 25 May 2009 22:50:30 GMT

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.

Google Spreadsheet connectivity

parkera — Tue, 26 May 2009 05:30:20 GMT

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

Google Spreadsheet connectivity

parkera — Tue, 26 May 2009 15:37:11 GMT

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=0

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-database-hopefully/

Re: Google Spreadsheet connectivity

chrisbrain — Mon, 09 Nov 2009 16:30:05 GMT

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

Google Spreadsheet connectivity

Sat, 14 May 2011 21:48:34 GMT

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

Google Spreadsheet connectivity

chrisbrain — Sun, 15 May 2011 11:22:00 GMT

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/

Google Spreadsheet connectivity

chrisbrain — Wed, 07 Dec 2011 10:53:07 GMT

This is now released:

http://www.QVSource.com