Script Google Sheets supprimer la ligne si certaines cellules sont vide
Je report au cas ou le script qui fonctionne
// mike steelson
function onOpen() {
SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
.addItem('👉 nettoyer lignes vides', 'removeEmpty')
.addToUi();
removeEmpty()
}
function onEdit(e) {
var sh = e.source.getActiveSheet();
if (sh.getName() != 'Suivi Clients') return;
var editRange = {
top: 6,
left: 3,
right: 7
};
var thisRow = e.range.getRow();
if (thisRow < editRange.top || thisRow > editRange.bottom) return;
var thisCol = e.range.getColumn();
if (thisCol < editRange.left || thisCol > editRange.right) return;
removeEmpty()
}
function removeEmpty() {
const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Suivi Clients');
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 + 6);
empty_rows.reverse().forEach(x => sh.deleteRow(x));
sh.insertRowsAfter(lastRow - empty_rows.length, 5)
var rng = sh.getRange('A7:Z7')
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);
}