Failure to refresh function Regextract(Filename()) every time Google sheet opens-CodePudding

CodePudding user response：

In your situation, how about recalculating your function of =REGEXEXTRACT(FILENAME(), "\d{2} .* \d{4}") to the cell "C6" of "CASES LIST" sheet? When this is reflected in a sample script, how about the following modification?

Modified script:

function Filename() {
  return SpreadsheetApp.getActiveSpreadsheet().getName();
}

const sample = _ => "Loading..."; // Dummy.

function onOpen(e) {
  const sheet = e.source.getSheetByName("CASES LIST");
  const range = sheet.getRange("C6");
  const formula = `=REGEXEXTRACT(FILENAME(), "\\d{2} .* \\d{4}")`;
  range.setFormula("=sample()");
  SpreadsheetApp.flush();
  range.setFormula(formula);
}

In this script, when the Spreadsheet is opened, =REGEXEXTRACT(FILENAME(), "\d{2} .* \d{4}") in the cell "C6" of "CASES LIST" sheet is recalculated by a simple trigger of OnOpen.