REF error when setting cell formula Google Script

Question

I have created a custom function called =matchlist using apps script. That function is working with no problem. I also created another function called refreshTBA which clears the content of a certain cell (A2) and replaces it with the formula =matchlist(Setup!$b$2). When I run this function while I have the sheet open, there is no problem. However, any time I navigate away from the page and open it back up, the function in the cell is =matchlist(#REF).

Here is the code for the refreshTBA function:

function refreshTBA() {
  var ss = SpreadsheetApp.getActive().getSheetByName("MatchImport")
  var cell = ss.getRange("A2")
  cell.clearContent()
  cell.setFormula('=matchlist(Setup!$b$2)')
  var cell = ss.getRange("A300")
  cell.clearContent()
  cell.setValue('Refreshed') 
}

Jared Jared · Accepted Answer · 2019-02-26T19:15:33

I solved this thanks to help on the Google Product Forum.

When putting a formula using the setFormula paramter, there is no need to include the equal sign. If I use setValue then I should

REF error when setting cell formula Google Script

1 Answers