CODEWITHSUNDEEP
xmlxpathgoogle-apps-scriptgoogle-sheets
Harry12345
Harry12345

Reputation: 1160

Google Spreadsheets ImportXML into 2 columns

I have an xml document that looks like this:

<Time>
<ID>22653948</ID>
<Job>
<ID>J000010</ID>
<Name>NEP0001_Evesham</Name>
</Job>
<Task>
<ID>12379652</ID>
<Name>Assemble</Name>
</Task>
<Staff>
<ID>68684</ID>
<Name>Ruiardh Robinson</Name>
</Staff>
<Date>2012-10-29T00:00:00</Date>
<Minutes>180</Minutes>
<Note/>
<Billable>true</Billable>
</Time>

The document contains multiple <Time> entries and what I'm trying to do is import all the <Date> and <Minutes> fields for each task, only where the date contains the string "2014". I can import all the dates fields from 2014 using:

=QUERY(ImportXML('Time Data'!F2, "//Time/Date"), "SELECT * WHERE Col1 contains '2014-04'")

But I can't get the corresponding minutes field for the same entry, I know you can do multiple xpath queries using the | operator and I tried the following but it doesn't work as it just returns the first date field from the first entry for some reason.

=QUERY(ImportXML('Time Data'!F2, "//Time/Date | //Time/Minutes"), "SELECT * WHERE Col1 contains '2014-04'")

Does anyone know how I can import both columns only where the date contains "2014"?

Upvotes: 0

Views: 2266

Answers (1)

Tobias Klevenz
Tobias Klevenz

Reputation: 1645

You could use:

//Time/*[self::Date or self::Minutes]

however the way google reads the XML, this will populate across rows and not columns, so if you have the query in A1 the result will be

   A                    B                  C     ...
1 2012-10-29T00:00:00
2 180

So if you want this to be in columns you could just add a 2nd ImportXML query in B1 that uses //Time/Minutes

Personaly however I'd just import the whole XML in another sheet and then query that.

Btw you could also use //Time[starts-with(Date, '2014')] instead of wrapping the ImportXML in QUERY

Upvotes: 3

Related Questions