0

I need to refresh all of my custom formulas via a script in Google Sheets but this seems to take forever (like, 30 seconds for 100 cells). There will potentially be thousands of cells with my custom formula so I must come up with a better way. I have:

function refresher(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getDataRange();
  var columns = selection.getNumColumns();
  var rows = selection.getNumRows();
  for (var column=1; column <= columns; column++){
    for (var row=1; row <= rows; row++){
      var cell=selection.getCell(row,column);
      var formula = cell.getFormula();
      if (formula.startsWith("=myfunc(")){
        cell.setFormula(formula.replace("=myfunc(", "?myfunc("));
      }
    }
  }
  SpreadsheetApp.flush();
  for (var column=1; column <= columns; column++){
    for (var row=1; row <= rows; row++){
      var cell=selection.getCell(row,column);
      var formula = cell.getFormula();
      if (formula.startsWith("=?myfunc(")){
        cell.setFormula(formula.replace("=?myfunc(", "=myfunc("));
      }
    }
  }
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
user_87625129
  • 89
  • 5
  • Does this answer your question? [Custom function won't refresh as inputs are changed](https://stackoverflow.com/questions/56893480/custom-function-wont-refresh-as-inputs-are-changed) – TheMaster May 22 '20 at 04:59
  • Related: https://stackoverflow.com/questions/56798247 – TheMaster May 22 '20 at 05:00

1 Answers1

3

In order to achieve your goal, how about using TextFinder? In this case, I think that the process cost might be able to be reduced. From your script, when TextFinder is used for your situation, it becomes as follows.

Sample script:

function refresher() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const formula = "=myfunc";
  const tempFormula = "=sampleFormula";
  sheet.createTextFinder("^\\" + formula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula);
  sheet.createTextFinder("^\\" + tempFormula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
}

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Cool. Thanks! Can you explain why I should use `"^\\"` as well as `useRegularExpression(true)`? – user_87625129 May 23 '20 at 01:29
  • It seems to get stuck in the `Loading...` state a lot of the times I try to refresh the formulas – user_87625129 May 23 '20 at 01:33
  • 1
    @user_87625129 Thank you for replying. A1. `\\` of `^\\` is used for `=` for using it as the regex. A2. `Loading...` means that the formulas were updated. Unfortunately, I cannot replicate your situation. I apologize for this. For example, when `SpreadsheetApp.flush()` is put to the last line of `refresher()`, what result will you get? – Tanaike May 23 '20 at 01:42
  • It doesn't do anything. I can confirm that either way it makes a call to our API but just displays Loading... BTW, this is on Firefox. – user_87625129 May 23 '20 at 03:37
  • Seems to work fine with or without `SpreadsheetApp.flush()` in Chrome so seems to be just a Firefox bug. Weird. – user_87625129 May 23 '20 at 03:46
  • 1
    @user_87625129 Thank you for replying. About ”A2", I deeply apologize my comment was not useful for your situation. The reason that your issue cannot be resolved soon is due to my poor skill. I have to apologize for this. For example, when a simple Spreadsheet that a simple sample formula is updated is used instead of your actual Spreadsheet, what result will you get? If the same situation occurs, how about testing by changing the browser? I think that if the sample script worked, the formulas in actual Spreadsheet are required to be confirmed. How about this? – Tanaike May 23 '20 at 03:47