ImportXML data reloading every time I open the sheet

Question

I have a problem with my Google Sheets updating the IMPORTXML data every time I open my sheet.

Does importxml function run every time I open Google Sheets? Is there a way of turning off this? I don't want it to refresh data every time I open the sheet. Surely there must be a way of controlling when the update must be done.

Answer 1

I don't think there is a built-in way to disable automatic updates of importXML, but here is a workaround. Enter the script given below in the Script Editor. It will add a new menu item, Custom > Update imported data, next time the spreadsheet is opened.

Place any importXML formulas in the first row of a sheet and precede them with a backtick, so they are not recognized immediately:

`=importXML("http://cnn.com", "//div")

This doesn't do anything on its own. But when the command "Update imported data" is executed, it will place the actual formula (without backtick) one row below, so it is executed. After that, it will replace all formulas on the sheet with their output; in particular there will not be any active importXML formula left. The original backticked formula will stay in place, so the data can be refreshed again just by using the same menu item.

function onOpen() {
  var menu = [{name: "Update imported data", functionName: "update"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function update() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.offset(1, 0).clear();
  var values = range.getValues()[0];
  for (var j=0; j<values.length; j++) {
    if (/^`=import/i.test(values[j])) {
      range.getCell(1, j+1).offset(1, 0).setFormula(values[j].slice(1));
    }
  }
  SpreadsheetApp.flush();
  var range = sheet.getDataRange().offset(1, 0);
  range.copyTo(range, {contentsOnly: true});
}

Limitations

When updating the output, the script erases everything below the first row, to make room for new data. So you can't have much else on this sheet, other than importXML. Put the rest of logic on other sheets.

Alternatively, one can modify the script to keep the first N rows unaffected, and use the rows starting with N+1 for imported data.