Hide a range of columns using script - Google Sheets

Question

So I have got a spreadsheet that is basically a diary of appointments containing 12 months, I have the below script contained within the spreadsheet that hides columns (to save time) the problem is that there is too much data and this takes a long time, especially the hideAll function!

I was wondering if there is a way I can change these functions to hide the columns as ranges instead of as individual columns, I haven't managed to find anything like this so far. I would need to be able to set up the menu the same and be able to hide each month individually and if possible hide all as a function also (like the existing script but in blocks not individually). This has to be ranges as I have columns in between each month that need to stay visible and not be hidden.

Please see example spreadsheet below (this is not an up to date version but the script it contains is what is used live on the real version!)

https://docs.google.com/spreadsheets/d/1sOlQEzG1D29RaY86YeR1Da--c8t94J-ZAGjv52U4dsY/edit?usp=sharing

function onOpen(){
SpreadsheetApp.getUi().createMenu('Sophie\'s Menu :)')
  .addItem('Relative validation', 'copyValidation')
  .addItem('Relative rows, absolute columns', 'copyValidationColumnsAbsolute') 
  .addItem('Absolute rows, relative columns', 'copyValidationRowsAbsolute') 
  .addItem('Hide All Months', 'hideAll')
  .addItem('Hide January', 'hideCols1')
  .addItem('Hide February', 'hideCols2')
  .addItem('Hide March', 'hideCols3')
  .addItem('Hide April', 'hideCols4')
  .addItem('Hide May', 'hideCols5')
  .addItem('Hide June', 'hideCols6')
  .addItem('Hide July', 'hideCols7')
  .addItem('Hide August', 'hideCols8')
  .addItem('Hide September', 'hideCols9')
  .addItem('Hide October', 'hideCols10')
  .addItem('Hide November', 'hideCols11')
  .addItem('Hide December', 'hideCols12')
  .addToUi();
}

function copyValidation(rowsAbsolute, columnsAbsolute)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var r = ss.getActiveRange();
  var dv = r.getDataValidations();
  var dvt = dv[0][0].getCriteriaType();
  if (dvt != SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) return;
  var dvv = dv[0][0].getCriteriaValues();
  Logger.log(dvv);
  for (var i = 0; i < dv.length; i++)
  {
    for (var j = i ? 0 : 1; j < dv[0].length; j++)
    {

  dv[i][j] = dv[0][0].copy().withCriteria(dvt, [dvv[0].offset(rowsAbsolute ? 
  0 : i, columnsAbsolute ? 0 : j), dvv[1]]).build();
    }
  }
  r.setDataValidations(dv);
}

function copyValidationRowsAbsolute()
{
  copyValidation(true, false);
}

function copyValidationColumnsAbsolute()
{
  copyValidation(false, true);
}

function hideCols1(){

showhideCols('3,4,5,6,7,8,9,10,11,12,13,14,15,16,
17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33',false);
}

function hideCols2(){
  showhideCols('35,36,37,38,39,40,41,42,43,44,45,46,47,
  48,49,50,51,52,53,54,55,56,57,58,59,60,61,62',false);
}

function hideCols3(){

showhideCols('64,65,66,67,68,69,70,71,72,73,74,75,76,77,
78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94',false);
}

function hideCols4(){
  showhideCols('96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,
  111,112,113,114,115,116,117,118,119,120,121,122,123,124,125',false);
}

//Right up to hideCols12 

function hideCols12(){
  showhideCols('348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,
  364,365,366,367,368,369,370,371,372,373,374,375,376,377,378',false);
}

//I even have a hideAll function that contains every single column listed above! 

function showhideCols(colscsv,show){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  if(!colscsv){
    var resp=SpreadsheetApp.getUi().prompt('Enter Column Numbers separated by 
commas.', SpreadsheetApp.getUi().Button.OK);
    var cA=resp.getResponseText().split(',');
  }else{
    var cA=colscsv.split(',');
  }
  for(var i=0;i<cA.length;i++){
    if(show){
      sh.showColumns(cA[i]);
    }else{
      sh.hideColumns(cA[i]);
    }
  }
}

random-parts random-parts · Accepted Answer · 2017-12-09T00:10:01

If the indexes in the hideColsN() are static, then you only need to send the starting columnIndex and the numColumns in the range to hideColumns(columnIndex, numColumns)

function hideCols1(){
  showhideCols(3, 31, false);
}

function showhideCols(start, amount, show){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();

  // set variables to the list of numbers from the prompt
  // may want to change it to take the startIndex and number of columns to hide/show. 
  // also there was not a way listed to determine if the columns should be hidden or not.
  if(!start){
    var resp=SpreadsheetApp.getUi().prompt('Enter Column Numbers separated by commas.', SpreadsheetApp.getUi().ButtonSet.OK);
    var cA=resp.getResponseText().split(',');
    var start = cA[0];
    var amount = cA.length;
    var show = false;
  }
  // Show or hide column ranges in one call
  if(show){
    sh.showColumns(start, amount);
  }else{
    sh.hideColumns(start, amount);
  }

}

edit: I may be overlooking it, but I didnt see your hideAll() method so that was not added to this answer

Hide a range of columns using script - Google Sheets

1 Answers