[SOLVED] Show next row as soon as a cell is filled, using a macro

Show next row as soon as a cell is filled, using a macro

In a spreadsheet I want to distribute to my colleagues, I want to automatically unhide the next row (for the following record), as soon as a row (or a cell) is entered data into. Alternatively unhide the next row (e.g. row 52) when a checkbox is clicked (in row 51). On my sheet, there are multiple blocks with hidden cells, so I want the code to work everywhere for the 'next' row.

I recorded a macro but it only works with row 51. I want it to work wherever the macro is activated. For instance when a checkbox is marked on row 40, unhide row 41.

function UnhideNext() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow() + 2, 1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveSheet().showRows(51, 2);
};

Solution

One option is to use an onEdit() simple trigger.

Example 1 below unhides the next row if a checkbox in column A is set to true.
Example 2 unhides the next row if any in-cell edit is made.

Example 1

const checkboxCol = 1;  // Column with checkbox

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const rng = e.range;
  if ((typeof e.value !== 'undefined') &&   //  IF:   The edit was not pasted, &
      (rng.isChecked()) &&                  //        it's a checked "checkbox", &
      (rng.getColumn() == checkboxCol)) {   //        it's in the correct column,
    sheet.unhideRow(rng.offset(1,0))        //  THEN: Unhide the row below
  };
}

Example 2

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (typeof e.value !== 'undefined') {    //   if the edit was not pasted
    sheet.unhideRow(e.range.offset(1,0))   //   then unhide the row below
  };
}

Note: e.value is undefined for a pasted range (i.e. not an in-cell edit or in-cell paste)