0

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

1 Answer 1

0

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 pagejavascript.infoMozilla Developer Network and Apps Script at Stack Overflow.

1
  • Thank you!! Worked perfectly!
    – K.Newman
    Jun 16, 2021 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.