Cannot retrieve xml data with IMPORTXML in Google Sheets

Question

I am trying to retrieve and parse xml data from the following URL using

=IMPORTXML("https://monitoringapi.solaredge.com/site/894123/sensors?startDate=2020-10-01%2000:00:00&endDate=2020-10-06%2000:00:00&api_key=MGTH4B2W39EJ8O433GSMRNQHRY0WKF9C";"/siteSensors/data/telemetries/sensorsTelemetry")

or

=IMPORTXML("https://monitoringapi.solaredge.com/site/894123/sensors?startDate=2020-10-01%2000:00:00&endDate=2020-10-06%2000:00:00&api_key=MGTH4B2W39EJ8O433GSMRNQHRY0WKF9C","//date | //globalHorizontalIrradiance")

I keep on getting the error message 'Imported XML content cannot be parsed'.

I've checked the xpath in an online xpath tester and it returns data fine.

When i put the URL in a browser window I do indeed receive the XML (which curiously has a script element at the end....)

All help most gratefully appreciated,

Best regards /Colm

General Grievance · Accepted Answer · 2020-11-30 14:53:46Z

1

Add an extra &format=xml to your URL:

=IMPORTXML("https://monitoringapi.solaredge.com/site/894123/sensors?startDate=2020-10-01%2000:00:00&endDate=2020-10-06%2000:00:00&api_key=MGTH4B2W39EJ8O433GSMRNQHRY0WKF9C&format=xml";"//siteSensors/data/telemetries/sensorsTelemetry")

It looks like the API gives Google Sheets JSON data by default. According to the Solaredge API docs, you can specify the format as json, xml, or csv. (The docs say you want application/xml, but this didn't work for me.)

answered Nov 30, 2020 at 14:53

General Grievance

5,12039 gold badges40 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

carbontracking Over a year ago

Excellent ! Thank you for reading the docs better then I did.

General Grievance Over a year ago

@carbontracking Eh, I can't fault you for thinking that it returns XML data based on what you get when pasting it into the address bar. Probably some user-agent check on the site or something. Go figure.

Collectives™ on Stack Overflow

Cannot retrieve xml data with IMPORTXML in Google Sheets

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related