CODEWITHSUNDEEP
xmlgoogle-sheetsgoogle-sheets-formula
supercarp
supercarp

Reputation: 49

Google Sheets IMPORTXML returns nothing from straightforward query

I'm trying to automate collection of Formula 1 results data using Google Sheets and IMPORTXML. There is a developer site that makes collection simple by pre-formatting into a table.

Example of results site, used to pull XML table from: http://ergast.com/api/f1/current/last/results

When inspecting the site, the results table is tagged as such:

<tr>
<th>Pos</th>
<th>No</th>
<th>Driver</th>
<th>Constructor</th>
<th>Laps</th>
<th>Grid</th>
<th>Time</th>
<th>Status</th>
<th>Points</th>
</tr>
<tr>
<td>1</td>
<td>16</td>
<td>Charles Leclerc</td>
<td>Ferrari</td>
<td>71</td>
<td>2</td>
<td>1:24:24.312</td>
<td>Finished</td>
<td>25</td>
</tr>
[....continues on with one table row per driver for 20 rows]

I simply want to pull the results into Google Sheets. My formula is:

=IMPORTXML("http://ergast.com/api/f1/current/last/results","//tr")

The error I get is "Imported content is empty."

I'm able to use this formula on other sites, for instance:

=IMPORTXML("https://en.wikipedia.org/wiki/Fish_farming","//tr")

Upvotes: 1

Views: 118

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15328

This is an xml formated file as you can see in the source

enter image description here

This site uses a namespace, so you will have to use local-name

=importxml(A1,"//*[local-name() ='Driver']")

enter image description here

Upvotes: 1

Jack Fleeting
Jack Fleeting

Reputation: 24940

That's because the table formatting is loaded using javascript - which can't be handled by IMPORTXML(). Fortunatley, the page loads its actual data, in xml format, without using javascript. So, for example,

=IMPORTXML("http://ergast.com/api/f1/current/last/results","//*[local-name()='Driver']")

will output

16  Charles Leclerc 1997-10-16  Monegasque
33  Max Verstappen  1997-09-30  Dutch
44  Lewis   Hamilton    1985-01-07  British

etc.

Upvotes: 2

Related Questions