How to write function onEdit which hide rows with empty cells from specific column when checkbox in this header column is unchecked? I tryed merge some codes from another questions, but I'm too weak to do this. Screen and example below

• if I would check only checkbox H1 it should show only rows with not empty cell in H column, so it would be: 4-5 & 17-25 rows
• if I would check only K1 checkbox it should show only rows 17-24, the rest should be hidden
• e.t.c.

Maybe simplier - this is product list and them color variation. Checkbox should show products "on stock" with specific checked color.

CodePudding user response：

Use Sheet.showRows() and Sheet.hideRows() in your onEdit(e) function, like this:

/**
* Simple trigger that runs each time the user edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. It runs automatically when you manually edit the spreadsheet.');
  }
  hideRowsOnCheckboxClick_(e);
}


/**
* Runs when a checkbox is clicked in the range H1:K1.
* Hides all rows below frozen rows where the checkbox column is blank.
*
* @param {Object} e The onEdit() event object.
*/
function hideRowsOnCheckboxClick_(e) {
  // version 1.0, written by --Hyde, 19 September 2022
  //  - see https://stackoverflow.com/a/73774777/13045193
  if (e.value !== 'TRUE' || e.range.rowStart !== 1 || e.range.columnStart < 8 || e.range.columnStart > 11) {
    return;
  }
  const triggerValue = '';
  const sheet = e.range.getSheet();
  const lastRow = sheet.getLastRow();
  const rowStart = sheet.getFrozenRows()   1;
  const numRows = lastRow - rowStart   1;
  sheet.showRows(1, lastRow);
  const triggerColumn = sheet.getRange(rowStart, e.range.columnStart, numRows, 1);
  const rowsToHide = triggerColumn
    .getValues()
    .flat()
    .map((value, index) => value === triggerValue ? rowStart   index : 0)
    .filter(Number);
  countConsecutives_(rowsToHide)
    .forEach(group => sheet.hideRows(group[0], group[1]));
  e.range.setValue(false);
}


/**
* Counts consecutive numbers in an array and returns a 2D array that
* lists the first number of each run and the count of numbers in each run.
* Duplicate values in numbers will give duplicates in result.
*
* The numbers array [1, 2, 3, 5, 8, 9, 11, 12, 13, 5, 4] will get
* the result [[1, 3], [5, 1], [8, 2], [11, 3], [5, 1], [4, 1]].
*
* Typical usage:
* const runLengths = countConsecutives_(numbers.sort((a, b) => a - b));
*
* @param {Number[]} numbers The numbers to group into runs.
* @return {Number[][]} The numbers grouped into runs.
*/
function countConsecutives_(numbers) {
  return numbers.reduce(function (acc, value, index) {
    if (!index || value !== 1   numbers[index - 1]) {
      acc.push([value]);
    }
    acc[acc.length - 1][1] = (acc[acc.length - 1][1] || 0)   1;
    return acc;
  }, []);
}

CodePudding user response：

I made an attempt at this, and when I read it I assumed multiple checkboxes should be able to be checked.

This is the code I came up with. Hopefully it's obvious what the three "let" variables are at the top and how to change them.

function onEdit(e){
  if (!e) {throw new Error('Please do not run the script in the script editor window. It runs automatically when you manually edit the spreadsheet.')}
  myFunction(e)
  }

function myFunction(e){
  let tabName          = 'Build Sample Here';
  let checkRangeString = 'H1:K1';
  let offsetToValues   = 2;

  var sheet = e.range.getSheet();
  if(sheet.getName()!= tabName){return}
  var checkRange = sheet.getRange(checkRangeString);
  if(e.range.rowStart != checkRange.getRow() || e.range.columnStart <checkRange.getColumn() || e.range.columnStart>checkRange.getLastColumn()){return}
  var checks = checkRange.getValues().flat();
  var lastRow = sheet.getLastRow();
  if(checks.reduce((a,b)=>!a && !b)){sheet.showRows(1,lastRow);return}
  
  var values = checkRange.offset(offsetToValues,0,sheet.getLastRow()-offsetToValues).getValues().map(e=>e.filter((f,i)=>checks[i]));
  if(!values.length){sheet.showRows(1,lastRow);return}
  values = values.map((e,i)=>[i,e.reduce((a,b)=>a b)]).filter(e=>!e[1]).map(e=>e[0] offsetToValues 1);

  var rowSets =[];
  for (let i=1,tempCount=1,startRow=offsetToValues 1;i<values.length;i  ){
    if(values[i]==values[i-1] 1 && values[i]!=lastRow){tempCount   ;continue}
    if(values[i]==lastRow){tempCount  }
    rowSets.push([startRow,tempCount]);
    startRow = values[i];
    tempCount =1;
  }
  
  sheet.showRows(1,lastRow);
  rowSets.forEach(e=>sheet.hideRows(e[0],e[1]));
}