Googlesheet dropdown list to show/hide rows

user_9155488 0
Viewed 58

I want to have various cells that have a dropdownlist to show/hide certain rows I have the following tableenter image description here

If I select A2 (dropdownlist with AMERICAS_a and AMERICAS_s), with the _s option, I want to hide only rows 3 to 6
If I select A7 (dropdownlist with EUROPE_a and EUROPE_s), with the _s option, I want to hide only rows 8 to 12
I am using an Appscript which works only for 1 of the continent. I want to make it work for any of the Watchcell

    function onEdit(a) {
      var watchSheet = "Sheet1";
      var watchCell = "A2";
      
      var sheet = a.range.getSheet();
      if (sheet.getName() !== watchSheet ||
          a.range.getA1Notation() !== watchCell) {
        return;
      }
      sheet.hideRows(2, 5);
      switch (a.value) {
        case "AMERICAS_a":
          sheet.showRows(2, 5);
          break;
        case "AMERICAS_s":
          sheet.showRows(2, 1);
          break;
        default:
      }  
    }

But I am unsure how to add A7 as another watchcell. I have repeated the function onEdit(e) but only the last onEdit function works.

google-apps-script google-sheets
1 Answers

I believe your goal is as follows.

  • Your Spreadsheet has the data validation rules for several cells in the column "A".
  • When the value of the dropdown list is changed, you want to run the script.
  • For example, when the suffix letters of the value of the dropdown list are _s, you want to hide rows until the next dropdown list.
  • For example, when the suffix letters of the value of the dropdown list are _a, you want to show rows until the next dropdown list.

Modification points:

  • In your showing script, in the case of both values of AMERICAS_a and AMERICAS_s, the rows are shown.
  • From your question, I thought that when the position of data validation rules is retrieved, the script might become simple.

When these points are reflected in a sample script, it becomes as follows.

Modified script:

function onEdit(e) {
  const sheetName = "Sheet1"; // Please set the sheet name.

  const { range, value } = e;
  const sheet = range.getSheet();
  const row = range.rowStart;
  const datavalidations = sheet.getRange("A1:A" + sheet.getLastRow()).getDataValidations();
  if (sheet.getSheetName() != sheetName || !datavalidations[row - 1][0]) return;
  datavalidations.splice(0, row);
  const n = datavalidations.findIndex(([a]) => a);
  sheet[value.slice(-2) == "_s" ? "hideRows" : "showRows"](row + 1, n > -1 ? n : datavalidations.length);
}

Testing:

When this script is used, the following result is obtained.

enter image description here

Note:

  • This sample script is for your sample showing Spreadsheet. So, when you change the structure of the Spreadsheet, this script might not be able to be used. Please be careful about this.

  • When this script is directly run with the script editor, an error like TypeError: Cannot destructure property 'range' of 'e' as it is undefined. occurs. Please be careful about this. Please change the dropdown list of the column "A".

References:

user_71086530
Related