Google Sheets script - how do I replace text in formulas?

1
votes

I have a number of ImportXML functions in one sheet pulling in data from an external source. Some of these take some time to load and if Google Sheets does not receive the data in time, the function times out and I get an "#ERROR!" in the cell rather than the expected data.

Here's a sample statement:

=ImportXML("www.sourceurl.com?apply_formatting=true&apply_vis=true&cachebust=123", "//row")

If it works, I'll get something like this:

Month || Sales

2018-02 || 2098

But if it does not work, I'll get "#ERROR!"

Currently, if I want to force Google Sheets to re-try pulling the data, I'll just update the numbers in "cachebust=123" to force Google Sheets to try again. But this gets painful when having to do it over 200 cells.

How can I create some functionality such that: - If there is an error in the cell (IFERROR()), then replace the values in the formula string to something else.

Thanks for the help!

1
google-sheets

1 Answers

0
votes

You might just want to have a cell that contains the "123" bit and use string concatentation. Do this in all the cells where you use this technique. Then, when you want to rerun it, just update that one cell.

For example, cell X999 could contain "123". Then the line above would become:

=ImportXML(CONCATENATE("www.sourceurl.com?apply_formatting=true&apply_vis=true&cachebust=", X999), "//row")

Then updating X999 to contain "124" would force an update of all the ImportXML cells.