Google工作表脚本删除行，如果某些单元格是空的，并在底部添加行

Question

我有一个表，其内容范围为B6:Y1000。我想一个脚本，将删除所有行，如果列C-D-E-F-G是空的。我还需要这个脚本添加5个新行在最后一行在列C-D-E-F-G的内容后的底部。所有添加的行都必须包含公式。添加的行使我能够继续填充表，因为第一个函数删除了所有的行。这是我的表单:

我的表

谢谢你的见解!

Answer 1

Try

function removeEmpty() {
const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Suivi Clients');
const empty_rows = [];
const lastRow = sh.getLastRow()
Browser.msgBox(lastRow)
const data = sh.getRange("C6:G" + lastRow).getValues();
for (var i in data) if (data[i].join('') == '') empty_rows.push(+i + 6);
empty_rows.reverse().forEach(x => sh.deleteRow(x));
sh.insertRowsAfter(lastRow - empty_rows.length, 5)
var rng = sh.getRange('A6:Z6')
rng.copyTo(sh.getRange('A' + (lastRow - empty_rows.length + 1) + ':Z' + (lastRow - empty_rows.length + 5)), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
// H-I-J-K-L
var rng = sh.getRange('H' + (lastRow - empty_rows.length) + ':L' + (lastRow - empty_rows.length))
rng.copyTo(sh.getRange('H' + (lastRow - empty_rows.length + 1) + ':L' + (lastRow - empty_rows.length + 5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
// O
var rng = sh.getRange('O' + (lastRow - empty_rows.length) + ':O' + (lastRow - empty_rows.length))
rng.copyTo(sh.getRange('O' + (lastRow - empty_rows.length + 1) + ':O' + (lastRow - empty_rows.length + 5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
// Q-R-S-T-U
var rng = sh.getRange('Q' + (lastRow - empty_rows.length) + ':U' + (lastRow - empty_rows.length))
rng.copyTo(sh.getRange('Q' + (lastRow - empty_rows.length + 1) + ':U' + (lastRow - empty_rows.length + 5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
// X-Y 
var rng = sh.getRange('X' + (lastRow - empty_rows.length) + ':Y' + (lastRow - empty_rows.length))
rng.copyTo(sh.getRange('X' + (lastRow - empty_rows.length + 1) + ':Y' + (lastRow - empty_rows.length + 5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
}

Answer 2

删除行

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