IMPORTXML Google Sheets Fetch URL Error (Need script trigger help on open only)

Question

My date source is: https://walletinvestor.com/forecast/achain-prediction

On the website they have three main numbers that I am trying to import to my Google spreadsheet (Current Price, 1-Year Forecast, 5-Year Forecast).

In cell C3 I have:

https://walletinvestor.com/forecast/achain-prediction

My D column is going to be used to import "Current Price" number, my E column is going to be used to import "1-Year Forecast" number, and my F column is going to be used to import "5-Year Forecast" number.

This is the code I am using in D3 to import the three numbers:

=arrayformula(regexreplace(TRANSPOSE(IMPORTXML(C3,"//div[@class='col-md-4 col-xs-12 np']//span[@class='bignum']")),"USD",""))

About 1% of the time this will work and it will import the numbers that I need, but the majority of the time I get an error of "Can't fetch URL". I believe this to be because Google Sheets is pulling in the data too frequently. What I was wondering was, is there a Google Sheets script that I could make to only pull in this data when I open the spreadsheet? I am scripting illiterate and any help would be appreciated.

Answer 1

I think that there are 2 patterns for achieving what you want.

Pattern 1 :

If you want to retrieve values as a custom function, how about this script? When you use this, for example, please put =fetch(C3) to D3.

Sample script 1 :

function fetch(url) {
  var data = UrlFetchApp.fetch(url).getContentText();
  var values = data.match(/col-md-4 col-xs-12 np\"\>([\s\S]+?)col-md-12/g)[0].match(/\s([0-9.]+)(?=[ |&])/g);
  return [values.map(function(e){return e.trim()})];
}

Pattern 2 :

If you want to retrieve values every time when you open Spreadsheet, how about this script? When you use this, please install a trigger for onOpen().

Sample script 2 :

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var urls = ss.getRange("C3:C21").getValues();
  var res = [];
  for (var i in urls) {
    var data = UrlFetchApp.fetch(urls[i][0]).getContentText();
    try {
      var values = data.match(/col-md-4 col-xs-12 np\"\>([\s\S]+?)col-md-12/g)[0].match(/\s([0-9.]+)(?=[ |&])/g);
      res.push(values.map(function(e){return e.trim()}));
    } catch(e) {
      res.push(["", "", ""]);
    }
  }
  ss.getRange("D3:F21").setValues(res);
}

Note :

• For https://walletinvestor.com/forecast/funfair-prediction, col-md-4 col-xs-12 np cannot be found in the retrieved data. So the values cannot be retrieved.
  • In this case, I couldn't understand the values you want. I'm sorry.

If I misunderstand your question, I'm sorry.