Google Sheets script add formula into cell but cell value doesn't update

Question

I have a script that when I run it, should add a formula info cells, if the cells are empty, the script works without any problems but if I try to run it again to update the values of the formula, the values doesn't change, here is the script:

function addInstructions(){
  var sheetI = SpreadsheetApp.getActive().getSheetByName('Sheet');
  
  var i,j;
  for(i=7, j=7; i<57, j<57; i=i+2, j++){
  var cell = sheetI.getRange(i,4);
  cell.setFormula("=medInstructions("+i+")");

  var cell2 = sheetI.getRange(i+1,2);
  cell2.setFormula("=medInstructions2("+j+")");
  }
}

What I tried to do is add another script that clear the content of the cells before added the formular but for some reason that doesn't work, I also added sleep utility after the clear function and lastly I tried to set the formula to empty first before adding the actual formula but none of these had worked, here is the script with my trials

function addInstructions(){
  clearInsatructions();
  Utilities.sleep(200)
  var sheetI = SpreadsheetApp.getActive().getSheetByName('Sheet');
  
  var i,j;
  for(i=7, j=7; i<57, j<57; i=i+2, j++){
  var cell = sheetI.getRange(i,4);
  cell.setFormula("")
  cell.setFormula("=medInstructions("+i+")");

  var cell2 = sheetI.getRange(i+1,2);
  cell2.setFormula("")
  cell2.setFormula("=medInstructions2("+j+")");
  
  }
}

the custom functions are:

function medInstructions(j) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var infoSheet = ss.getSheetByName("sheet"); const meds = ["var1", "var2"] const instructions =["Ins 1", "Inst 2"] var med = infoSheet.getRange(j,2).getValue().toString().toLowerCase() if(med.indexOf(meds[0].toString().toLowerCase())>-1){ var info = instructions[0] } else if(med.indexOf(meds[1].toString().toLowerCase())>-1){ var info = instructions[1] } else if(med==0){ var info = "" } else{ var info = "Other" } return info }

function medInstructions2(j) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var infoSheet = ss.getSheetByName("sheet"); const meds = ["var3", "var4"] const instructions =["Ins 3", "Inst 4"] var med = infoSheet.getRange(j,2).getValue().toString().toLowerCase() if(med.indexOf(meds[0].toString().toLowerCase())>-1){ var info = instructions[0] } else if(med.indexOf(meds[1].toString().toLowerCase())>-1){ var info = instructions[1] } else if(med==0){ var info = "" } else{ var info = "Other" } return info } 

Any ideas how to fix this?

Answer 1

Try the following

function addInstructions(){
  clearInsatructions();
  Utilities.sleep(200)
  var sheetI = SpreadsheetApp.getActive().getSheetByName('Sheet');
  
  var i,j;
  for(i=7, j=7; j<57; i=i+2, j++){

    // Get the cells
    var cell = sheetI.getRange(i,4);
    var cell2 = sheetI.getRange(i+1,2);

    // Clear the cells
    cell.clearContent();
    cell2.clearContent();

    // Apply the cells clearing
    Spreadsheet.flush();

    // Add the custom function formulas
    cell.setFormula("=medInstructions("+i+")");
    cell2.setFormula("=medInstructions2("+j+")");
  
  }
}

Another option is to add add a random number as second parameter of the custom functions. i.e.

cell.setFormula("=medInstructions("+i+","+Math.random()+")");