CODEWITHSUNDEEP
if-statementgoogle-apps-scriptgoogle-sheetsconditional-statements
Aaron Morris
Aaron Morris

Reputation: 15

How to write If ELSE statement in Google Sheets to Hide columns dependent on cell value

Trying to write and if statement in Google sheets to hide a range of columns dependent on cell value in D4

If D4 == 1 hide columns H:AA
If D4 == 2 hide columns O:AA
Else show A:AA

Code below:

var cell = "D4";
var value1 = 1;
var value2 = 2;
var value3 = 3;
var activeSheet = ss.getActiveSheet();

function HideColumn(); {
    If(cell == value1) {
        activeSheet.hideColumn("H:AA");
        Else If(cell == value2) {
            activeSheet.hideColumn("O:AA");
            Else If(cell == value3) {
                activeSheet.unhideColumn("H:AA");
            }
        }
    }
}

Upvotes: 0

Views: 367

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

Your code needs some modifications:

var ss=SpreadsheetApp.getActive();
var value1 = 1;
var value2 = 2;
var value3 = 3;
var activeSheet = ss.getActiveSheet();
var cell = activeSheet.getRange("D4").getValue();

function HideColumn() {
  if(cell == value1) {
      activeSheet.hideColumns(8, (27-8+1));
  }
  else if(cell == value2) {
        activeSheet.hideColumns(15, (27-15+1));
  }
   else if(cell == value3) {
      activeSheet.hideColumns(8, (27-8+1));
  }
}

Explanations:

  1. If you want to hide several columns rather than one, you need to use the methods hideColumns(columnIndex, numColumns) (mind the notation)
  2. The if and else if statements should be separate rather than nested (note the brackets)
  3. if and else statements in Apps Script are written in low case
  4. The correct way to obtain the content of a cell is var cell = activeSheet.getRange("D4").getValue();

I hope my answer was helpful for you.

Upvotes: 1

Related Questions