Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load from Web - Google Docs

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);

1 Solution

Accepted Solutions
MVP
MVP

Re: Load from Web - Google Docs

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.

14 Replies
giakoum
Honored Contributor II

Re: Load from Web - Google Docs

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.

Not applicable

Re: Load from Web - Google Docs

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.

giakoum
Honored Contributor II

Re: Load from Web - Google Docs

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$]

Not applicable

Re: Load from Web - Google Docs

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).

MVP
MVP

Re: Load from Web - Google Docs

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

giakoum
Honored Contributor II

Re: Load from Web - Google Docs

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...

Not applicable

Re: Load from Web - Google Docs

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

giakoum
Honored Contributor II

Re: Load from Web - Google Docs

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.

Not applicable

Re: Load from Web - Google Docs

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...

Community Browser