I want to use an If statement that checks the cell value for "TRUE" before proceeding

Question

I want to use the If statement to check that the cells value is equal to "TRUE" before proceeding. I have created the below but there is one problematic line I cannot figure out. I have inserted a comment above the problematic line of script below.

Some notes:

The below macro runs onEdit.
I have separated it out into a separate if statement because there is a specific function I want to run if the cell value is not "TRUE".
The cell that the "TRUE" value is taken from is a Checkbox. Not sure if this is important.

I am completely new to google script. Thank you for your help :)

function onEdit(e) {

//This If statement is to ensure my macro only runs when a particular cell is edited:

if(
e.source.getSheetName() == "Daily Data" &&
e.range.columnStart == 3 &&
e.range.columnEnd == 3 &&
e.range.rowStart >= 3 &&
e.range.rowEnd <= 52 
){ 


var checkboxtest = e.range.getValue()

/*
*
*    THIS NEXT LINE IS WHERE MY ISSUE LIES
*
*/

if(checkboxtest == "TRUE"){


//This is the main section of my macro that works when using a different line above:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var daily_data = ss.getSheetByName("Daily Data");
var date_cellRow = e.range.rowStart      
daily_data.getRange(date_cellRow,4).setFormula("=NOW()").activate();      
SpreadsheetApp.flush();     
daily_data.getRange(date_cellRow,4).copyTo(daily_data.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)      
}
else 
{
//Here I will put some script to run when the cell value is not equal to "TRUE"
}
}
}
;

Welcome. The code isn't complete ( `function functionName(e)` or something similar is missing. By the other hand, it's not clear if you are using a simple or an installable trigger and if the `"TRUE"` is really a string literal or if the source cell display TRUE on the formula bar and on the cell itself. — Rubén, Sep 22 '19 at 05:21
Hi Rubén, thanks for your response. This is being used within an onEdit(e) function that runs when a checkbox is checked. I have tried removing the quotation marks from TRUE, since the checkbox has a boolean value as Sourabh said in their response, but I still cannot get it to work. — Nagaram92, Sep 23 '19 at 05:37
Meaning, add a complete onEdit function but having just the necessary code and all the necessary details to help others to reproduce the problem. — Rubén, Sep 23 '19 at 05:48

score 2 · Accepted Answer · answered Sep 22 '19 at 07:13

Your code is perfect except for 2 things :)

You need to wrap this within a onEdit(e) function as that way, e gets defined (i'm assuming you're already doing that but your current code doesn't reflect that)
In the case of a checkbox, TRUE is not a string but a boolean value (need to be in lowercase) i.e. it doesn't require quotes

This is final piece of code that should work as desired -

function onEdit(e) { // wrapping it within an onEdit(e) function
  //This If statement is to ensure my macro only runs when a particular cell is edited:

  if(
    e.source.getSheetName() == "Daily Data" &&
    e.range.columnStart == 3 &&
    e.range.columnEnd == 3 &&
    e.range.rowStart >= 3 &&
    e.range.rowEnd <= 52 
  ){ 


    var checkboxtest = e.range.getValue()

    /*
    *
    *    THIS NEXT LINE IS WHERE MY ISSUE LIES
    *
    */

    if(checkboxtest == true){ // replace "TRUE" with true


      //This is the main section of my macro that works when using a different line above:

      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var daily_data = ss.getSheetByName("Daily Data");
      var date_cellRow = e.range.rowStart      
      daily_data.getRange(date_cellRow,4).setFormula("=NOW()").activate();      
      SpreadsheetApp.flush();     
      daily_data.getRange(date_cellRow,4).copyTo(daily_data.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)      
    }
    else 
    {
      //Here I will put some script to run when the cell value is not equal to "TRUE"
    }
  }

}

Thanks for your response! I am using it within an onEdit function that defines e as you've said. I have tried removing the quotations from TRUE but it still will not work for some reason? — Nagaram92, Sep 23 '19 at 05:38
I'm hoping you've also setup the `onEdit` trigger as well - please let me know if you haven't. — Sourabh Choraria, Sep 23 '19 at 05:40
Could you please change "TRUE" to true (without quotes and in lowercase)? I've done so in my response. — Sourabh Choraria, Sep 23 '19 at 06:22

score 1 · Answer 2 · answered Sep 22 '19 at 07:28

1

Remove the quotes on the TRUE value

answered Sep 22 '19 at 07:28

Izzy_P

11
1

I gave this a shot but no luck. – Nagaram92 Sep 23 '19 at 05:38
TRUE needs to be in lowercase :) (just FYI) – Sourabh Choraria Sep 23 '19 at 07:04

I want to use an If statement that checks the cell value for "TRUE" before proceeding

2 Answers2