Script Google Sheets supprimer la ligne si certaines cellules sont vide

Le 20/04/2022 à 21:23
J'ai un tableau en B6:Y dans lequel j'entre les informations de mes nouveaux clients.
Cependant, il arrive que de temps en temps certains clients se rétractent.
Dans ce cas, j'efface simplement les valeurs des cellules C-D-E-F et G. Du coup, la ligne en question est laissée vide. Je souhaite que cette ligne soit effacée et que les lignes en dessous contenant des valeurs remontent et qu'en bas de page, il me reste quand même des ligne afin de pouvoir continuer a incérer des clients et ne plus avoir un tableau de 1000 lignes.
Je voudrais qu'il n'y ait que 5 lignes vides après la dernière ligne du bas contenant des valeurs.
Attention car dans les autres colonnes de la ligne, il y a des formules. Donc je souhaiterais que les lignes ajoutées par le script contiennent ces formules.
Donc pour faire simple, Je voudrais un script qui supprimerait toutes les lignes si C-D-E-F-G est vide et ajouterait de nouvelles lignes avec des formules en bas après la dernière ligne avec des valeurs mais pas plus de 5 lignes en bas. Voici mon Sheet:

https://docs.google.com/spreadsheets/d/1CPcMx3Dhbqi-zO4D3jYNxO-PGjyW3iTfRo5gRmEB9p4/edit#gid=0

Merci.
Le 21/04/2022 à 03:21

Bonjour,

proposition

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

avec, et c'est le plus important, une transformation des formules en appliquant arrayformula dans l'en-tête des colonnes

Le 21/04/2022 à 03:51

si tu ne souhaites pas passer en arrayformulas comme tu l'as fait en B ...

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 + 6);
  empty_rows.reverse().forEach(x => sh.deleteRow(x));

// ajouts
  sh.insertRowsAfter(lastRow - empty_rows.length, 5)

// formats
  var rng = sh.getRange('A' + (lastRow - empty_rows.length) + ':Z' + (lastRow - empty_rows.length))
  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);

}

c'est nettement moins élégant !

Le 21/04/2022 à 13:26

Bonjour Mike,

Merci pour tes réponses qui fonctionnent parfaitement.

J'utilise ARRAYFORMULA uniquement dans B, et tu remarqueras que la fonction démarre en B5 et pas en B6, car sinon quand je filtre mon tableau, j'ai des erreurs...

Donc la 2eme proposition est moins esthétique, certes mais plus fonctionnelle pour moi.

Penses tu que l'on puisse rajouter au script les fonctions onOpen(e) et ausii onEdit(e) ou onSelectionChange(e)?

Le 21/04/2022 à 13:55

Je suis étonné quand même que les arrayformulas puissent bloquer le filtrage !! à condition bien sûr de toutes les mettre en ligne 5 ...

onOpen, oui, tu peux ajouter la fonction comme suit

function onOpen(){
  removeEmpty()
}

tu avais un menu parsonnalisé, dans ce cas il faut le mettre dans la même fonction

onSelectionChange(e) : il a parfois un fonctionnement bizarre, pourquoi pas, mais il faut définir l'origine de l'action

onEdit(e) : idem il faut définir l'événement déclencheur, cela peut être la suppression d'une donnée entre Cn et Gn (ligne n) en testant que toutes les données entre C et G ont été effacée, par exemple

corrigé plus bas

mais a contrario cela peut ralentir l'introduction de nouvelles données

Conclusion, je laisserais juste le onOpen

nota : en mp, quelques commentaires ...

Le 21/04/2022 à 14:00

Oui elle démarraient en ligne 6 car ligne 5 j'ai le titre de mes colonnes.. Donc en filtrant, la formule se retrouvait a je ne sais quelle ligne.

Je voudrais simplement que le script s'execute automatiquement si j'efface ou j'ajoute une valeur dans la colonne "C". Et aussi, si possible, ajouter une fonction dans ma barre de menu avec onOpen(e) dans le cas ou le script ne s'execute pas et que je dois l'executer manuellement.

Le 21/04/2022 à 14:08
Mozart75 a écrit :

Oui elle démarraient en ligne 6 car ligne 5 j'ai le titre de mes colonnes.. Donc en filtrant, la formule se retrouvait a je ne sais quelle ligne.

si tu configure ta formule en ligne 5 comme suit cela devrait fonctionner

={"titre de la colonne";arrayformula(__________)}
Mozart75 a écrit :

Je voudrais simplement que le script s'execute automatiquement si j'efface ou j'ajoute une valeur dans la colonne "C". Et aussi, si possible, ajouter une fonction dans ma barre de menu avec onOpen(e) dans le cas ou le script ne s'execute pas et que je dois l'executer manuellement.

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('👉 supprimer lignes vierges & ajouter 5 lignes', 'removeEmpty')
    .addToUi();
  removeEmpty()
}
Le 21/04/2022 à 14:23

Super! Tout marche bien sur le fichier que je t'ai partagé. Par contre sur mon fichier initial ou j'ai 4 onglet j'ai une erreur avec ce script:

// mike steelson
function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('👉 supprimer lignes vierges & ajouter 5 lignes', 'removeEmpty')
    .addToUi();
  removeEmpty()
}

function onEdit(e) {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('DPT 56 EST + 35');
  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('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);

}

15:20:03. Avis. Exécution démarrée

15:20:03. Erreur. TypeError: Cannot read property 'range' of undefined

onEdit. @ Sans titre.gs:17

Le 21/04/2022 à 14:27

Je n'utilise pas encore les fonctions ARRAYFORMULA..

Le 21/04/2022 à 14:36

j'ai été un peu vite en copiant un script de ma bibliothèque !!

corrigé plus bas
Le 21/04/2022 à 14:44

Du coup, une autre erreur..

C'est bizarre car sur le fichier test ca marche tres bien

15:43:17. Avis. Exécution démarrée

15:43:18. Erreur. ReferenceError: event is not defined

onEdit. @ Sans titre.gs:10

Le 21/04/2022 à 14:46

10. var sh = event.source.getActiveSheet();

Le 21/04/2022 à 14:47

décidemment ... manque ()

corrigé

je re-teste !

edit : a priori c'est ok !

Le 21/04/2022 à 14:55

Toujours pas..

Tu veux l'acces a mon fichier initial?

Le 21/04/2022 à 14:59

zut zut zut !! je merdoie complètement ...

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()
}
Le 21/04/2022 à 15:04

Toujours meme erreur..

15:43:17. Avis. Exécution démarrée

15:43:18. Erreur. Cannot read property 'source' of undefined

onEdit. @ Sans titre.gs:10

==> 10. var sh = event.source.getActiveSheet();

Le 21/04/2022 à 15:17

regared si tu n'as pas 2 fois le même script, car j'avais corrigé

var sh = e.source.getActiveSheet();
Le 21/04/2022 à 15:19

J'ai ça:

// 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('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);

}
Le 21/04/2022 à 15:25

donc tu as la bonne ligne

var sh = e.source.getActiveSheet();

et non celle qui semble planter

==> 10. var sh = event.source.getActiveSheet();

n'y a-t-il pas un autre onEdit(e) qui traine ?

Le 21/04/2022 à 15:37

Ok tout marche parfaitement.

Je ne sais pas pourquoi et ce qu'il n'allait pas

Merci encore Mike pour ton professionnalisme.

Rechercher des sujets similaires à "script google sheets supprimer ligne certaines vide"