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);
};
2
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)
- Simple Triggers
- Event Objects
2