How to import multiple tables from website using ImportXML to googlesheets by overriding 50 request limit, and splitting details table data

importxml

#1

Hi,

I would like to:

(A) Confirm a way to import details table data for all 497 venues, via the following url, in one =ImportXml request. I would like to do this to override the 50 request limit, as I have to add 50 requests at a time by ten times to complete import of all data. Which is taking to long to import data for each 50 requests. Url=: http://www.datascape.com.au/infocomse/morelandcc/search.cfm?Page=Details&SS=yes&DIL=no&LP=Results&IT=&Opt=&DirPath=114Halls~and~Meeting~Rooms~for~Hire&DirCond={LSN:75}&SN=114&RequestTimeout=500&Type=Directory&Which=&String=&SubjectString=&Letter=&SubThesSN=&BFS=&Checks=&LSN=&Group=&Surround=false&TermList=&Order=EntryName&EntrySN=62456&All=yes.

The page is split into two sections: left side displays the 497 venues,and the right displays the Details table data for the venue you select.

(B) I would also like to import details table data for each venue as follows transposed from vertical to horizontal as follows:

Displays the headers on row 1 ie (Also known as:, Map Reference:, Postal:, Telephone:, Internet:, Contacts:, Area Served:, Meeting Times:, Facilities:, Parent Body:, Subjects:, Last Updated:),

Display the respective data for each header on row two.

I have been able to import this details table data, but the data is displayed with headers and data together.

Google spreadsheet link is: https://docs.google.com/spreadsheets/d/1FaGO5fI1nC2PgY5jUUdkqHhyjGLb5HzxQBjYLb8pASA/edit#gid=0

Any help with these two issues would be greatly appreciated.


#2

Where is this 50 request limit coming from?


#3

Hi Bobby,
It is a limit applied by Google for Google sheets, making requests for ImportXML, ImportHtml, ImportData & ImportFeed. Refer to this url:https://docs.google.com/document/d/1E7sT_BMkFOXR4EsDMUUhOgeD6BrEjFzS8fHv8tNrfPU/edit#


#4

@Snowie89 this might help you!


#5

Hi Bobby,
I tried this code before but was not successful, attempted again today both ways, after authorising script:
(A) In a cell: =importRegex(C2,"//table[3]//table[3]"), error message: Error
Invalid argument: http://www.datascape.<?>/infocomse/morelandcc/search.cfm?Page=Details&SS=yes&DIL=no&StartRow=1&ShowAll=Yes&LP=Details&IT=&Opt=&DirPath=114Halls~and~Meeting~Rooms~for~Hire&DirCond={LSN:75}&SN=114&RequestTimeout=500&Type=Directory&Which=Directory&String=&SubjectString=&Letter=&SubThesSN=&BFS=&Checks=&LSN=75&Group=&Surround=false&TermList=&Order=EntryName&EntrySN=62456&All=yes (line 3).

(B) Run via script: error message: DNS error: http://undefined (line 3, file “Code”).

Could you please advise what these two errors mean and how to resolve this. As the url is valid, as per results in sheet I sent to you.

Also do you have a resolution of how to split the headers and and respective data on two rows, for each of the 497 venues.


#6

Requested access to your spreadsheet, thanks!


#7

Hi Bobby,

Have you been able to solve my two issues ?

Regards,

Ron


#8

Dear @Snowie89,

I am sorry but I haven’t been able to find a solution.

Maybe someone else from the community can pick it up!


#9

Hi Bobby,

Thanks for investigating my issues anyway. Has anyone got any clues how to solve my two issues ?