importxml could not fetch url after repeated attempts

0
votes

I am trying to import the weather data for a number of dates, and one zip code, in Google Sheets. I am using importxml for this in the following base formula:

=importxml("https://www.almanac.com/weather/history/zipcode/89118/2020-01-21","//*")

When using this formula with certain zip codes and certain times, it returns the full text of the page which I then query for the mean temperature and mean dew point. However, with the above example and in many other cases, it returns "Could not fetch URL" and #N/A in the cells.

Thus, the issue is, it works a number of times, but by the fifth date or so, it throws the "Could not fetch URL" error. It also fails as I change zip codes. My only guess based on reading many threads is that because I'm requesting the URL so often from Sheets, it is eventually being blocked. Is there any other error anyone can see? I have to use the formula a few times to calculate relative humidity and other things, so I need it to work multiple times. Is it possible there would be a better way to get this working using a script? Or anything else that could cause this? Here is the spreadsheet in question (just a work in progress, but the weather part is my issue): https://docs.google.com/spreadsheets/d/1WPyyMZjmMykQ5RH3FCRVqBHPSom9Vo0eaLlff-1z58w/edit?usp=sharing

The formulas that are throwing errors start at column N. This Sheet contains many formulas using the above base formula, in case you want to see more examples of the problem.

Thanks!

1
web-scrapinggoogle-sheetsgoogle-sheets-formulagoogle-sheets-importxml
What happens when you use the "base formula" as an actual formula instead of nested inside other formulas? - Rubén
@Rubén Sadly, the same thing. You can see that example in cell U5. I tried it there without any variables just to see what was happening. - Brian - RGY Studio
While a demo spreadsheet could be helpful, all the relevant details should be included in the question, like formula, the formula result, the textual error message if there is any... - Rubén
The demo should be minimal reproducible example - Rubén
I apologize. I tried to include all of that. I'll edit the question for more clarity. - Brian - RGY Studio

1 Answers

2
votes

After a great deal of trial and error, I found a solution to my own problem. I'm answering this in detail for anyone who needs to find weather info by zip code and date. I switched to using importdata, transposed it to speed up the query, and used a helper cell to hold the result for each date. I then have the other formulas searching within the result in the helper cell, instead of calling import*** many times throughout. It is slow at times, but it works. This is the updated helper formula (where O3 contains the date in "YYYY-MM-DD" form, O5 contains the URL "https://www.almanac.com/weather/history/", and O4 contains the zip code:

=if(O3="",,query(transpose(IMPORTdata($O$5&$O$4&"/"&O3)),"select Col487 where Col487 contains 'Mean'"))

And then to get the temperature (where O3 contains the date and O8 contains the above formula):

=if(O3="",,iferror(text(mid(O$8,find("Mean Temperature",O$8)+53,4),"0.0° F"),"Loading..."))

And finally, to calculate the relative humidity:

=if(O3="",,iferror(if(now()=0,,exp(((17.625*243.04)*((mid(O$8,find("Mean Dew Point",O$8)+51,4)-32)/1.8-(mid(O$8,find("Mean Temperature",O$8)+53,4)-32)/1.8))/((243.04+(mid(O$8,find("Mean Temperature",O$8)+53,4)-32)/1.8)*(243.04+(mid(O$8,find("Mean Dew Point",O$8)+51,4)-32)/1.8)))),"Loading..."))

Most importantly, importdata has not once thrown the Could not fetch URL error, so it appears to be a better fetch method for this particular site.

Hopefully this can help others who need to pull in historical weather data :)