google apps script - hide / unhide rows based on cell value

Question

I've got a spreadsheet I use for work and been struggling with a hide/show rows depending on a specific cell value.

The spreadsheet in question creates a contract for our suppliers and the specific cell value (in my case F16) should trigger a change in the middle part of the contract by hiding/showing relevant rows with data. Luckily, all rows can be grouped in three blocs so basically the end premise of the formula/script should be:

cell F16 = "A", "B" or "C" (cell value changes by a vlookup formula in that 
cell that is connected to a specific reference number)

Block1 = rows 16 to 27
Block2 = rows 28 to 39
Block3 = rows 40 to 51

if F16 = "A" - show block1, hide block2, hide block3
if F16 = "B" - hide block1, show block2, hide block3
if F16 = "C" - hide block1, hide block2, show block3

Been playing with this:

function HideSelectedRows2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Contract"); // Enter sheet name
var row = s.getRange('AM:AM')
    .getValues(); // Enter column letter that has the texts "Unhide" and 
"Hide until here" 

// hide all rows except first
s.hideRows(2, s.getMaxRows() - 1);
for (var i = 0; i < row.length; i++) {
    // then if the row says "unhide", start unhiding from that row and 
unhide 1 row
    if (row[i][0] == 'Unhide') {
        s.showRows(i + 1);
    }

    // then if the row says 'Hide' start from the next row and hide until 
the end 
    else if (row[i][0] == 'Hide') {
        s.hideRows(i + 1);
    }
}
}

But keep getting a constant loop through rows (via column AM:AM that creates "hide" and "unhide" with an If formula) on every edit. Annoying as hell, as the blocks have editable areas.

Would like the spreadsheet to read the starting trigger (reference number) do its magic with vlookup formulas and once it changes the value in cell F16 trigger hide/unhide rows based on the premise. And stop right there.

Hope I'm making sense Help? Ideas?

Answer 1

In the sheet in which I want to hide rows in the past I created three colums: 1 that shows whether a row is hidden yes or no: =IF(SUBTOTAL(103; AD2);"No";"Yes") (column AD is a column with numbers) 2 A column that shows the row number =arrayformula(ROW(AJ2:AJ)) 3 A column that shows whether the column should be hidden based on the data in the sheet: =ARRAYFORMULA(IF(FLOOR(Z2:Z) FLOOR(TODAY());IF(AN2:AN="No";"Yes";"No");IF(E2:E=0;"Yes";"No")))

For a specific sheet I created a function:

 `
 ` function HideToevoegen(){
   var ss = SpreadsheetApp.getActive();
   var setupSheet = ss.getSheetByName("Toevoegen activiteiten");
   var lastrow= setupSheet.getRange("L4").getValue();
   var allData =  setupSheet.getRange("AI2:AJ"+lastrow).getValues().valueOf();
   hideRows(setupSheet,lastrow,allData)
   }



   function hideRows(setupSheet,lastrow,allData) {
     var ss = SpreadsheetApp.getActive();
     var beginRij = 0
     var nbrOfRows = 0
     var rowNum = 0
     var filteredData = allData.filter(function (dataRow) {
     return dataRow[0] === 'Ja'
     });
       for(var i in filteredData){
           if ( nbrOfRows == 0){   
           beginRij  = filteredData[i][1]
           nbrOfRows = nbrOfRows = +1
           var nextRow = beginRij + 1
           }
          else if ( filteredData[i][1] == nextRow ){ 
            nbrOfRows = nbrOfRows +1
            nextRow = nextRow + 1
           }
          else {    
             setupSheet.hideRows(beginRij,nbrOfRows);
             var beginRij = filteredData[i][1]
             var nbrOfRows = 1
             nextRow = beginRij+1
             }
           }
          // process last row
           if ( nbrOfRows > 0){
           setupSheet.hideRows(beginRij,nbrOfRows);
          }
        };

Answer 2

I created a script to hide all rows that have a date in the past. I set the date of the last execution of the script in a sheet called "General information" to avoid unnecessary executions and to avoid hiding rows that are already hidden. For performance reasons I first collect the first row of a group of rows to hide that are following each other and count the number of rows to hide before executing setupSheet.hideRows(beginRow, nbrOfRows);

Hope this helps

function hideRows() {
    var ss = SpreadsheetApp.getActive();
    var setupSheet = ss.getSheetByName("sheetName");
    var lastDateOpened = ss.getSheetByName("General     
    Info").getRange("B9").getValue().valueOf();
    // remove timestamps.
    lastDateOpened =  parseInt(lastDateOpened/100000000)
    var today = parseInt(new Date().valueOf()/100000000)
    // execute once a day in onOpen()
    if (today == lastDateOpened){
    return;
    }
    var lastrow = setupSheet.getRange("L4").getValue();
    var beginRow = 0
    var nbrOfRows = 0
    var rowNum = 0
    var dates =  
    setupSheet.getRange("A2:A"+lastrow).getValues().valueOf(); 

    for(var i = 0; i <lastrow-1; i++){
    rowNum = i+2
    var date =new Date(dates[i]).valueOf()
    date = parseInt(datum/100000000)  
    var nbrVolNeeded = ss.getSheetByName("sheetName")
    .getRange("E"+rowNum).getValue(); 

    if (date < today && date >= lastDateOpened){ 
    // date must be in the past and later than last execution date
    if (beginRow == 0){
    beginRow = rowNum 
    }
    nbrOfRows = nbrOfRows + 1
    }
    else {  
    if ( nbrOfRows > 0){
    setupSheet.hideRows(beginRij,nbrOfRows);
    var beginRow = 0
    var nbrOfRows = 0
            }
            }
        }
    ss.getSheetByName("General Info").getRange("B9").setValue(new 
    Date())
}

Answer 3

Hiding Rows Based upon Value in F16

Block1 = rows 16 to 27
Block2 = rows 28 to 39
Block3 = rows 40 to 51

if F16 = "A" - show block1, hide block2, hide block3
if F16 = "B" - hide block1, show block2, hide block3
if F16 = "C" - hide block1, hide block2, show block3

I used a switch statement to simplify the logic and make it easier to see what's going on. I used getDataRange() and getValues() to get the number of rows. Getting the extra data won't really affect the time of execution as that will be dominated by the hiding and showing of rows.

function HideSelectedBlocks() 
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh=ss.getSheetByName("Contract");
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var F16=sh.getRange("F16").getValue();
  for(var i=0;i<vA.length;i++)
  {
    var row=i+1;
    switch(F16)
    {
      case 'A':
        if(row>=16 && row<=27){sh.showRows(row);}
        if(row>=28 && row<=39){sh.hideRows(row);}
        if(row>=40 && row<=51){sh.hideRows(row);}
        break;
      case 'B':
        if(row>=16 && row<=27){sh.hideRows(row);}
        if(row>=28 && row<=39){sh.showRows(row);}
        if(row>=40 && row<=51){sh.hideRows(row);}
        break;
      case 'C':
        if(row>=16 && row<=27){sh.hideRows(row);}
        if(row>=28 && row<=39){sh.hideRows(row);}
        if(row>=40 && row<=51){sh.showRows(row);}
        break;
      default:
    }
  }
}

It took about 5 seconds to execute and less than a tenth of a second to get values on my spreadsheet.