Home > Software design >  Hiding and Showing Columns Based on Value in Cell
Hiding and Showing Columns Based on Value in Cell

Time:02-08

I'm looking to create a script that hides/shows columns based off of a value in a particular cell, but I know absolutely nothing about coding. I've tried to adapt code from other answers on the site but have been unsuccessful, and don't know enough about coding to know why.

Here's what I want to accomplish: If the value in A3 is 1, show columns C-E, hide columns F-AF; If the value in A3 is 2, show columns C-H, hide columns I-AF; If the value in A3 is 3, show columns C-K, hide columns L-AF And so on in increments of 3 columns until: If the value in A3 is 10, show columns C-AF

Here is what I was able to try and piece together. I'm sure it's very wrong:

function HideSelectedBlocks() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh=ss.getSheetByName("Contract");
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var A3=sh.getRange("A3").getValue();
  for(var i=0;i<vA.length;i  )
  {
    var column=i 1;
    switch(A3)
    {
      case '1':
        if(column>=C && column<=E){sh.showColumns(column);}
        if(column>=F && column<=AF){sh.hideColumns(column);}
        break;
      case '2':
        if(column>=C && column<=H){sh.showColumns(column);}
        if(column>=I && column<=AF){sh.hideColumns(column);}
        break;
        case '3':
        if(column>=C && column<=K){sh.showColumns(column);}
        if(column>=L && column<=AF){sh.hideColumns(column);}
        break;
        case '4':
        if(column>=C && column<=N){sh.showColumns(column);}
        if(column>=O && column<=AF){sh.hideColumns(column);}
        break;
        case '5':
        if(column>=C && column<=Q){sh.showColumns(column);}
        if(column>=R && column<=AF){sh.hideColumns(column);}
        break;
        case '6':
        if(column>=C && column<=T){sh.showColumns(column);}
        if(column>=U && column<=AF){sh.hideColumns(column);}
        break;
        case '7':
        if(column>=C && column<=W){sh.showColumns(column);}
        if(column>=X && column<=AF){sh.hideColumns(column);}
        break;
        case '8':
        if(column>=C && column<=Z){sh.showColumns(column);}
        if(column>=AA && column<=AF){sh.hideColumns(column);}
        break;
        case '9':
        if(column>=C && column<=AC){sh.showColumns(column);}
        if(column>=AD && column<=AF){sh.hideColumns(column);}
        break;
        case '10':
        if(column>=C && column<=AF){sh.showColumns(column);}
        break;
      default:
    }
  }
}

I then also need two more that do essentially the same thing: If the value in A5 is 1, show columns AH-AJ, hide columns AK-BK continuing with increments of 3 columns until: If the value in A5 is 10, show columns AH-BK and If the value in A7 is 1, show columns BM-BO, hide columns BP-CP continuing with increments of 3 columns until: If the value in A7 is 10, show columns BM-CP

Here is an example document, in case it is useful: enter image description here

  • Say number 2 is selected on cell A3. After running the script function:

enter image description here

  • Then, say number 3 is selected. After running the script:

enter image description here

[UPDATE]

  • On second half assignment hideSecondHalf function, say 2 was selected on cell A5:

enter image description here

  •  Tags:  
  • Related