Paste results of solved formula based on IMPORTXML as values

Question

I have Google spreadsheet with many formulas based on IMPORTXML function, and they are solving very slowly - about some days, because of the limited number of incoming requests to the site. I want to create trigger, which every 30 minutes will replace solved formulas by values. And not solved formulas calculate again if they have result #N/A

I found only how to refresh IMPORTXML by trigger Periodically refresh IMPORTXML() spreadsheet function

But how can I make a replaces?

Answer 1

I solved my problem but not totally, I have funktion that replace solved formulas by values, and not solved formulas its just refresh. But this only make for one cell, not for all.

function testfindfunc1() {
  var ss1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('18869')
  var targetrow = 37;
  var targetcell = 2;
  var result = ss1.getRange(targetrow, targetcell);
  //var formula1 = result.getFormula();
  var findVal = ss1.getRange(targetrow, targetcell).getValue();
  if( findVal == ('#N/A') || findVal == ('Loading...')) {
  var rangeToCopy = ss1.getRange(targetrow, targetcell);
  rangeToCopy.copyTo(ss1.getRange(targetrow, targetcell));
} else {
  result.setValue(findVal);
}
}