CODEWITHSUNDEEP
google-apps-script
David Wong
David Wong

Reputation: 33

Google Sheet Conditional HideColumn() & ShowColumn()

Appreciate if someone can help me out with this chunk of codes. What I am trying to achieve (step by step) is:

  1. If Checkbox in Cell "A7" is checked in Sheet1
  2. Column "P" will be automatically be unhidden in Sheet2
  3. Else if Checkbox in Cell "A7" is unchecked in Sheet 1
  4. Column "P" will be automatically be hidden in Sheet2

This is my 1st time doing coding....really grateful if a kind soul can help. Thanks.

function showHide() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("sheet1");
  var sheet2 = ss.getSheetByName("sheet2");
  var vtrue = "TRUE"
  var vfalse = "FALSE"

  sheet1.getRange('A7').activate();

  if (sheet1.getRange('A7').getValue() == vtrue)  {
    sheet2.showColumns(sheet2.getRange("P:AA"));  
  } else if (sheet1.getRange('A7').getValue() == vfalse)   {
    sheet2.hideColumns(sheet2.getRange("P:AA"));
  }       
}

Thanks a million.

Cheers.

Upvotes: 1

Views: 5056

Answers (2)

David Wong
David Wong

Reputation: 33

Thanks everyone for the tips! It helped. I have finally gotten the function working.

function showHide() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("sheet1");
  var sheet2 = ss.getSheetByName("sheet2");

  sheet1.getRange('A7').activate();

  if (sheet1.getRange('A7').getValue() == true)  {
    sheet2.showColumns(16,2);      
    } else {
    sheet2.hideColumns(16,2);    
  }   
}

Instead of getting var result from checkbox, it is already a boolean. Nosyara is right, the showColumns() API require number parameters to indicate the column(s) to hide or show.

Many thanks again!

Upvotes: 2

Oleg Imanilov
Oleg Imanilov

Reputation: 2751

The right API to hide columns is sheet2.hideColumns(columnIndex, numOfColumns) - both parameters are numbers. You can see it here .

There is good trick to learn how thinks works.

  • Go to menu->tools->Macros (New).
  • Do the action you want to do programmatically (click, hide-column, etc.)
  • Go to script editor - you'll see there the script :-)

Upvotes: 2

Related Questions