How to make my 'Show and hide rows' script function properly in Google Sheets

1
votes

I am trying to have a script in (google sheets) sheet "Info Pull" show/hide rows based on the contents of Column C.

Currently I am running the following formula in Column C:

=if(or(len(H47)>0,len(I47)>0,len(J47)>0,len(K47)>0),"unhide","hide")

which produces either 'hide' or 'unhide' depending on whether or not cells H,I,J,and K 47 are returning a value.

If any of the aforementioned cells return a value, the corresponding cell in Column C turns to 'unhide' and vice-versa if all the cells return no value.

I've been trying to have this script hide/unhide the rows based on the contents of Column C however, the script is only partially functioning.

If I actively type 'hide' into a C cell, the row will hide. However, if I paste 10 rows worth of 'hide' into 10 C cells, only the first row will hide. Likewise, the script is not re-evaluating and hiding/unhiding based off of the changing results of the IF formula. If I depopulate cell H47, turning C47 to hide, the cell does not hide.

Also, if I set cell A1 to 'hide' and paste '=$A$1' in a group of cells in Column C, only the first cell in the group will hide and it will not unhide if I set A1 to 'unhide'. I think this may have something to do with my trigger but I am relatively new to scripting and am unsure.

function onEdit(e) {
  hideAndShowRows(e);
}

    function hideAndShowRows(e) {

  var sheetsToWatch = ['Info Pull'];
  var columnToWatch = 3;

  var sheet = e.range.getSheet();
  if (sheetsToWatch.indexOf(sheet.getName()) < 0)  {
    return;
  }
  var editedRow = e.range.getRow();
  var cellToWatch = sheet.getRange(editedRow, columnToWatch);
  if (cellToWatch.getValue().toLowerCase() === 'hide') {
    sheet.hideRows(editedRow);
  }
  if (cellToWatch.getValue().toLowerCase() === 'unhide') {
    sheet.showRows(editedRow);
  }
}

I would like the cells to hide/unhide based off of whether or not the formula in Column C returns hide/unhide.

2
javascriptgoogle-apps-scriptgoogle-sheetstriggers

2 Answers

0
votes

Here is a modified function that will solve few issues:

function onEdit(e) {
  hideAndShowRows(e);
}

    function hideAndShowRows(e) {

  var sheetsToWatch = ['Setup'];
  var columnToWatch = 3;

  var sheet = e.range.getSheet();
  if (sheetsToWatch.indexOf(sheet.getName()) < 0)  {
    return;
  }
  var editedRow = e.range.getRow();
  var numOfRows = e.range.getNumRows()
  for (var i = 0; i<numOfRows ; i++){
  var cellToWatch = sheet.getRange(editedRow + i, columnToWatch);
  if (cellToWatch.getValue().toLowerCase() === 'hide') {
    sheet.hideRows(editedRow + i);
  }
  if (cellToWatch.getValue().toLowerCase() === 'unhide') {
    sheet.showRows(editedRow + i);
  }
  }
}

Firstly, your code only works on the row that was edited, it doesn't look for any other row for changes. Specifically this line:

var editedRow = e.range.getRow()

e is a event object passed to simple triggers, which contain info like which range was edited was in this case. The above function getRow() gets the first row in the range which was edited, hence when you edit multiply rows only the first one gets hidden. To get all the rows that were edited you will have use getNumRows() and loop through each one of them like so:

var editedRow = e.range.getRow();
  var numOfRows = e.range.getNumRows()
  for (var i = 0; i<numOfRows ; i++){
  var cellToWatch = sheet.getRange(editedRow + i, columnToWatch);
  if (cellToWatch.getValue().toLowerCase() === 'hide') {
    sheet.hideRows(editedRow + i);
  }
  if (cellToWatch.getValue().toLowerCase() === 'unhide') {
    sheet.showRows(editedRow + i);
  }
  }

Finally, a caveat when you point all the cells to A1 and only edit row A (just cell A1), the program will only look for changes in that row i.e row A. You can find more resource here.

1
votes

Your code is "failing" because it only gets the row number of the top-left cell of the edited range

var editedRow = e.range.getRow();

and 

var cellToWatch = sheet.getRange(editedRow, columnToWatch);

You could use the following properties to get the range dimensions

e.range.rowStart
e.range.columnStart
e.range.rowEnd
e.range.columnEnd

or to get the reference of the edited range use

e.range.getA1Notation();

Related