mercredi 16 octobre 2019

How to setFormula with a IF condition?

I'm creating a tool for my company and I want it to create a new sheet and have a list of them in the "main sheet". When the checkbox is checked in a line of the main sheet, I want a cell in the "secondary sheet" to show "Finish", else "not finished".

My problem is : the formula is'nt applied to the cell correctly.

I tried to avoid " or ' in the setFormula function but nothing changed and I still have "ERROR" in the cell

function newsheet(){
var s = SpreadsheetApp.getActive();
//All lines to create the new sheet
s.getRange('L1').setValue("=IF(MainSheet!H3=TRU;\"FINISH\";\"NOT FINISHED\")");
}

//I also tried the following :

function tests(){
  var s = SpreadsheetApp.getActive();
  var finished = '"FINISHED"';
  var not_finished = '"NOT FINISHED"';
  s.getRange('L1').setFormula('=IF(MainSheet!H3=TRUE;'+finished+';'+not_finished+')');
}

I want this line to apply the formula so any checkbox change at any time can modify the cell without a script but for now I have "formula analysis error" in both tests.

Aucun commentaire:

Enregistrer un commentaire