Search code examples
google-apps-scriptgoogle-sheets

Is it possible to record exact time of last update of data obtained through importhtml function in Google Sheets?

I have an url in cell A1. I am pulling data using using script

`=IMPORTHTML(A1, "table", 0)`

in cell A3 which gives an array A3:F33. I want to record the exact time of the last update/fetch using importhtml function. The exact recorded time shall be shown in cell B1.

Solution

  • I'm worried that in the current stage, the detailed refresh time of IMPORTHTML might not be able to be directly retrieved. So, in this case, I would like to propose a workaround using Google Apps Script. The flow of this workaround is as follows.

    1. Put your formula of =IMPORTHTML(A1, "table", 0) into a cell "A2". By this, the table is retrieved from the URL of "A1".
    2. Remove the formula by copying the values to the same cells.
    3. Put an update time into cell "B1".

    The script is periodically run by the time-driven trigger.

    When this flow is reflected in a sample script, it becomes as follows.

    Sample script:

    Please copy and paste the following script to the script editor of Google Spreadsheet. And, please set your sheet name in the function sample.

    In order to install a time-driven trigger, first, please run installTrigger. In this sample, the function sample is run every 1 hour. By this, =IMPORTHTML(A1, "table", 0) is refreshed every 1 hour.

    function installTrigger() {
  const functionName = "sample"; // This is the same value as the below function name.

  const t = ScriptApp.getProjectTriggers().find(e => e.getHandlerFunction() == functionName);
  if (t) ScriptApp.deleteTrigger(t);
  ScriptApp.newTrigger(functionName).timeBased().everyHours(1).create();

  sample(); // Added
}

function sample() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const formula = '=IMPORTHTML(A1, "table", 0)'; // This is from your question.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getRange("A2").setFormula(formula);
  SpreadsheetApp.flush();
  const range = sheet.getDataRange();
  range.copyTo(range, { contentsOnly: true });
  sheet.getRange("B1").setValue(new Date());
}

    • When sample is run, the above flow is run.

    • If you want to refresh =IMPORTHTML(A1, "table", 0) every 30 minutes. Please modify ScriptApp.newTrigger(functionName).timeBased().everyHours(1).create(); to ScriptApp.newTrigger(functionName).timeBased().everyMinutes(30).create();.

    • Of course, you can directly manually run sample. But, from your question, I guessed that you might have wanted to periodically retrieve the values with =IMPORTHTML(A1, "table", 0). So, I proposed executing the function with the time-driven trigger.

    Note:

    • This script supposes that the URL of https://www.bseindia.com/markets/equity/EQReports/MostActiveScrips.aspx is existing in cell "A1". Please be careful about this. Of course, this URL can be also put into the cell or the formula using a script.

    Reference: