I'm trying to write script that will achieve the following logic:

If cell J1 has a value of 1, show columns 1-10. If cell J1 has a value of 2, show columns 1-18. If cell J1 has a value of 3, show columns 1-26. If cell J1 has a value of 4, show columns 1-36.

Here' what I have so far:

function onOpen(e) {
  hideVersions_(e);
}
    function hideVersions_() {
  // get current sheet
  var sheet = e.source.getActiveSheet(); 
  // get row and column of edited cell
  var row = e.range.getRow();
  var col = e.range.getColumn();

  if (col == 10 && row == 1 && sheet.getSheetName() == 'Design') { 

    if (e.value === "2") {
      sheet.hideColumns(19, 17); // hide column 19-36 (S-AJ) 
      sheet.showColumns(1,17); // show column 1-18 (A-R)
    }
    if (e.value === "3") {
      sheet.hideColumns(27, 9); // hide column 27-36 (AB-AJ) 
      sheet.showColumns(1,25); // show column 1-26 (A-AA)
    }
      if (e.value === "4") 
      sheet.showColumns(1,35); // show column 1-36 (A-AJ)
    }
    else {
        sheet.hideColumns(11, 25); // hide column 11-36 (K-AJ) 
      sheet.showColumns(1,9); // show column 1-10 (A-J)
    }
  }

CodePudding user response：

This is what the onOpen() event object looks like:

{"authMode":"LIMITED","range":{"columnEnd":1,"columnStart":1,"rowEnd":1,"rowStart":1},"user":{"email":"redacted","nickname":"redacted"},"source":{}}

Please note: no e.value

CodePudding user response：

As I tried to get this to work I noticed you had an Underscore on your Function Name

function hideVersions_() {

That kept me from running and testing that Function.

You also Passed e from the onOpen Event into the hideVersion Function, but did not declare e as a parameter.

Instead of the onOpen Event do you want to use the onChange Event? Even though you might be making other changes we can modify the script to only look at cell J1 on a specific sheet. You'll notice in the code below I added an if statement to check if e is Null, this allowed me to test the file in the editor with out the triggering event.

I think your logic in your if statement can be simplified, not sure why you are checking if the sheet is a certain sheet of the spreadsheet. I also tend to grab the Value I want instead of letting the e event be the most recently edited cell. This is why I removed the row and col check from your first if statement.

In your if statements you "stringed" your values, but I am assuming you are putting numbers into the cell, and therefore your === is expecting numbers on both sides. Removing the quotes fixed that issue.

In your if statements you hide the new cells and then show cells, I think this can be simplified by Showing all cells and then hiding the new cells.

Below is the code that I got to work that completes what you asked for:

function onOpen(e) 
{
  hideVersions(e);
}

function hideVersions(e) 
{
  // get current sheet
  if ( e == null)  // When you run this Function in Editor
  {
    var sheet = SpreadsheetApp.getActiveSheet();
  }
  else
  {
    var sheet = e.source.getActiveSheet(); 
  }
  
  // get Control Cell J1
  var controlValue = sheet.getRange(1,10).getValue(); // J1 is Row 1 Col 10
  Logger.log(controlValue);
  var lastColumn = sheet.getLastColumn();
  if (sheet.getSheetName() == 'Design') 
  {   //since we grabed the cell explicitly, only Need to check SheetName

    if (controlValue === 2) 
    {
      Logger.log("Hiding s-AJ");
      sheet.showColumns(1, lastColumn); // show All
      sheet.hideColumns(19, 17); // hide column 19-36 (S-AJ) 
      
    }
    if (controlValue === 3) 
    {
      Logger.log("Hiding AB-AJ");
      sheet.showColumns(1, lastColumn) //Show All
      sheet.hideColumns(27, 9); // hide column 27-36 (AB-AJ) 
    }
    if (controlValue === 4) 
    {
      Logger.log("Showing all");
      sheet.showColumns(1,lastColumn); // show all
    }
    else 
    {
      Logger.log("Hiding K-AJ");
      sheet.showColumns(1, lastColumn); // Show All
      sheet.hideColumns(11, 25); // hide column 11-36 (K-AJ) 
    }
  }
}