Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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.
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.
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.
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$]
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).
In your https URL, do you see a #gid=0 part? Try manipulating this part by replacing the 0 with 1,2,3,...
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...
There is a #gid=0 part, but changing it doesn't seem to affect anything. It keeps pulling only the first sheet.
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.
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...