I'm building a Google Sheet Add-on and I have a script that copies a few sheets from a shared sheet (includes formulas and formatting). The script properly copies everything over, however the formulas return an error when it first loads. The formula is correct and I am able to eliminate the error simply by selecting the cell then pressing enter to move out of the cell.

In the image below, you can see that I have cleared the errors in Column C, all the other cells still have the error and are marked as such.

Is there a way to programmatically clear these errors? To be clear, the formula works but for some reason it throws an error to start. I tried SpreadsheetApp.flush() but that didn't do anything.

Thanks for helping!
EDIT - I tried =iferror(original formula, value if error) which gets rid of the error message, but it doesn't force the spreadsheet to evaluate the formula.

CodePudding user response：

If you click on the spreadsheet settings, you can have the sheet recalculate every minute. This is basically the same is clicking a cell and hitting enter. See screenshots below.

Alternatively, I think you could probably just simulate your enter actions by doing this...?

function updateFormulas() {
  // ss is your sheet
  var theRange = ss.getDataRange();
  var someFormulas = theRange.getFormulas();
  theRange.setFormulas(someFormulas);
}