Google Sheets' IMPORTXML breaks randomly

1
votes

I'm writing since I'm experiencing some issues with the following IMPORTXML formula in Google Sheets:

=IMPORTXML("http://xmltestmagicplan.000webhostapp.com/xml_test.xml","/catalog/book[2]")

The formula works fine most of the times but approximately once a day it breaks, showing an error in loading the formula and giving #N/A as a result.

The workaround is changing anything in the URL (i.e. http -> https or 000webhostapp.com -> 000webhostapp.org) and then changing it back. Basically I think the workaround is forcing Google Sheets to reload the formula.

The same thing happens if I use this formula:

=IMPORTXML("http://xmltestmagicplan.000webhostapp.com/xml_test.xml")

No problem instead with the following formulas:

=IMPORTXML("https://gist.githubusercontent.com/Ram-N/5189462/raw/46db0b43ad7bf9cbd32a8932f3ab981bd4b4da7c/books.xml","/catalog/book[2]")

=IMPORTXML("https://gist.githubusercontent.com/Ram-N/5189462/raw/46db0b43ad7bf9cbd32a8932f3ab981bd4b4da7c/books.xml")

I think the issue is due to the hosting (000webhostapp.com) I'm using but I can't figure why. Any ideas?

1
google-sheetshostingweb-hostinggoogle-sheets-formulagoogle-sheets-importxml
I've had similar problems with =importrange() and =indirect(). Makes it difficult to rely on those functions. - a-burge
@a-burge, I tried with the workaround player0 suggested and it seems it works at the moment. - aragornii

1 Answers

1
votes

you can try to trick it like this:

=IFERROR(
 IMPORTXML("http://xmltestmagicplan.000webhostapp.com/xml_test.xml", "/catalog/book[2]"), 
 IMPORTXML("http://xmltestmagicplan.000webhostapp.com/xml_test.xml", "/catalog/book[2]"))

or if the switch is too fast add 1 fake switch between like:

=IFERROR(IFERROR(
 IMPORTXML("http://xmltestmagicplan.000webhostapp.com/xml_test.xml", "/catalog/book[2]"), 
 IMPORTXML("http://xmltestmagicplan.000webhostapp.ORG/xml_test.xml", "/catalog/book[2]")),
 IMPORTXML("http://xmltestmagicplan.000webhostapp.com/xml_test.xml", "/catalog/book[2]"))