topic Loading Google sheet into Qlik Sense in App Development

Loading Google sheet into Qlik Sense

undergrinder — Sat, 05 Mar 2016 16:01:16 GMT

Hi All,

I have an issue with loading data from gsheets.

I've read this thread:

How to load data from Google Drive into Qlik Sense?

The connection is work well with the link, that mto‌ shared, but when I'm trying my file to load I get an error message "Unsupported file format".

Is it can be an access problem?

The file, that I want to read isn't share with everybody, but with my user is.

Re: Loading Google sheet into Qlik Sense

undergrinder — Sat, 05 Mar 2016 16:12:50 GMT

I've read the full thread, and mountaindude‌ answered why it doesn't work.

Google is changing their various Google Drive products to a new format.

They say "they will migrate all docs in the 2014-2015 timeframe". So it

could be tomorrow, could be in a year's time.

The old Drive format allowed for having restricted access set on a doc, and

still be able to get the doc's data via a simple http call (which could be

done from a QV load script).

In the new format only docs visible to everyone can be accessed that way

(just like you had also found out).

If you set access restrictions on a doc, for example sharing it with

certain people, or maybe with everyone in your organisation (if you have

Google docs for organisations), then the "old" way of retrieving the data

from QV's load script does no longer work.

The only way seems to be using Google's APIs to get the data.

Which is somewhat of a pain if you do it yourself... Lot's of API calls,

OAuth and similar things that needs to be taken care of.

QVSource, while being an add-on that cost money, does handle all that work

for you - and it has worked flawlessly for us so far, in this very scenario.

It would of course be nice if Google would enable the old way of geting

data from docs also in the new Drive file formats. Might be hard though, if

they made the change to improve security etc. The old way of retrieving

data was pretty insecure...

/Göran

Re: Loading Google sheet into Qlik Sense

parkera — Wed, 29 Jun 2016 19:15:31 GMT

To break in Qlik Sense 3.0 I thought I would load data from sheets.google.com and see if I could load data.

What worked for me (the no fuss version)

1 Make copy of privately shared doc - 10 seconds

2 Change share options of copy doc to public with key - 10 seconds

3 Enter URL into Qlik Sense Web Data Load via cut and paste - 5 seconds

4 Load data into Qlik Sense... - 20 seconds

5 Delete copy doc in Google. - 5 seconds

Elapsed time 50 seconds...

If you need to access sheets.google.com via API then Qlik's new connector (QVSource as was) is the way to go but for one time infrequent loads the above works a charm and is a no fuss way of capturing the data that we store on a monthly "snapshot" basis.

Re: Loading Google sheet into Qlik Sense

undergrinder — Thu, 30 Jun 2016 09:15:04 GMT

Hi Adrian,

Thank you for your suggestion. I think it is a bit cumbersome with automated task...

I made a poor man's gsheet "connector", I'll share soon with community.

The solution was:

Disable standard mode
Execute batch - download private gsheet through Google API
Read the downloaded csv

Re: Loading Google sheet into Qlik Sense

fkeuroglian — Sat, 22 Oct 2016 01:11:41 GMT

Hi Gabor, do you have any example to the bach to download the google sheet?

Thanks a lot

Fernando K.

Re: Loading Google sheet into Qlik Sense

fkeuroglian — Sat, 22 Oct 2016 01:12:51 GMT

Hi adrian

what is the way to do this:

2 Change share options of copy doc to public with key

Thank a lot

Fernando K.