Search code examples
google-apps-scriptgoogle-sheetsimportcustom-function

Script to refresh importHTML with a checkbox in Google Sheets

Background

I am building a large spreadsheet with various economic indicators and because of the lack of commodity prices in the Google finance function, I have to use importHTML to get the data from a series of tables from business insider. However, I don't want the importHTML function to refresh automatically every so often because I don't want it running in the background when I don't need it to. I've been trying to get it to refresh the data whenever I click a checkbox, and the script would automatically uncheck the checkbox.

Applicably, I would like to build a function that allows for the refreshing of import commands by copying the cell's contents, deleting the contents, and then re-pasting the contents in the same cell. (or by in any other simpler series of commands) So, in a google sheet the formula would look something like this:

=RefreshImport(input1, input2, input3, inputx)

=RefreshImport(B3, C3, D3)

The bottom is an example of what it would look like. All inputs after input1 would be optional. So, the objective would be to be able to throw it into an IF statement in a spreadsheet and it would "refresh" the contents of the input cells.

Current Script

Since I'm new to javascript I turned to Macros to try to figure it out but I'd like there to be an IF statement reliant on the checkbox.

function RefreshCheckmark() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('Q6').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getActiveRange().setFormula('=importhtml("https://markets.businessinsider.com/commodities", "table", 2)');
  spreadsheet.getRange('Q12').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getActiveRange().setFormula('=importhtml("https://markets.businessinsider.com/commodities", "table", 3)');
  spreadsheet.getRange('Q21').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getActiveRange().setFormula('=importhtml("https://markets.businessinsider.com/commodities", "table", 4)');
  spreadsheet.getRange('Q29').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getActiveRange().setFormula('=importhtml("https://markets.businessinsider.com/commodities", "table", 5)');
  spreadsheet.getRange('Y4').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');
};

With this Macro I tried to make an IF statement in the actual sheet, which wouldn't work because it doesn't have permission to. (even though I gave it permission) Here's the IF statement I tried to use in the sheet: (Y4 is the cell with the checkbox)

=IF(Y4 = TRUE, RefreshCheckmark(), "Refresh Script")

So, now I'm having a hard time trying to convert the Macro to the function I described in the background section. I've been messing with it all day and I ran out of ideas.

Solution

  • I believe your goal and your current situation are as follows.

    • You want to run the function RefreshCheckmark() when the checkbox is checked.
    • Your script of RefreshCheckmark() works fine.
    • The checkbox is put in a cell "Y4".

    Modification points:

    • Unfortunately, the custom function cannot use setValue. I think that the reason of your issue of wouldn't work because it doesn't have permission to.. Ref
    • In order to use the method of setValue when the checkbox is checked, in this answer, I would like to propose to use OnEdit trigger. When the above points are reflected to the script, it becomes as follows.

    Sample script:

    Please copy and paste the following script to the script editor and save the project. And, please check the checkbox of "Y4". By this, the script onEdit() is automatically run by the OnEdit simple trigger and your script RefreshCheckmark() is also run.

    function onEdit(e) {
  const sheetName = "Sheet1";  // Please set the sheet name which has the checkbox at the cell "Y4".
  const range = e.range;
  if (range.getSheet().getSheetName() != sheetName || range.getA1Notation() != "Y4" || !range.isChecked()) return;

  RefreshCheckmark();  // This is your script.
}

    Note:

    • In this answer, it supposes that your script of RefreshCheckmark() works fine as you expected.

    References: