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);
};