CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Dmcn2020
Dmcn2020

Reputation: 13

Hide/unhide rows based on checkbox on Google sheet

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); 
}

Upvotes: 1

Views: 7516

Answers (2)

Larsky
Larsky

Reputation: 1

I know it is a bit of an old thread, but still very helpfull to al lot of people i guess. Both the code from the TS and the answer were very helpfull.

I used this for a similar project and found it a bit strange to have two checkboxes to show/hide rows though, so i made this work with one checkbox in cell "G1" to toggle the view hide/unhide.

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() === "G1" && editedValue === "FALSE") {
    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); 
  }
}

Upvotes: 0

Iamblichus
Iamblichus

Reputation: 19339

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); 
  }
}

Upvotes: 0

Related Questions