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

Currently, I have to keep clicking into individual cells with =query() formulas to refresh the cells' formulas when I import a new datasheet into the template sheet.

How this is supposed to work is that users make a copy of the template sheet every time and import a new datasheet with values that will be reflected in cells A2 and B2 on the "report overview" tab of the template copy.

Instead of clicking into individual cells with #REF! to "activate"/"refresh" the formula in those cells, is there a script that can be used with a custom menu so that users just have to click on a menu button and have specific cells like A2 and B2 be "refreshed" altogether.

Template (Google Sheets) - https://docs.google.com/spreadsheets/d/11JZMo-cgTCTPx9U6SYwDX7L26kpsQ5YDQH-IuW_3TY8

Datasheet (.xlsx/.xls) - https://docs.google.com/spreadsheets/d/1IprKWqX1OolT1k6x3OClFjMOHd1LIpsc

CodePudding user response：

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:

CodePudding user response：

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