Xpath Query - Using importXML function Google Sheets for Gqueues page where Xpath has @id field

1
votes

I am a long time reader, first time poster. Thanks so much for all of the help in the past.

I am new to Xpath and the Google sheets importXML function. I am having trouble getting the Xpath query syntax right. I want to import the values from my published Gqueues task list into a Google Sheets Spreadsheet using the importXML function. For example the first list item is "send out contract for 260 o'farrell st.". I am having trouble writing the Xpath correctly and have been getting #N/A and #Error messages in the Google Sheets cell.

The Gqueues task list is located here: https://www.gqueues.com/public/queues/ag1zfmdxdWV1ZXMtaHJkciILEgdBY2NvdW50GKPKmg0MCxIFUXVldWUYgICAgMSxmAoM?options=1

Using Chrome I inspected the element that I want and copied the Xpath. According to Chrome, it looks like this: //*[@id="ag1zfmdxdWV1ZXMtaHJkciELEgdBY2NvdW50GKPKmg0MCxIESXRlbRiAgICAkLb-Cgw"]/div[1]/table/tbody/tr/td[4]/table/tbody/tr[2]/td[3]

I think that the @id tag may be the cause of my problem. Here is the Google sheets function I have been trying where A1 references the link:

=importXML(A1, "//*[@id="ag1zfmdxdWV1ZXMtaHJkciELEgdBY2NvdW50GKPKmg0MCxIESXRlbRiAgICAkLb-Cgw"]/div[1]/table/tbody/tr/td[4]/table/tbody/tr[2]/td[3]")

Where the Xpath is the same as copied directly from Chrome. There are no results and I get the following error. error: Parse error

I have also tried using a single quote around the id tag: =importXML(A1, "//*[@id='ag1zfmdxdWV1ZXMtaHJkciELEgdBY2NvdW50GKPKmg0MCxIESXRlbRiAgICAkLb-Cgw']/div[1]/table/tbody/tr/td[4]/table/tbody/tr[2]/td[3]")

This time I get a #N/A result and the following error, "error: The xPath query did not return any data."

I am getting close with either path? How can I go about understanding how to write the Xpath expression to return the text lines "send out contract for 260 o'farrell st.".

2
xmlfunctionxpathxml-parsinggoogle-docs

2 Answers

1
votes

The contents of that page are loaded using JavaScript at runtime. This is easy to see if you look at the page source (do not use Firebug / Chrome Developer Tools) and search for the contents.

There is no way to parse this page with Google Spreadsheet. You might be able to analyze the code loading the data and directly query the page though. Looking at the queried URLs, you will easily find that this call contains the data you need:

https://www.gqueues.com/public/queues/content/ag1zfmdxdWV1ZXMtaHJkciILEgdBY2NvdW50GKPKmg0MCxIFUXVldWUYgICAgMSxmAoM?filter=open&type=user&_=1387204105014

This data is in JSON format. Google Spreadsheet doesn't support it out of box as far as I know, but there are some efforts on libraries adding it, yet I do not know it.

1
votes

If your goal is to get your tasks from a queue in GQueues into a Google Sheet then I would suggest checking out GQueues "export a single queue" functionality, which exports the data in a queue into a CSV file. Then you can just drag the exported file onto Google Drive and it will upload it and make it into a Google Sheet.

It's a manual process, so I'm not sure if ithat meets your needs, but it's one way to do it.

Help Doc: http://www.gqueues.com/help/export#single

example