ImportXML not stable in Google Sheets, how can I improve that?

Question

I have ImportXML in my spreadsheet.

I am tracking the Alexa ranking of a list of websites.

Here is the formula:

=HYPERLINK(CONCAT("www.alexa.com/siteinfo/",A4), value(ImportXML(CONCATENATE("http://data.alexa.com/data?cli=10&dat=snbamz&url=",$A4),"/ALEXA/SD/POPULARITY/@TEXT")))

And the result looks like this:

Most of the data are correct but a few are not showing up, no matter how many times I refresh the page. And the rows that are not showing up correctly are just random.

What can I do? For instance, can I set up some timed out limit to be longer?

Answer 1

You probably hit the IMPORTXML limit by having too much IMPORTXML lookups in one spreadsheet. The easiest course of actions would be to set up a second spreadsheet and divide IMPORTXML lookups. After that just import the whole range of results from the 2nd sheet into your 1st master sheet with one simple IMPORTRANGE formula.