Formatting IMPORTXML Xpath query into readable data for Google Sheets

Question

I am importing XML data to a google sheet which has the following structure: edit: URL: https://sonicstate.com/news/tools/revive_stats/client_camp_3785.xml

<data>
    <campaignId>15802</campaignId>
    <campaignName>Some name</campaignName>
      <startDate>
       <year>2021</year>
       <month>12</month>
       <day>02</day>
     </startDate>
     <endDate>
       <year>2021</year>
       <month>12</month>
       <day>13</day>
     </endDate>
</data>
<data>
.... another record
</data>

I want the results of multiple rows to be imported with date values concatenated so they can appear in manageable rows in the sheet I can then query while the other values will appear in their own cells with a row for each record. eg

15802 | Some name | 2021/12/02 | 2021/12/13

15803 | Another name | 2021/11/30 | 2021/12/04

I have tried:

IMPORTXML("myurl" , "//data/campaignId | //data/campaignName | //data/startDate/year | //data/startDate/month|//data/startDate/day")

But each value returns on a separate row with cells for year, month, day eg:

15802
Some Name
Year | Month | Day
15802
Another Name
Year | Month | Day
etc

I also tried:


IMPORTXML("myurl" , "concat(//data/campaignId , //data/campaignName , //data/startDate/year,'/', //data/startDate/month,'/',//data/startDate/day")

But that only returns a single record. I'm struggling to find the right terms to search for what I am trying to achieve. Also Sheets XMLIMPORT uses XPath 1.0 which limits the functions available

score 1 · Answer 1 · answered Oct 31 '21 at 10:56

Try this

in A1 : your url

in A2 : your function without any change

in B1 : put 5 as you request 5 values per item

in B2 :

=ARRAYFORMULA(VLOOKUP(SEQUENCE(ROUNDUP(COUNTA(A2:A)/B1),B1,ROW(A2)),{ROW(A2:A),A2:A},2,0))

https://docs.google.com/spreadsheets/d/1nFcPgXgRc11-WWICG8Y8KEsB4qAoOt1lIbptHzXdC4M/edit?usp=sharing

score 1 · Answer 2 · answered Oct 31 '21 at 11:10

1

try:

=INDEX({IMPORTXML(A1, "//data/campaignId"),
 IMPORTXML(A1, "//data/campaignName"), 
 IMPORTXML(A1, "//data/startDate/day")&"/"&
 IMPORTXML(A1, "//data/startDate/month")&"/"&
 IMPORTXML(A1, "//data/startDate/year")})

answered Oct 31 '21 at 11:10

player0

124,011
12
67
124

Thanks, that would certainly work,but due to the number of IMPORTXML alreeady in this sheet it may well overload the number of requests, I am already seeing some loading issues and was looking for a way to reduce the number of calls to that fuction – Nowski Oct 31 '21 at 11:54

player0 · Accepted Answer · 2021-10-31T17:27:08.030

1

try:

=INDEX(SUBSTITUTE(TRIM(SPLIT(FLATTEN(SPLIT(QUERY(FLATTEN(QUERY(TRANSPOSE(
 IFERROR(IF(0=MOD(ROW(A:A)-1, 5)-{0, 1, 4, 3, 2}, {"♦","","","",""}&TEXT(
 IMPORTXML(A1, "//data/campaignId|//data/campaignName|//data/startDate/year|//data/startDate/month|//data/startDate/day"), 
 {"@","@","@","@","@"})&{"♠","♠","","♣","♣"}, ))),,9^9)),,9^9), "♦")), "♠")), "♣ ", "/"))

formula explanation

shorter fx:

=INDEX(SUBSTITUTE(TRIM(SPLIT(FLATTEN(SPLIT(QUERY(IFNA(
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 5), {0, 1}), "♦", )&
 IMPORTXML(A1, "//data/campaignId|//data/campaignName|//data/startDate/year|//data/startDate/month|//data/startDate/day")&
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 5), {0, 1, 2, 3, 4}), "♠","♠","♣","♣","")),,9^9), "♦")), "♠")), "♣ ", "/"))

edited Oct 31 '21 at 17:27

answered Oct 31 '21 at 14:22

player0

124,011
12
67
124

1

Wow! Thats intense! It worked (took a while to populate) I need to figure out how to expand it to pull more data in - gonna take me a little while to figure out whats going on but a brilliant and single request solution. – Nowski Oct 31 '21 at 14:54
Ok, I need to add the enddate (also formatted) and impressions values in there for each row but, I just cant decipher what you did, much to my shame! I figured that I should be able to figure that out from any answers I got but your expertise is way beyond mine. – Nowski Oct 31 '21 at 15:09
@Nowski see: https://docs.google.com/spreadsheets/d/1G5ClGPjAdMltTD5Ju37zj7B5BR99O7k-ycoG5xedcUM/edit#gid=385434737 – player0 Oct 31 '21 at 16:08
1

Amazing, thanks so much for your assistance, I will take a look at that and figure out what I need from it. Much appreaciated – Nowski Oct 31 '21 at 16:35
@Nowski added shorter alternative – player0 Oct 31 '21 at 17:19
1

absolutely clever/smart ! – Mike Steelson Nov 01 '21 at 10:08
Thanks! I did get the long version working. Now I just saw the shorter version, not figured out how to add more bits of information to shorter one though. Now I have to figure out how to loop that over the selected importxml sources to create a neat row of results (see https://stackoverflow.com/questions/69795698/google-sheets-increment-formula-in-next-available-row) – Nowski Nov 01 '21 at 17:17

Formatting IMPORTXML Xpath query into readable data for Google Sheets

3 Answers3

formula explanation

Linked