Search code examples
google-apps-scriptshow-hidenamed-ranges

How to hide/unhide a named range of columns with Google script

Anyone who can help me with a script that allows me to hide or show columns that have already been named manually. The code below only hides/displays specific columns and my problem is that the user can insert/delete columns within a named range and that causes the range of named columns to change dynamically. I figure that when manipulating a range by name, the code will dynamically include the columns in the named range.

    function showColBasicInfo() {
      sheet.showColumns(4,5);
      sheet.getRange(1, 4).activate();
    }
    function hideColBasicInfo() {
      sheet.hideColumns(4,5);
      sheet.getRange(1, 4).activate();
    }

I did this more: This code loads all the named ranges in the document into an array and I can search for a particular one by loading it into a variable, but that's as far as I got.

    function returnRangeName1() {
      var rangeName = "H2aCI"; //Nombre de rango a buscar
      var RangosCNombre = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges();
      for(var i = 0; i < RangosCNombre.length; i++) {
        if(RangosCNombre[i].getName() == rangeName) {
          Logger.log(RangosCNombre[i].getName() + " encontrado");
        }
        //Logger.log(RangosCNombre[i].getName());
      }
    }

I need to know what code I should write in GAS to hide the range found. I will appreciate any help. Thank you.

Solution

  • I believe your goal as follows.

    • You want to hide the columns of the named range.
    • In your script, you want to hide the columns in the named range of H2aCI.

    Modification points:

    • In this case, you can directly retrieved the named range from the name of H2aCI.
    • And, you can retrieve the information of column from the named range. Using this information, you can hide the columns of the named range.

    When above points are reflected to your script, it becomes as follows.

    Modified script:

    function returnRangeName1() {
  var rangeName = "H2aCI";

  var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(rangeName);
  if (range) range.getSheet().hideColumns(range.getColumn(), range.getNumColumns());
}

    References: