2

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?

5
  • Custom functions are recalculated only when the spreadsheet is opened and when one of the custom functions parameters changes. Please add more details: make a "minimal complete verifiable example", in other words, make an example having the minimal elements required to reproduce the problem, and include it here so other might understand what is happening and reproduce the problem. (the medInstructions and medInstructions2 are declarations are missing)
    – Rubén
    Apr 5 at 17:56
  • I updated the question and added the custom functions, also I have to add that the sheet is open, but the parameters doesn't change only the value of the formula, but If I added a function to clear the cells first why isn't that excuted? Apr 5 at 18:30
  • Thanks for your reply. It doesn't make sense to include SpreadsheetApp.flush() as the first statement of a custom function as custom functions can't change any spreadsheet element other than the value / values that they returns.
    – Rubén
    Apr 5 at 18:38
  • even when I remove this line, the issue is still present Apr 5 at 18:39
  • currently the only way the functions runs and gets the new value is if I run the clear function alone and then rerun the addinstuction function, but if I put the clear function inside the addinstuction function the clear function doesn't run, any idea how to fix that? Apr 5 at 18:47

1 Answer 1

2

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()+")");
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.