title: google apps script - hide / unhide rows based on cell value
body: <p>I've got a spreadsheet I use for work and been struggling with a hide/show rows depending on a specific cell value.</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Been playing with this:</p>
  
  <pre><code>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 &lt; 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);
      }
  }
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Hope I'm making sense
  Help? Ideas? </p>