Search code examples
google-apps-scriptgoogle-sheetstriggersgoogle-workspace

How do I script multiple "hideRows" commands?

My goal is to create a checkbox that hides several rows of information below it for every month. I have very very little knowledge of coding but have successfully been able to write a simple code for the first month.

For example, In row 19 of my spreadsheet it says "SEPTEMBER" and along side it in column "J", is a checkbox. When this checkbox is ticked, "TRUE", the 30 rows below it are hidden. The code I wrote is as follows:

function onEdit(e){
  if ((e.range.columnStart != 10),(e.range.rowStart != 19) || e.value != "TRUE") return;
  SpreadsheetApp.getActiveSheet().hideRows(21,30);
}

However my problem arises when I try to create the same effect but for the following month, in this instance the cell that the code identifies would be different (J52). I have been unable to get both checkboxes working. I will also need to repeat the process for multiple months after this.

In addition to this dilemma I would also like to know how to unhide the subsequent rows when the dedicated checkbox above them is un checked "FALSE". However, if this complicates things too much then it is not crucial.

If anyone could help it would be much appreciated,

Thank you

Solution

  • Recommendation:

    You can configure the onEdit function to capture the index of the row being edited then just hide 30 rows underneath it.

    EDITED: Added another condition to unhide rows.

    Try this code snippet:

    function onEdit(e) {
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var value = e.range.getValue();
  var sh = SpreadsheetApp.getActiveSheet();

  if (col == 10 && value == true) {
    sh.hideRows(row + 1, 30);
  } else if (col == 10 && value == false) {
    var range = sh.getRange(row + 1, 1, 30, 10);
    sh.unhideRow(range);
  } else return;
}

    References: