topic Re: Load from Web - Google Docs in QlikView

Load from Web - Google Docs

Thu, 09 Aug 2012 13:32:08 GMT

I am trying to load data from a file on Google Docs, but the document has multiple sheets and only the data from the first sheet is being captured. Does anyone know how I can edit my script to include data from all the sheets? All the sheets have the same data fields, they just need to be joined into one big data set.

My current script looks like this:

LOAD

F2 as EmployeeName,

     F3 as StartDate,

     F4 as EndDate,

     F5 as Duration,

     F6 as Category

FROM

[https://docs.google.com/spreadsheet/etc...]

(html, codepage is 1252, embedded labels, table is @3);

Re: Load from Web - Google Docs

giakoum — Thu, 09 Aug 2012 13:50:04 GMT

Since all sheets have the same field names, you need to repeat the load script for every sheet. The result will be one large table. You can also use the for...next function to run the load as many times as you wish without repeating it, but in this case you must pass the sheet name as a parameter.

Re: Load from Web - Google Docs

Thu, 09 Aug 2012 13:52:09 GMT

Thanks Ioannis. How do I refer the script to the next sheets though? Right now, it seems to see only Sheet 1 no matter what hyperlink I use.

Re: Load from Web - Google Docs

giakoum — Thu, 09 Aug 2012 13:56:01 GMT

Have not tried it with Google Docs, only Excel, but it should be the table parameter :

table is @3

you can refer to the table by name as well. Example :

table is [General Settings$]

Re: Load from Web - Google Docs

Thu, 09 Aug 2012 14:54:55 GMT

Tables @1, @2, and @3 are the only options I can see. I am very new with LOAD script, so I apologize if this is a very dumb question, but do you know how to identify the tables? I know how in Excel, but Google Docs spreadhseets seem to have far less functionality (I never use them).

Re: Load from Web - Google Docs

swuehl — Thu, 09 Aug 2012 15:08:21 GMT

In your https URL, do you see a #gid=0 part? Try manipulating this part by replacing the 0 with 1,2,3,...

Re: Load from Web - Google Docs

giakoum — Thu, 09 Aug 2012 16:08:40 GMT

Sorry you are right, i cannot get it to work either.

Maybe somebody else has tried that and can help.

You could of course create multiple documents... I know you dont like the idea but it would help...

Re: Load from Web - Google Docs

Thu, 09 Aug 2012 16:10:07 GMT

There is a #gid=0 part, but changing it doesn't seem to affect anything. It keeps pulling only the first sheet.

Re: Load from Web - Google Docs

giakoum — Thu, 09 Aug 2012 16:15:57 GMT

swuehl was right, it is a different URL :

https://docs.google.com/spreadsheet/ccc?key=0AjZFjwU5KgutdDZmUFdBRkREMW1ScldpX2tEeFM2UUE#gid=1

https://docs.google.com/spreadsheet/ccc?key=0AjZFjwU5KgutdDZmUFdBRkREMW1ScldpX2tEeFM2UUE#gid=0

it works for me, try the above links out and you will see. the only difference between the two URLs is the #gid=1 or #gid=0 at the end.

Re: Load from Web - Google Docs

Thu, 09 Aug 2012 16:21:01 GMT

What's weird though is that when I change the URL in my script to only add sheet 2 for example, it still returns the data for Sheet 1 even though the URL is to Sheet 2...

Re: Load from Web - Google Docs

giakoum — Thu, 09 Aug 2012 16:27:58 GMT

true

very annoying...

not supported I guess, sorry I cannot help you further.

Re: Load from Web - Google Docs

Thu, 09 Aug 2012 16:37:13 GMT

No worries. Thank you anyways! Maybe someone else on the Community has encountered this before?

Re: Load from Web - Google Docs

swuehl — Thu, 09 Aug 2012 16:43:05 GMT

I was able to access the different sheets by first publishing the document (via Google Docs publish feature), then use the published doc URL and used the gid=0 / gid=1 to switch between sheets.

This will somewhat create a public access to your document, only secured by the encrypted URL.

Re: Load from Web - Google Docs

Thu, 09 Aug 2012 16:54:37 GMT

Got it. Thanks!!

Re: Load from Web - Google Docs

Tue, 11 Dec 2012 15:25:22 GMT

Hi!

has anyone found a solution for this problem?

I can not load either the sample document of numbers and letters, I can only load the numbers gid...

Thanks in advance