CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
NoobCoder
NoobCoder

Reputation: 137

Hiding and unhiding columns toggle button

I am trying to create a simple toggle button which continues to hide the next column from column 5 onwards every time it is clicked.

e.g.

  1. click button to hide column 6/F
  2. click button again to hide column 7/G
  3. click button again to hide column 8/H
  4. etc

And i want to create a button which does the reverse as well.

e.g.

  1. click button to unhide column 6/F
  2. click button again to unhide 7/G
  3. etc

I have the below code, what do I need to add so it continues to show/hide the next column every time I click/run the function?

function hideCol()
{
  var sheet = SpreadsheetApp.getActive().getSheetByName('testsheet'); //put the name of the sheet in place of 'Main'
  
  sheet.hideColumns(6); //F

}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Hide Columns', 'hideCol')
      .addItem("Show Columns", 'show')
      .addToUi();
}

function show() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('testsheet'); //put the name of the sheet in place of 'Main'
  sheet.showColumns(6);  //F

}

Upvotes: 1

Views: 3432

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

  • The logic would be to iterate the number of columns starting from column 5 onwards. To do this, you can create an array for which the first element is 5 up to the last available column, provided by getMaxColumns:

    var array =  Array(sheet.getMaxColumns()-4).fill().map((element, index) => index + 5);

  • If you click on the Hide Columns button and you encounter an unhidden column then you simply hide it.

  • If you click on the Show Columns button and you encounter a unhidden column then you unhide the previous one.

  • To check whether a particular column is hidden or not, you need to use isColumnHiddenByUser.

Solution:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Hide Columns', 'hideCol')
      .addItem("Show Columns", 'show')
      .addToUi();
}

function hideCol() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('testsheet'); //put the name of the sheet in place of 'Main'
  const array =  Array(sheet.getMaxColumns()-4).fill().map((element, index) => index + 5);
  for(let col of array){
     if(!sheet.isColumnHiddenByUser(col)){
         sheet.hideColumns(col);
         break;
     }
  }
}

function show() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('testsheet'); //put the name of the sheet in place of 'Main'
  const array =  Array(sheet.getMaxColumns()-4).fill().map((element, index) => index + 5);
  for(let col of array){
     if(sheet.isColumnHiddenByUser(col)){
         sheet.showColumns(col);
         break;
     }
  }
}

Upvotes: 2

Related Questions