如何编写onEdit函数，实现基于表头复选框和特定列单元格值隐藏行

Got it, let's build this onEdit function exactly for your product and color variant sheet. This script will check the checkboxes in H1 and K1, then show/hide rows based on which color has inventory (non-empty cells in the column) and your specified row ranges.

Full Working Code #

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  // Make sure this only runs on your target sheet (replace "Inventory" with your sheet name)
  if (sheet.getName() !== "Inventory") return;

  // Get the checkbox states from H1 and K1
  const hCheckbox = sheet.getRange("H1").getValue();
  const kCheckbox = sheet.getRange("K1").getValue();

  // Define the row ranges we care about
  const hRows = [...Array(2).keys()].map(i => i + 4) // Rows 4-5
              .concat([...Array(9).keys()].map(i => i + 17)); // Rows 17-25
  const kRows = [...Array(8).keys()].map(i => i + 17); // Rows 17-24
  const allRelevantRows = [...new Set([...hRows, ...kRows])]; // Combine and deduplicate rows

  // First, unhide all rows to reset state (prevents stuck hidden rows)
  allRelevantRows.forEach(row => sheet.showRows(row));

  // Case 1: Only H1 checkbox is checked
  if (hCheckbox && !kCheckbox) {
    hRows.forEach(row => {
      const cellValue = sheet.getRange(`H${row}`).getValue();
      if (!cellValue) { // Hide rows where H column is empty
        sheet.hideRows(row);
      }
    });
    // Hide all rows NOT in hRows (adjust the rowNum >=4 if needed)
    sheet.getDataRange().getValues().forEach((_, index) => {
      const rowNum = index + 1;
      if (!hRows.includes(rowNum) && rowNum >=4) {
        sheet.hideRows(rowNum);
      }
    });
  }

  // Case 2: Only K1 checkbox is checked
  else if (!hCheckbox && kCheckbox) {
    kRows.forEach(row => {
      const cellValue = sheet.getRange(`K${row}`).getValue();
      if (!cellValue) { // Hide rows where K column is empty
        sheet.hideRows(row);
      }
    });
    // Hide all rows NOT in kRows (adjust the rowNum >=4 if needed)
    sheet.getDataRange().getValues().forEach((_, index) => {
      const rowNum = index + 1;
      if (!kRows.includes(rowNum) && rowNum >=4) {
        sheet.hideRows(rowNum);
      }
    });
  }

  // Case 3: Both checkboxes are checked
  else if (hCheckbox && kCheckbox) {
    allRelevantRows.forEach(row => {
      const hValue = sheet.getRange(`H${row}`).getValue();
      const kValue = sheet.getRange(`K${row}`).getValue();
      // Hide row only if both H and K are empty
      if (!hValue && !kValue) {
        sheet.hideRows(row);
      }
    });
    // Hide rows outside our relevant ranges
    sheet.getDataRange().getValues().forEach((_, index) => {
      const rowNum = index + 1;
      if (!allRelevantRows.includes(rowNum) && rowNum >=4) {
        sheet.hideRows(rowNum);
      }
    });
  }

  // Case 4: Neither checkbox is checked (show all rows)
  else {
    sheet.getDataRange().getRows().forEach((_, index) => {
      sheet.showRows(index + 1);
    });
  }
}

Key Details & Customization Tips #

• Sheet Name: Replace "Inventory" with your actual sheet name so the script only runs on the right tab.
• Row Ranges: If your product rows change later, adjust the hRows and kRows arrays directly. For example, if H column needs to include rows 6-7 too, update the first part of hRows to [...Array(4).keys()].map(i => i +4) to cover 4-7.
• Empty Cell Check: If your "empty" cells have hidden whitespace, change !cellValue to cellValue.toString().trim() === "" to avoid false negatives.
• Row Visibility Scope: The rowNum >=4 check keeps rows 1-3 visible (like your headers). Remove this if you want to hide all rows outside the target ranges.

How to Set This Up #

1. Open your Google Sheet.
2. Go to Extensions > Apps Script to open the script editor.
3. Delete any existing code and paste the code above.
4. Save the project with a name like "InventoryFilterScript".
5. Add checkboxes to H1 and K1 via Data > Data validation > Criteria > Checkbox.
6. Test by checking/unchecking the boxes — the rows should show/hide as expected!

Note: The first time you trigger the script (via editing a checkbox), you'll need to grant permission for it to access your sheet. Follow the prompts, and click "Advanced" > "Go to [Project Name]" to proceed past the security warning.

内容的提问来源于stack exchange，提问作者Stickman