Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
undergrinder
Specialist II
Specialist II

Loading Google sheet into Qlik Sense

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.

G.

1 Solution

Accepted Solutions
undergrinder
Specialist II
Specialist II
Author

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

G.

View solution in original post

5 Replies
undergrinder
Specialist II
Specialist II
Author

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

G.

parkera
Partner Ambassador
Partner Ambassador

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.

undergrinder
Specialist II
Specialist II
Author

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

G.

fkeuroglian
Partner - Master
Partner - Master

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

Thanks a lot

Fernando K.

fkeuroglian
Partner - Master
Partner - Master

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.