Google Sheets et Google Apps Script : contrôler les modifications apportées sur une feuille de calcul

Vous avez un fichier Google Sheets qui est éditable mais vous voulez vous assurez que les modifications apportées sont volontaires et, si besoin, les annuler ? Voici deux options qui s’offrent à vous, avec pour chacune leurs avantages et inconvénients.

Solution 1 : utilisation de la protection native Google Sheets

Google Sheets propose sa solution de protection, visible en faisant clic droit sur le classeur et en sélectionnant l’option « Protéger la feuille ».

Vous pouvez alors choisir de protéger soit la feuille entière en excluant si besoin certaines cellules, soit une plage de données dans la feuille.

Ensuite vous avez deux options d’autorisation :

• afficher un avertissement si la plage en question est modifiée
• limiter l’édition à certains compte Google uniquement

L’option de bloquer simplement l’édition est déjà très bien expliquée dans cet article. Nous sommes ici plutôt dans l’option où nous voulons quand même laisser aux utilisateurs les droits d’édition tout en s’assurant que les modifications sont bien intentionnelles.

Une fois la protection « Modification autorisée (avec avertissement préalable) » en place, toute modification dans la plage sélectionnée entraîne l’affichage d’une pop-up demandant à l’utilisateur si il veut ou non poursuivre la modification.

Si l’utilisateur clique sur « OK », la modification est bien effectuée. Et si il clique sur « Annuler », la modification n’est pas prise en compte et rien n’est changé dans le classeur.

Il y a également l’option « Ne plus afficher l’avertissement pendant cinq minutes » qui est proposée, ce qui est une bonne chose pour l’utilisateur si il ne veut plus être « dérangé » par cette alerte à chaque modification, mais une mauvaise chose car l’utilisateur n’a alors plus conscience de la conséquence de ses changements.

Le fait que cette alerte d’avertissement ne soit pas configurable (pas de possibilité d’enlever l’option « Ne plus afficher cet avertissement pendant cinq minutes » ou de changer le texte pour qu’il soit plus parlant dans certains cas) amène à considérer une autre solution qui serait, elle, configurable mais comme nous le verrons, est limitée à des changements simples.

Solution 2 : création d’un script dédié pour avertir l’utilisateur lors d’une modification de cellule

La fonction onEdit() dans Google Apps Script se déclenche dès qu’une cellule est éditée et nous renseigne sur la plage modifiée et, dans le cas où la plage se limite à une cellule unique, nous renvoie l’ancienne valeur et la nouvelle de la cellule (remarque: les ajouts/suppressions de colonnes/lignes ne déclenchent pas cet événement).

L’appel à cette fonction est fait après la modification du fichier, il n’est donc pas possible de simplement empêcher la modification (comme avec la solution 1) mais il est possible de mettre en place un journal des modifications (comme expliqué dans cet article) ou alors afficher une pop-up dans certains cas (feuille(s) et/ou plage(s) de données particulières) avec un message précis pour prévenir l’utilisateur et annuler son action (dans le cas simple d’un changement de valeur d’une cellule).

Exemple concret : vérifier qu’une case est cochée avant d’en cocher une autre dans Google Sheets

Vous renseignez dans une feuille la liste des prestations effectuées par la société, avec une colonne où vous indiquez si elle a été facturée et une autre si elle a été réglée.

Vous voulez que ces colonnes « Facturé » et « Payé » soient éditables par tout le monde mais vous aimeriez mettre en place plusieurs pare-feux :

1. la colonne « Facturée » doit être cochée avant de pouvoir cocher la colonne « Payée »
2. la colonne « Payée » ne devrait normalement pas être cochée avant la colonne « Facturée » mais il peut y avoir des exceptions
3. la colonne « Facturée » une fois cochée ne devrait plus être décochée mais il peut y avoir des exceptions

Avec la Solution 1 (protéger les plages de Google Sheets), vous pourrez uniquement afficher un avertissement dès d’une modification sera apportée aux colonnes « Facturé » et « Payé » même quand ces modifications seront « normales »; à l’inverse, vous ne pourrez pas prévenir l’utilisateur sur le problème exact en cas de modification non désirée (comme les trois cas détaillés ci-dessus).

Avec la Solution 2 (Script), vous pouvez créer un script dédié qui va dans le cas 1 annuler l’action de l’utilisateur et dans les cas 2 et 3 vérifier avec l’utilisateur si son action était volontaire ou pas avant de l’annuler.

Et voici le code correspondant (à coller dans Outils > Éditeur de script):

const colIndexFacture = 5;
const colIndexPaiement = 6;
function onEdit(e)
{
  const celluleEditee = e.range;
  const numeroLigneEditee = celluleEditee.getRow();
  const numeroColonneEditee = celluleEditee.getColumn();
  const feuilleEditee = celluleEditee.getSheet();
  const nomFeuilleEditee = feuilleEditee.getName();
  const ancienneValeur = (e.oldValue==null)?"":e.oldValue;
  const nouvelleValeur = e.value;
  if (nomFeuilleEditee == "Liste prestations") {
    // Cas 1
    if (numeroColonneEditee == colIndexPaiement && nouvelleValeur == "TRUE") {
      const prestationDejaFacturee = feuilleEditee.getRange(numeroLigneEditee,colIndexFacture).getValue();
      if (!prestationDejaFacturee) {
        const ui = SpreadsheetApp.getUi();
        var result = ui.alert(
          'Erreur !',
          'Vous avez ligne '+numeroLigneEditee+' marqué la prestation comme payée alors qu\'elle n\'est pas encore facturée, veuillez tout d\'abord la marquer comme facturée avant de la marquer comme payée.',
          ui.ButtonSet.OK);
          e.range.setValue(ancienneValeur);
      }
    }
    else if (numeroColonneEditee == colIndexFacture && nouvelleValeur == "FALSE") {
      const prestationDejaPayee = feuilleEditee.getRange(numeroLigneEditee,colIndexPaiement).getValue();
      // Cas 2 & 3
      const ui = SpreadsheetApp.getUi();
      var result = ui.alert('Attention !',
        'Vous avez ligne '+numeroLigneEditee+' marqué la prestation comme non facturée alors qu\'elle l\'était auparavent'+(prestationDejaPayee?' (et en plus elle est déjà marquée comme réglée)':'')+', êtes-vous sûr de vouloir effectuer cette action?',
        ui.ButtonSet.YES_NO);
      if (result == ui.Button.NO) {
        ui.alert('Annulation effectuée');
        // Remet l'ancienne valeur
        e.range.setValue(ancienneValeur);
      }
    }
  }
}

Si jamais vous avez d’autres astuces, n’hésitez-pas à commenter et à les partager avec nous !