Using script via custom menu to one-click refresh formulas in cells rather than clicking into cells individually to refresh formulas in cells

Solution 1:

Without any script ...

• add a tab and a checkbox in A1
• in B2 =iferror(QUERY(indirect("Pets!A2:D11"),"SELECT A WHERE B>5"),refresh!$A$1)
• in C2 =iferror(QUERY(indirect("Pets!A2:D11"),"SELECT C WHERE D>5"),refresh!$A$1)

to activate all formulas evrywhere, check/unchek the box in A1*

you can also do that by a simple script

function refresh() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('refresh')
  sh.getRange('A1').setValue(!sh.getRange('A1').getValue())
}

edit : the basic method by script is to clear the cells and then re-build by script the formulas

Solution 2:

Add this as a script:

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Refresh Formulae").addItem("Refresh", "refresh").addToUi()
}

function refresh() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getSheetByName("report overview")
  const range = sheet.getRange("A2:B2")
  
  range.setFormulas(range.getFormulas())
}

Rundown:

• On open, create a menu item to refresh the formulae
• Once authorised and run, this will re-set the formulae in A2 and B2

References:

• getFormulas()
• setFormulas()