How to refresh google spreadsheets by clicking a "button"?

Question

I tried writing an AppsScripts script to refresh sheet each time I press a button that the script is assigned to.

The only thing that must change each time is the cell with formula "=INDIRECT("A"&RANDBETWEEN(1;20))"

Answer 1

In your situation, I thought that these threads might be useful. "https://stackoverflow.com/q/56893480", "https://stackoverflow.com/q/61300428" But, in this case, the event object is used and a part of the formula is used. So, in this answer, I would like to introduce a sample script for refreshing the cells including the specific function =INDIRECT("A"&RANDBETWEEN(1;20)) by clicking a button on the Spreadsheet.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. And, please assign the function name of myFunction to a button on the Spreadsheet. By this, when you click the button, the script is run.

function myFunction() {
  const formula = '=INDIRECT("A"&RANDBETWEEN(1;20))'; // This is from your question.

  const temp = formula.replace(/([=()])/g, "\\$1");
  const dummyFormula = "=sample";
  const obj = [
    { "from": `^${temp}|^${temp.replace(/;/g, ",")}`, "to": dummyFormula },
    { "from": `^\\${dummyFormula}`, "to": formula }
  ];
  const sheet = SpreadsheetApp.getActiveSheet();
  obj.forEach(({ from, to }) =>
    sheet.createTextFinder(from).matchFormulaText(true).useRegularExpression(true).replaceAllWith(to)
  );
}

• When this script is run, the cells including a formula of =INDIRECT("A"&RANDBETWEEN(1;20)) in the active sheet are refreshed.

Note:

• This sample script is for refreshing your provided formula of =INDIRECT("A"&RANDBETWEEN(1;20)) on the active sheet. When you change the formula, this script might be required to be modified. Please be careful about this.

Reference:

• createTextFinder(findText) of Class Sheet