I have a sheet with a table in the range B6:Y1000. I would like a script that would delete all rows if C-D-E-F-G are empty and add new rows with formulas at the bottom after the last row with values ​​but no more than 5 rows at the bottom. This is my sheet:

My Sheet

Thanks for your insight!

CodePudding user response：

Delete Rows

function drows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet Name");
  let d = 0;
  sh.getRange("B6:Y1000").getValues().forEach((r, i) => {
    if (!r[2] && !r[3] && !r[4] && !r[5] && !r[6]) {
      sh.deleteRow(i   6 - d  );
    }
  });
}

CodePudding user response：

Try

function drows() {
  const sh = SpreadsheetApp.getActiveSheet();
  const empty_rows = [];
  const lastRow = sh.getLastRow()
  const data = sh.getRange("C6:G"   lastRow).getValues();
  for (var i in data) if (data[i].join('') == '') empty_rows.push(i * 1   6);
  empty_rows.reverse().forEach(x => sh.deleteRow(x));
  sh.insertRowsAfter(lastRow-empty_rows.length,5)
}

and transform your formulas in arrayformula in the header of each column