How to Update Value Permanently from ImportXML Function

Question

In Google Sheets, I have a spreadsheet with over 150 importXML functions. I am using this formula to pull YouTube views from live streams from my organization over the past year. Occasionally when the spreadsheet is opened and the importXML function refreshes, it will display #ERROR until it refreshes again (at which point it will return a value with no errors). It will display #ERROR only on a handful of random cells each time and the rest of the numbers refresh with no problems. And usually if I wait about 5 minutes it will refresh again and the cells with #ERROR will return the actual value. Although annoyingly, some cells at this point which already had actual values will now randomly return #ERROR.

I believe I could solve this problem if only I could command the cell to display the last updated number instead of #ERROR. How can I get Google Sheets to update a static value instead of having to rely on the function to refresh every time the sheet is loaded?

Example: Last time the sheet updated on row 30 the number was 3,045. But I refreshed the page and now it displays #ERROR at the end of row 30.

Instead of #ERROR, how can I get the sheet to display the last value (3,045) until the formula refreshes without returning an error. Let's say now the value is 3,047. So ideally the formula would display 3,047 until it refreshes again, and I'd never had to see that pesky #ERROR again.

I have found a way to make the formula display '0' instead of #ERROR, and I have manually entered the last updated number on several problem cells, but this is not an automatic solution.

To make the formula display "0" instead of #ERROR I have added an =IFERROR wrapper around the formula. I would like to use this wrapper to display the last updated number instead of "0"

Main formula:

=value(REGEXREPLACE(text(importxml(F173,"//*[contains(@class, 'watch-view-count')]"),0)," view(s)?",""))

Main formula with IFERROR:

=IFERROR(value(REGEXREPLACE(text(importxml(F173,"//*[contains(@class, 'watch-view-count')]"),0)," view(s)?","")),0)

Main formula with IFERROR and "//@last" which DOES NOT WORK

=IFERROR(value(REGEXREPLACE(text(importxml(F173,"//*[contains(@class, 'watch-view-count')]"),0)," view(s)?","")),"//@last")

Using "//@last" as the IFERROR value instead of "0"

Expected: The last number updated is displayed in the cell instead of #ERROR

Results: The cell just displays //@last when returned with an error.

Answer 1

there is no //@last parameter in Google Sheets. the best practice is to use blank IFERROR like:

=IFERROR(formula())

otherwise, you would need a script which will periodically archive your whole sheet into another sheet and then you could call it via the 2nd parameter of IFERROR

Answer 2

Maybe you can try to replace the method instead of value when IFERROR occur, which mean you can try to importxml() once again once you get the error, something like:

=IFERROR(importXML(),importXML());

or maybe try it twice:

=IFERROR(importXML(),IFERROR(importXML(),importXML()))

Answer 3

I have around 100 fields in different same spreadsheet having same issue. Google should have caching system of last known good figure.

Instead of the spreadsheet keep refreshing itself, using chained IFERROR within same spreadsheet, I just duplicated same sheet with just the important importXML cells in other spreadsheets. Chaining 2 up. There IFERROR >> Spreadsheet 2 >> IFERROR >> Spreadsheet 3 >> IFERROR "refresh" - then i know all 3 has failed! Going by possibilities, very unlikelihood all 3 same similar importxml cell would fail simultaneous.

Reduces much of my error calculations. I too added a checkbox next to the cell. If value = TRUE, importxml. FALSE > "". Manually tick, untick to refresh.

Am not a coder, if someone has a scripting solution which can simply run through specified ranges of cell having "refresh" remark and to automate task of UNTICK checkbox and subsequently re-TICK, should do the trick.