IF statement is not functioning?

Wikk 0
Viewed 72

I'm no wizard, but I've been playing around trying to learn a bit with google apps script and I'm just confused as to why it isn't working. I've looked through the documentation and have referenced other works using if + else scripts and I can't find the error. Context: E7 is a checkbox, F7 is a fill-in-the-blank.

function testO() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var getRange = mySS.getRange("E7")
  
  
  if(getRange = true){
    mySS.getRange("E7").setValue("FALSE")
  }
  else{
    mySS.getRange("F7").clearContent()
  }
}
comparison-operators conditional-statements google-sheets google-apps-script javascript
2 Answers

It looks that you need to spend some time learning the pretty basics of JavaScript. Please start by reading https://developers.google.com/apps-script/guides/sheets as it gives helpful pointers like,

  • Google Apps Script programming language is JavaScript
  • How to read and write to a Google Sheets spreadsheet.

Regarding the specifics of your code,

  1. (optional) In the mySs.getRange methods, instead of A1 use Sheet1!A1 style references. This might help to improve the code readability.
  2. In the comparison expressions for equality in your if statement, instead of the assign operator = use abstract equality == or strict equality === operators, but in this very specific case it might be better to use Range.isChecked().
  3. There are specific methods for working with checkboxes: Range.isChecked(), Range.uncheck(), Range.check();

Below is a proposal to fix your script:

function testO() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var getRange = mySS.getRange("Sheet1!E7");
  
  
  if(getRange.isChecked()){
    getRange.uncheck();
  }
  else{
    mySS.getRange("Sheet1!F7").clearContent();
  }
}

Notes:

  • Google Apps Script use the same method name for some methods that return the same type of object, i.e. Spreadsheet.getRange, Sheet.getRange, but they might require different parameters,

  • Spreadsheet.getRange might use A1 or Sheet1!A1 style references. When using A1 it will get the cell A1 from the first sheet.

  • Sheet.getRange might use A1 style, it will return the cell A1 from the corresponding sheet, no matter of its position.

userH4H60

A simple example with some explanation

function testO() {
  const ss = SpreadsheetApp.getActive();//I like havign access to the spreadsheet as a single variable.  While it may not be important now it might be in the future.
  const sh = ss.getSheetByName("Sheet0");//For clerity sake it is better to identify the sheet you are going to.  In your code you depended upon the fact that the spreadsheet has a default active sheet which is ss.getSheets()[0]
  const rg = sh.getRange("E7");//getRange returns a range no a value
  const v = rg.getValue();//getValue returns a single value
  
  if(v == true){ //= is an assignment == is a comparison 
    sh.getRange("E7").setValue("FALSE");//This is usually the value reserved for an unchecked checkbox
  }
  else{
    rg.clearContent()
  }
}
user30hu0
Related