I want to show / hide rows with macros in google sheets

user_19233769 0
Viewed 64

/* What I want with this code using macros is to hide and show multiple columns BUT with the same button. However, it does not give the expected results. I need your help. */

function Show_Hide() {
  var spreadsheet = SpreadsheetApp.getActive();
  var status = 1;

  function Show() {
    spreadsheet.getActiveSheet().getColumnGroup(3, 1).expand();
    status = 1;
  };

  function Hide() {
    spreadsheet.getActiveSheet().getColumnGroup(3, 1).collapse();
    status = 0;
  };

  if (status==1){
    Hide();
  } else{
    Show();
  }
}
google-apps-script google-sheets
1 Answers

The error in your code is that you are setting status to 1, and then checking if it is equal to 1. (It will always equal 1 with this logic).

Instead,

Show/Hide Grouped Columns:

function ShowHide() {

  const targetGroup = SpreadsheetApp.getActive()
                                    .getActiveSheet()
                                    .getColumnGroup(3, 1)

  return (targetGroup.isCollapsed())
  ? targetGroup.expand()
  : targetGroup.collapse()

}

This will check if the specified column group .isCollapsed(), if so it will be expanded, if not it will be collapsed.

Learn More:

Show/Hide Grouped & Non-Grouped Columns:

function ShowHide() {

  const sheet = SpreadsheetApp.getActive().getActiveSheet()

  const colStart = 3 // "C"
  const numOfCols = 8 // "C" => "J"

  try {

    if (sheet.getColumnGroup(colStart, 1)) { 

      return (targetGroup.isCollapsed())
      ? targetGroup.expand()
      : targetGroup.collapse()  
      
    }

  } catch (e) {

    return (sheet.isColumnHiddenByUser(colStart)) 
    ? sheet.showColumns(colStart, numOfCols) 
    : sheet.hideColumns(colStart, numOfCols)

  }

}
user_190435340
Related