Hiding certain cells in Google Spreadsheet based upon values in other cells

How to hide/show rows in a Google Spreadsheet based upon values of other cells.

1)  First off watch the video on YouTube.  The video shows you how to Hide certain cells in Google Spreadsheet based upon values in other cells.

2)  The code required for this project uses "Named Ranges" in Google Spreadsheets.  (tip: If you are unsure on how to create a script see steps 2-4 on this blog post on Create a custom drop down menu based upon values in a Google Spreadsheet.

3)  Create a new script and paste in the following code. (tip: If you are unsure on how to create a script see steps 3-5 on This Blog Post on How to Send a Custom Email from a Google Script)

function HideColumns() {

  //open the current spreadsheet and get the value of the named range

   var ss = SpreadsheetApp.getActive();

   var name = ss.getRangeByName("VatReg");

   var namevalue = name.getValue();

   var scheme = ss.getRangeByName("VATscheme")

   var schemevalue = scheme.getValue();

  

  //check the named range and if the value is equal to no then hide the requested columns

  if (namevalue == "NO") {

    var sheet = ss.getSheetByName("Entries");

    sheet.hideColumns(9);

    sheet.hideColumns(11);

    //on sheet Setup hide the 3 rows as requested    

    var named = ss.getSheetByName("Setup");

    var range = sheet.getRange('A7');

    Logger.log(range);

    named.hideRows(range.getRowIndex());

    range = sheet.getRange("A8");

    named.hideRows(range.getRowIndex());

    range = sheet.getRange("A9");

    named.hideRows(range.getRowIndex());

  }

  

  //OTHERWISE make sure the colums are showing

  else if (namevalue != "NO") {

    var sheet = ss.getSheetByName("Entries");

    sheet.showColumns(9);

    sheet.showColumns(11);

    

    //on sheet Setup show the 3 rows as requested    

    var named = ss.getSheetByName("Setup");

    var range = sheet.getRange("A7");

    named.showRows(range.getRowIndex());

    //named.unhideRow(range); //.getRowIndex());

    range = sheet.getRange("a8");

    //named.unhideRow(range.getRowIndex());

    named.showRows(range.getRowIndex());

    range = sheet.getRange("A9");

    named.showRows(range.getRowIndex());

//    named.unhideRow(range.getRowIndex());

  }

  

  //hide column J if VatReg is NO or VATscheme FLat Rate

  if ((namevalue == "NO") || (schemevalue == "Flat rate")) {

    sheet.hideColumns(10);

  }

  //otherwise show the column

  else {

    sheet.showColumns(10);

  }

  

}

4) Create the following trigger