Show and hide rows and columns based on different dropdowns

Question

I'm trying to create an "intake form" as part of a google sheet that will show or hide rows and columns based on selection in different dropdowns:

1. Hide/show column on other sheet depending on selection in dropdown.

   • If dropdown selection in "NamedRange1" is "A", show column 3 on other sheet;
   • In all other cases (i.e. when blank or "B"), hide column 3 on other sheet;

2. Hide/show rows on same sheet depending on selection in dropdown.

   • If dropdown in "NamedRange2" is "Yes", show rows 15-19
   • In all other cases (i.e. when blank or "No), hide rows 15-19

3. Same as above but different dropdown and rows.

   • If dropdown in in "NamedRange3" "is "Yes", show rows 26-40
   • In all other cases (i.e. when blank or "No), hide rows 26-40

So far I can get nr. 1 to work, but not 2 or 3... Additionally, I can't get them to run automatically on edit of the form...

I'm pretty new to all of this, so any help would be much appreciated :-)

This is my code so far:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Start Here >>")
}  

function HideColumns() {
  var ss = SpreadsheetApp.getActive();
  var name = ss.getRangeByName("NamedRange1");
  var namevalue = name.getValue();

  if (namevalue == "A") {
    var sheet = ss.getSheetByName("Sheet 2");
    sheet.showColumns(3);
  }
  else if (namevalue != "A") {
    var sheet = ss.getSheetByName("Sheet 2");
    sheet.hideColumns(3);   
  }
}

function HideRows() {
  var ss = SpreadsheetApp.getActive();
  var name = ss.getRangeByName("NamedRange2");
  var namevalue = name.getValue();  

  if (namevalue == "Yes") {
    var sheet = ss.getSheetByName("Sheet1");
    sheet.showRows(15, 10);            
  }
  else if (namevalue != "Yes") {
    var sheet = ss.getSheetByName("Sheet1");
    sheet.hideRows(15, 10);     
  }
}

function HideRows() {
  var ss = SpreadsheetApp.getActive();
  var name = ss.getRangeByName("NamedRange3");
  var namevalue = name.getValue();  

  if (namevalue == "Yes") {
    var sheet = ss.getSheetByName("Sheet1");
    sheet.showRows(26, 15);             
  }
  else if (namevalue != "Yes") {
    var sheet = ss.getSheetByName("Sheet1");
    sheet.hideRows(26, 15);
  } 
} 

Answer 1

Script Flow:

• Create a class to create a configuration object for each set of interactions
• Call the run method of the class to execute the desired action

Sample Script:

Assuming the named Ranges1,2,3 are at A1, B1 and C1 of "Start Here >>" sheet,

/**
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e
 */
const onEdit = e => {
  const range = e.range;
  if (range.getSheet().getName() !== 'Start Here >>') return;//exit if edit is not in Start Here >> sheet

  class config {
    /**
     * @param {string} sheetToDo Sheet to act upon
     * @param {string} func function to run on sheet
     * @param {number} index Index of column/row to start desired action
     * @param {number} num Number of Rows/Columns to hide/show
     * @param {string} strToCheck The string to check against edited value
     * @param {any} val  edited value
     */
    constructor(sheetToDo, func, index, num, strToCheck, val) {
      this.sheetToDo = sheetToDo;
      this.func = func;
      this.index = index;
      this.num = num;
      this.switchFunc_(strToCheck === val);
    }
    run() {
      SpreadsheetApp.getActive()
        .getSheetByName(this.sheetToDo)
        [this.func](this.index, this.num);
    }
    switchFunc_(toggle) {//To switch hide to show and viceversa
      if (!toggle) {
        const change = ['hide', 'show'];
        this.func =
          change[Number(!change.indexOf(this.func.slice(0, 4)))] +
          this.func.slice(4);
      }
    }
  }
  const map = {
    A1: val => new config('Sheet2', 'hideColumns', 3, 1, 'A', val),//if edit is in A1, 
    B1: val => new config('Sheet1', 'showRows', 15, 4, 'Yes', val),
    C1: val => new config('Sheet1', 'showRows', 26, 15, 'Yes', val),
  };
  const createConfig = map[range.getA1Notation()];
  if (createConfig) createConfig(e.value).run();
};

Performance:

• ~1-2s if edit results in hiding/showing rows

References:

• Class
• Event objects

Answer 2

You could write it like this:

The big time consumer is still hiding and showing rows and columns

function onEdit(e) {
  const n1=e.source.getRangeByName("NameRange1").getValue();
  const n2=e.source.getRangeByName("NamedRange2").getValue(); 
  const n3=e.source.getRangeByName("NamedRange3").getValue();
  const sh1=e.source.getSheetByName("Sheet1");
  const sh2=e.source.getSheetByName("Sheet2");
  if (n1=="A"){sh2.showColumns(3);}else{sh2.hideColumns(3);}
  if (n2=="Yes"){sh1.showRows(15,10);}else{sh1.hideRows(15,10);}
  if (n3=="Yes"){sh1.showRows(26,15);}else{sh1.hideRows(26,15);} 
}