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 […]

Ce que vous allez découvrir
- Solution 1 : utilisation de la protection native Google Sheets
- Solution 2 : création d'un script dédié pour avertir l'utilisateur lors d'une modification de cellule
- Exemple concret : vérifier qu'une case est cochée avant d'en cocher une autre dans Google Sheets
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 :
- la colonne « Facturée » doit être cochée avant de pouvoir cocher la colonne « Payée »
- la colonne « Payée » ne devrait normalement pas être cochée avant la colonne « Facturée » mais il peut y avoir des exceptions
- 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 !
Besoin d'un peu plus d'aide sur Sheets ?
Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !
Découvrir nos formations Sheets- Tutos connexes
- Plus de l'auteur
Bonjour et merci pour ce tuto !
Pourriez-vous m’aider à modifier le script pour que la case paiement soit une date et non une case à cocher ?
Merci beaucoup !!!
Cet avis vous a été utile ?
Bonsoir Jérémy,
L’article contient normalement les bases pour pouvoir l’adapter à d’autres cas, comme celui du contrôle d’un champ date par exemple (en manipulant les variables ancienneValeur & nouvelleValeur).
Après, si besoin, pour vous aider dans votre cas spécifique, Numericoach propose des accompagnement à l’heure (https://school.numericoach.fr/1h-de-coaching-g-suite).
Bon courage pour le développement de votre script !
Cet avis vous a été utile ?
Bonsoir,
J’aurais une question, car la encore j’en suis à 4 café et 3 heures de recherche sur la toile.
Je cherche à savoir si il est possible de récupérer dans votre cas l’adresse de la personne qui à cliqué sur la case. Je n’arrive pas à trouver. J’avais pensé chercher dans le journal des historiques mais je ne trouve pas d’info. Pourriez-vous m’indiquer une piste ou un lien ?
Merci d’avance.
Bonsoir la commu
Cet avis vous a été utile ?
Bonsoir,
Pour être sûre de comprendre, dans le journal des historiques, vous voyez le changement mais avec uniquement le nom de la personne et pas son adresse mail, hors c’est cette information que vous voulez ?
Cet avis vous a été utile ?