I'm trying to search a range of cells, I already have the formulas set up for filtering. I'm trying to determine three different values a cell could have and use setFormula depending on which value the cell matches. Her's what I've come up with so far. It's working for the first two formulas but isn't setting "formula 3" if both cells contain data.

function setFormulas(){
    var ss = SpreadsheetApp.getActive()          
    var sheet = SpreadsheetApp.getActiveSheet()
    var cell = ss.getActiveCell()
    var cell1 = ("C2");
    var formulaCell = ("A5");
    var cell2 = ("C3");
    var cell1isblank = SpreadsheetApp.getActiveSheet().getRange(cell1).isBlank()
    var cell2isblank = SpreadsheetApp.getActiveSheet().getRange(cell2).isBlank()


    if (cell1 == "0" ) {
        SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("formula1")
    }
    else if (cell2 == "0" ) {
        2SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("formula2")
    }

}

    else {
       SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("Formula3")
    }

I'm trying to search a range of cells, I already have the formulas set up for filtering. I'm trying to determine three different values a cell could have and use setFormula depending on which value the cell matches. Her's what I've come up with so far. It's working for the first two formulas but isn't setting "formula 3" if both cells contain data.

function setFormulas(){
    var ss = SpreadsheetApp.getActive()          
    var sheet = SpreadsheetApp.getActiveSheet()
    var cell = ss.getActiveCell()
    var cell1 = ("C2");
    var formulaCell = ("A5");
    var cell2 = ("C3");
    var cell1isblank = SpreadsheetApp.getActiveSheet().getRange(cell1).isBlank()
    var cell2isblank = SpreadsheetApp.getActiveSheet().getRange(cell2).isBlank()


    if (cell1 == "0" ) {
        SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("formula1")
    }
    else if (cell2 == "0" ) {
        2SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("formula2")
    }

}

    else {
       SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("Formula3")
    }

• javascript
• if-statement
• google-sheets

1 Answer 1

Fist, you have a bracket in the wrong place. Second, you need to do the true/false compare to the cellisblank variables. Also, there is a typo (2SpreadsheetApp). Try try this:

function setFormulas(){
   var ss = SpreadsheetApp.getActive()          
   var sheet = SpreadsheetApp.getActiveSheet()
   var cell = ss.getActiveCell()
   var cell1 = ("C2");
   var formulaCell = ("A5");
   var cell2 = ("C3");
   var cell1isblank = SpreadsheetApp.getActiveSheet().getRange(cell1).isBlank()
   var cell2isblank = SpreadsheetApp.getActiveSheet().getRange(cell2).isBlank()

    if (cell1isblank == false && cell2isblank == true) {
  SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("=formula1")
    }
    else if (cell2isblank == false && cell1isblank == true ) {
SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("=formula2")
    }
  //}
   else {
  SpreadsheetApp.getActiveSheet().getRange(formulaCell).setFormula("=Formula3")
  }
}