Hide/unhide rows based on checkbox on Google sheet

Question

I have a Google spreadsheet which has checkboxes in column P, as and when a checkbox is ticked the corresponding row automatically hides.

I am looking to add an additional check box in G1 that will show all hidden rows if checked and if possible a checkbox in E1 to then hide again all the rows that have the checkbox ticked in column P.

Basically I want to automatically hide rows when I tick the checkbox in column P Then later I may need to review the hidden rows by clicking the checkbox in G1. When I am finished reviewing the hidden rows above click a checkbox in E1 and all rows which are checked in column P will hide again.

function onEdit(e){ 
    if (e.range.columnStart != 16 || e.value != "TRUE") return;
    SpreadsheetApp.getActiveSheet().hideRows(e.range.rowStart); 
}

Please add what you've already tried. You can start by including your code so we can help you better on debugging it. — jpneey
Please write the question accordingly to stackoverflow.com/help/how-to-ask . Firstly it will be great a better description of the code and a demo/example of the code. — Skatt
Hi jpneey, I am a beginner to scripts and the below is what I have and works when column P is ticked. I am not sure how to proceed with the script for showing all hidden rows and rehiding the checked rows. function onEdit(e){ if (e.range.columnStart != 16 || e.value != "TRUE") return; SpreadsheetApp.getActiveSheet().hideRows(e.range.rowStart); } — Dmcn2020

Iamblichus Iamblichus · Accepted Answer · 2021-05-27T09:26:24

Solution:

Check if edited cell is G1 via getA1Notation(). In that case, check if value is TRUE, and if that's the case, show all rows. An easy option to show all rows is using unhideRow(row), setting A:A as your range (since this includes all rows in the sheet).
Check if edited cell is E1. If the value is TRUE, retrieve all values from P column via getValues(), iterate through them, and hide the corresponding rows if the value is true, using hideRows(rowIndex).
Check if the edited cell is in column P, and use hideRows on the edited row if that's the case, as you're already doing.

Code snippet:

function onEdit(e){
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = e.range;
  const editedValue = e.value;
  if (range.getA1Notation() === "G1" && editedValue === "TRUE") {
    sheet.unhideRow(sheet.getRange("A:A"));
  } else if (range.getA1Notation() === "E1" && editedValue === "TRUE") {
    const firstRow = 2;
    const lastRow = sheet.getLastRow();
    const pValues = sheet.getRange("P" + firstRow + ":P" + lastRow).getValues().flat();
    pValues.forEach((pValue,i) => {
      if (pValue === true) {
        sheet.hideRows(i + firstRow);
      }
    });
  } else if (e.range.columnStart === 16 && editedValue === "TRUE") {
    sheet.hideRows(range.rowStart); 
  }
}

Hide/unhide rows based on checkbox on Google sheet

1 Answers

Solution:

Code snippet: