CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user3150100
user3150100

Reputation: 45

Function to toggle Boolean value in spreadsheet cell

I want this function to toggle the Boolean value in cell D38 from true to false, or from false to true. 

function changeState() {
  var a=SpreadsheetApp.getActiveSheet().getRange('D38');
  a=!a;
  SpreadsheetApp.getActiveSheet().getRange('D38').setValue(a);
};

It's not currently doing what I want: It will convert a TRUE to FALSE, but will not convert a FALSE to TRUE no matter what.

Why is it behaving like this, and how do I fix it?

Upvotes: 2

Views: 5474

Answers (1)

p.s.w.g
p.s.w.g

Reputation: 149040

From the documentation, getRange returns an object of type Range. The problem is that !a is negating the truth value of this object, not the value stored in the cell. So regardless of what the actual value is stored within that cell this will always return false (unless a is null).

You need to get the actual value from the cell using the appropriate getValue / setValue methods:

function changeState() {
   var a = SpreadsheetApp.getActiveSheet().getRange('D38'); 
   var value = a.getValue();
   value = !value;
   a.setValue(value);
};

Or more simply:

function changeState() {
   var a = SpreadsheetApp.getActiveSheet().getRange('D38'); 
   a.setValue(!a.getValue());
};

Upvotes: 4

Related Questions