topic Re: Re: How to load data from google docs spreadsheet? in QlikView

How to load data from google docs spreadsheet?

Thu, 30 Jan 2014 14:16:05 GMT

Hi,

I have few files on google drive spreadsheet. Can someone kindly share a simply way of how to load the data from google drive

For example I have the same excel sheet on my drive locally and this is the loadscript for it

LOAD a,

FROM

[test.xlsx]

(ooxml, embedded labels, table is [RAW DATA]);

How do I load the same file from google drive spreadsheet?

Arif

Re: How to load data from google docs spreadsheet?

Thu, 30 Jan 2014 14:33:51 GMT

open the spreadsheet with the internet explorer.

go to the script editor and clik on web file paste the url of the spread sheet.

thats one way

have you try to download the gdocs app and syn the drive with a file in your local computer?

Re: Re: How to load data from google docs spreadsheet?

Thu, 30 Jan 2014 14:43:34 GMT

I have tried to click on script editor and then clicked on spreadsheets, but which URL do I past and in what format in Qlikview?

Re: How to load data from google docs spreadsheet?

marcus_malinow — Thu, 30 Jan 2014 14:44:52 GMT

Hi Syed,

this is actually pretty easy though there are a couple of slight issues.

In your script editor, click on the 'Web Files' button. Enter the URL of your google spreadsheet.

In the dialog you'll need to select table '@2'

Note that you can only reference the first sheet in a google doc. If you want fuller functionality you'll probably have to use a product such as QVSource.

Marcus

Re: How to load data from google docs spreadsheet?

Thu, 30 Jan 2014 14:48:59 GMT

Can't see the webfiles button. Can you please share a screen and highlight it?

Syed

Re: How to load data from google docs spreadsheet?

marcus_malinow — Thu, 30 Jan 2014 14:50:18 GMT

Syed, it's in QlikView, in the Script Editor. It's at the bottom in the 'Data' tab.

Re: How to load data from google docs spreadsheet?

Thu, 30 Jan 2014 14:56:41 GMT

ohh that was a bit stupid of me lol. Many thanks. Yes, it is working. One last question though. Even the embed lables are not taking the first row as lablels. In excel, If I select embed lables, it takes the first row as labels. Why is it not taking the first row as labels?

Re: How to load data from google docs spreadsheet?

marcus_malinow — Thu, 30 Jan 2014 15:05:53 GMT

Basically because it's a bit rubbish

Try using this in the parameters to remove the top row:

(Remove(Row, Pos(Top, 1)))

Re: How to load data from google docs spreadsheet?

Thu, 30 Jan 2014 16:00:02 GMT

Sorry one more question. What do I need to do to reload the same script on another machine or on a client computer?

Syed

Re: How to load data from google docs spreadsheet?

marcus_malinow — Thu, 30 Jan 2014 16:02:44 GMT

Shouldn't need to do anything at all, as long as your machine has internet access.

Only issue might be if the document is secured, in which case you're going to run into issues.

If you need the document to be secured, then perhaps the Google docs sync app might be the way to go.

Re: How to load data from google docs spreadsheet?

Thu, 30 Jan 2014 16:19:14 GMT

how does it authenticate before accessing the file?

Re: How to load data from google docs spreadsheet?

Thu, 30 Jan 2014 16:20:58 GMT

Can we put some kind of authentication into the script that will authenticate everyone for loading the data?

Re: How to load data from google docs spreadsheet?

marcus_malinow — Thu, 30 Jan 2014 17:12:33 GMT

Script - Not as far as I'm aware, though I haven't tried this fully.

Only suggestion would be to set up a Google account for your service account, then when logged in to your server as that account log in to Google. Make sure you save your password in IE. Perhaps QlikView will then be able to use that authentication.

Re: How to load data from google docs spreadsheet?

Tue, 11 Feb 2014 14:48:41 GMT

Can you please explain how can we load data from local google drive? There are files with gsheet extensions and I dont know how to load them in Qlikview.

Arif

Re: How to load data from google docs spreadsheet?

Wed, 02 Sep 2015 11:43:26 GMT

Hi Marcus,

I'm having the same issue. Please could you explain how to do this as I don't follow.

Thanks,

Jayne

Re: How to load data from google docs spreadsheet?

marcus_malinow — Wed, 02 Sep 2015 12:04:05 GMT

Hi Jane

So, I have a spreadsheet at the URL https://docs.google.com/spreadsheets/d/1kIpJZ148hMFxN3nGPPKBhbQyov734um6ahBcMNG1xD0/edit#gid=0

I can use this script to load it:

LOAD *
FROM
[https://docs.google.com/spreadsheets/d/1kIpJZ148hMFxN3nGPPKBhbQyov734um6ahBcMNG1xD0]
(html, codepage is 1252, embedded labels, table is @2, filters(
Remove(Row, Pos(Top, 1))
));

Alternatively if you want to build the script yourself, In the script editor, click on the 'Web Files'' button at the bottom. Enter the URL to your Google spreadsheet and follow this wizard.

Marcus

Re: How to load data from google docs spreadsheet?

Thu, 03 Sep 2015 07:56:34 GMT

Thanks Marcus.

Re: How to load data from google docs spreadsheet?

korsikov — Wed, 18 May 2016 13:57:33 GMT

do you solve a problem? how to specify the Google account you want to use click to download data from Google drive

Save Password in IE not help

Re: How to load data from google docs spreadsheet?

marcus_malinow — Wed, 18 May 2016 17:58:57 GMT

Hi Alexander,

I investigated this fairly thoroughly last year and never managed to resolve it to my satisfaction.

If the document is secured, it didn't seem possible to provide authentication to gain access.

Using Google drive, the downloaded documents weren't in an understandable format.

Marcus

Re: How to load data from google docs spreadsheet?

korsikov — Thu, 19 May 2016 19:19:01 GMT

I found a solution. shareable link - it does not need to enter the Google account