Run Script in Google Sheets without Activating Sheet

Question

I have a spreadsheet that uses IMPORTHTML to pull information from a table into a worksheet called "Data". On a separate worksheet I run some calculations and display results. I have a macro that deletes the contents of the cell with the IMPORTHTML and pastes in the formula again so that it can reload. I have it set to run the script "on edit" so that I can use a checkbox to update the data on demand from my results worksheet.

However, when I push the button to refresh, or when I make changes to a cell, the macro causes the worksheet that is being displayed to change due to the getActive code in the macro.

Is there a way for me to have the macro run without it activating the "Data" worksheet?

BTW - I am a complete novice at this stuff so hopefully I am explaining it all correctly.

Here is my code:

function Refresh1() {
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  sheetName.getRange('A8').activate();
  sheetName.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  sheetName.getActiveRange().setFormula('=IMPORTHTML("URL","table",0)');
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('D1').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Results'), true);
};

Answer 1

Try this:

function Refresh1() {
  const range = SpreadsheetApp.getActive().getRange('Data!A8');
  range.clearContent();
  SpreadsheetApp.flush();
  range.setFormula('=importhtml("URL", "table", 0)');
}

Some of the best resources for learning Google Apps Script include the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets page, javascript.info, Mozilla Developer Network and Apps Script at Stack Overflow.