Tout utilisateur de Smartsheet finit par se heurter au même mur : il a besoin d'enregistrer une valeur une seule fois, une date d'échéance, un prix négocié, un jalon de référence, puis d'empêcher qui que ce soit de la modifier ensuite. La fonction native "Verrouiller la colonne" ressemble à la réponse évidente, mais elle ne résout pas réellement ce problème. Voyons pourquoi, avant de détailler deux solutions de contournement qui, elles, fonctionnent.
Pourquoi le verrouillage natif d'une colonne n'est pas satisfaisant
Verrouiller une colonne empêche les utilisateurs disposant des autorisations d'édition de toucher aux cellules qu'elle contient. C'est utile, mais cela laisse deux angles morts qui comptent ici :
Les Administrateurs et Propriétaires peuvent toujours modifier une colonne verrouillée. Le verrouillage ne restreint que les Éditeurs. Toute personne disposant d'un accès Administrateur ou Propriétaire sur la feuille peut la modifier malgré tout. Si la personne dont vous essayez de protéger la valeur est justement Administrateur, le verrou ne lui oppose aucune résistance.
Le verrouillage ne distingue pas une "première saisie" d'une "modification ultérieure". Une colonne verrouillée est inaccessible aux Éditeurs en toutes circonstances, y compris pour la toute première saisie. C'est très bien pour un champ que vous remplissez vous-même, en tant que propriétaire de la feuille, une fois pour toutes. Mais cela ne fonctionne pas si le champ doit rester modifiable au départ, puis être figé une fois qu'une condition est remplie, ce qui est exactement le schéma "enregistrer une fois, puis protéger" recherché ici.
Un verrou de colonne protège donc un champ contre une catégorie d'utilisateurs, pas contre un changement survenant au mauvais moment. Pour obtenir cette seconde protection, la valeur doit être enregistrée quelque part que la personne qui modifie la feuille ne peut pas atteindre, et une automatisation doit décider du moment où cet enregistrement a lieu. C'est exactement ce sur quoi reposent les deux méthodes ci-dessous.
Les éléments communs aux deux méthodes
Les deux approches s'appuient sur les mêmes trois ingrédients :
-
Une colonne d'identifiant unique sur la feuille source (une colonne de Numérotation automatique convient bien), afin que les lignes puissent être associées ou réordonnées de façon fiable entre les feuilles.
-
Un indicateur de verrouillage, une liste déroulante à option unique ou une case à cocher, qui marque une ligne comme n'étant plus éligible à la capture.
-
Une seconde feuille, copie vide de la première, qui devient l'endroit où la valeur protégée vit réellement, puisque tout ce qui s'y trouve est hors de portée de la personne qui modifie la feuille source au quotidien.
Une fois ces éléments en place, les deux méthodes ne diffèrent que sur un point : la façon dont la valeur passe de "vient d'être saisie" à "enregistrée en sécurité".
Méthode 1 : déplacer la ligne, puis la ramener
C'est la plus mécanique des deux méthodes, la ligne elle-même voyage entre les feuilles.
On suppose ici que le champ à protéger s'appelle "Valeur".
Mise en place
- Ajoutez la colonne d'identifiant unique (facultative ici, surtout utile pour réordonner les lignes plus tard si nécessaire).
- Ajoutez une colonne liste déroulante "Verrou", avec une seule option, "Verrouillé" (une case à cocher fonctionne tout aussi bien).
- Dupliquez la feuille et videz la copie.
- Sur la feuille source et sur la copie, ajoutez une colonne "Valeur verrouillée", et verrouillez-la sur les deux feuilles.
- Sur la copie uniquement, transformez cette colonne en formule :
=[Valeur]@row
Automatisations
- Feuille source : quand "Valeur" change, et que "Verrou" n'est pas "Verrouillé", déplacer la ligne vers la copie.
- Copie : quand une ligne est ajoutée, définir "Verrou" sur "Verrouillé".
- Copie : quand "Verrou" passe à "Verrouillé", ramener la ligne vers la feuille source.
Une modification de "Valeur" envoie donc la ligne faire un court aller-retour : elle atterrit sur la copie, se voit étiquetée "Verrouillé", sa propre formule "Valeur verrouillée" capture la valeur qui vient d'arriver, et la ligne repart aussitôt vers la feuille source. À partir de là, la condition de verrouillage de l'automatisation source n'est plus remplie, donc les modifications suivantes de "Valeur" ne déclencheront plus de nouvelle capture.
Avantages
- Simple à comprendre : aucune syntaxe de formule entre feuilles, seulement des actions de déplacement.
- La valeur capturée vit dans une colonne réellement distincte ("Valeur verrouillée"), verrouillée sur les deux feuilles, donc inaccessible directement à un Éditeur.
- Fonctionne aussi bien avec une case à cocher, si vous préférez éviter la liste déroulante.
Inconvénients
- La ligne quitte physiquement la feuille source, même si c'est un bref instant. Sur une feuille volumineuse ou très automatisée, cela représente deux exécutions d'automatisation supplémentaires, et donc deux occasions pour qu'un incident survienne en cours de route, par exemple si un utilisateur modifie la ligne pendant son absence.
- Toute personne qui observe la feuille verra des lignes disparaître puis réapparaître brièvement, ce qui peut prêter à confusion sans explication.
- Les rapports, tableaux de bord ou autres automatisations qui font référence à la feuille source par position de ligne peuvent être perturbés par ces allers-retours, Smartsheet replaçant les lignes déplacées en bas de la feuille plutôt qu'à leur position d'origine.
- Un administrateur peut toujours modifier la colonne "Valeur verrouillée" par erreur, s'il n'y prend pas garde.
Méthode 2 : copier la ligne et référencer la feuille copie
Cette version laisse chaque ligne à sa place et utilise des formules pour aller chercher la valeur protégée, plutôt que d'envoyer la ligne la récupérer elle-même.
Mise en place
- Ajoutez la colonne d'identifiant unique. Contrairement à la méthode 1, elle est ici obligatoire, puisque c'est la seule chose sur laquelle s'appuient les formules MATCH ci-dessous.
- Ajoutez la colonne liste déroulante (ou case à cocher) "Verrou", avec l'option unique "Verrouillé".
- Dupliquez la feuille et videz la copie.
- Sur la feuille source, ajoutez une colonne "Valeur verrouillée" et verrouillez-la. Donnez-lui cette formule de colonne :
=IFERROR(INDEX({colonne Valeur dans la feuille copie}, MATCH([Identifiant unique]@row, {colonne Identifiant unique dans la feuille copie}, 0)), "") - Ajoutez une formule similaire pour l'état de verrouillage :
=IFERROR(INDEX({colonne Verrou dans la feuille copie}, MATCH([Identifiant unique]@row, {colonne Identifiant unique dans la feuille copie}, 0)), "")
Automatisations
- Feuille source : quand "Valeur" change, et que "Verrou" n'est pas "Verrouillé", copier la ligne vers la feuille copie.
- Copie : quand une ligne est ajoutée, définir "Verrou" sur "Verrouillé".
C'est tout, seulement deux automatisations au lieu de trois, puisqu'il n'y a pas de trajet retour. La feuille copie devient une archive à sens unique : chaque modification éligible ajoute une nouvelle ligne verrouillée, et les formules INDEX/MATCH de la feuille source ramènent la valeur correspondante pour affichage, sans que la ligne ne bouge jamais.
 |
 |
| Valeurs envoyées par l'automatisation vers la copie | Valeurs ramenées par la formule sur la feuille source |
Avantages
- Les lignes ne quittent jamais la feuille source, donc aucune perturbation de l'ordre des lignes, des rapports ou des tableaux de bord construits dessus.
- Moins d'automatisations à maintenir (deux plutôt que trois), et aucun trajet retour dont il faut se soucier côté timing.
- La feuille copie devient naturellement une piste d'audit : chaque modification verrouillée ajoute une ligne plutôt que d'en écraser une, ce qui permet de voir l'historique complet des valeurs capturées par identifiant unique, si vous ne filtrez pas la feuille.
- La colonne "Valeur verrouillée" repose sur une formule de colonne, ce qui la rend moins susceptible d'être modifiée par erreur par un administrateur, car cela demande des étapes supplémentaires.
Inconvénients
- Davantage de travail de configuration en amont : deux formules INDEX/MATCH par champ protégé, dont il faut soigner les références entre feuilles. Si vous avez du mal avec ce type de formule, vous pouvez utiliser la fonctionnalité "Lier des données de colonne" en mode Table.
- La feuille copie accumulant une nouvelle ligne par capture plutôt qu'une ligne par élément, la formule MATCH renverra la première ligne correspondante qu'elle trouve. Si vous avez besoin de "la dernière valeur verrouillée" plutôt que "une valeur verrouillée", il faudra ajouter un tri ou un filtrage, ou archiver périodiquement les anciennes correspondances hors de la feuille copie. La solution peut aussi passer par INDEX/COLLECT.
- La colonne d'identifiant unique n'est pas facultative ici. Sans elle, les formules MATCH n'ont rien de fiable sur quoi s'appuyer.
Un détail technique qui explique pourquoi les deux méthodes fonctionnent
Voici la restriction des automatisations Smartsheet qui façonne les deux méthodes, même si aucune des deux ne s'y heurte réellement : les cellules contenant des formules de feuilles croisées ou des liens de cellule ne déclenchent pas les automatisations Déplacer la ligne, Copier la ligne, Verrouiller la ligne, Déverrouiller la ligne ou Demander une approbation. Autrement dit, si vous tentiez de déclencher un déplacement ou une copie à partir d'une modification de "Valeur verrouillée" dans la méthode 2 (la colonne INDEX/MATCH), cela ne se déclencherait tout simplement pas, puisque cette colonne est elle-même une formule de feuilles croisées.
Les deux méthodes contournent cela en déclenchant sur "Valeur", un champ simple et directement modifiable sur la feuille source, et en ne réservant la formule de feuilles croisées qu'à l'affichage. Si vous prolongez l'une ou l'autre de ces approches, veillez à garder le déclencheur sur un champ natif et à laisser la formule en aval de celui-ci.
Deux points à intégrer, quelle que soit la version choisie
Verrouiller les colonnes auxiliaires n'arrête pas les Administrateurs. Si la personne dont vous protégez la valeur dispose d'un accès Administrateur ou Propriétaire sur la feuille, elle peut toujours modifier directement les colonnes verrouillées. Si ce risque est réel, la véritable protection vient du fait de conserver la valeur réelle sur une feuille séparée qui ne lui est pas partagée, pas de l'icône de verrou elle-même.
Les demandes de mise à jour peuvent contourner les verrous de colonne. Des utilisateurs complétant une ligne via un formulaire de demande de mise à jour peuvent parfois modifier des champs verrouillés sur la feuille elle-même. Si vous diffusez ce champ via des formulaires ou des demandes de mise à jour, testez spécifiquement ce chemin plutôt que de supposer que le verrou le couvre.
D'autres options natives à connaître
Avant de construire l'une ou l'autre de ces solutions de contournement, il vaut la peine de vérifier si l'une de ces options natives, plus simples, ne couvre pas déjà le besoin. Aucune ne remplace directement les deux méthodes ci-dessus, mais chacune résout une variante proche du problème avec moins de configuration.
Verrouiller automatiquement toute la ligne, pas seulement le champ. Le générateur d'automatisation de Smartsheet propose une action native "Verrouiller la ligne" : déclenchez-la sur un changement de statut (par exemple "quand Statut passe à Terminé"), et toute la ligne devient inaccessible en modification pour les Éditeurs, sans feuille compagnon nécessaire. C'est de loin l'option la plus simple si figer toute la ligne est acceptable. Le hic, c'est qu'elle verrouille tout, pas seulement le champ que vous cherchez à protéger, donc cela ne convient pas si d'autres colonnes de cette ligne doivent rester modifiables après la capture de la valeur. Notez aussi que Smartsheet ne propose pas d'équivalent "Verrouiller la colonne" en tant qu'action d'automatisation. Le verrouillage automatique n'existe qu'au niveau de la ligne, ce qui explique en partie pourquoi les deux méthodes ci-dessus, au niveau du champ, ont besoin d'une feuille compagnon.
Les lignes de base, si le champ concerné est une date de début ou de fin de projet. Si votre champ "Valeur" est une date de projet, la fonctionnalité native de références de Smartsheet (mode Gantt, barre d'outils, "Références") fait peut-être déjà cela nativement : elle capture les dates de début et de fin dans des colonnes en lecture seule "Début de référence" et "Fin de référence", et calcule automatiquement l'écart à mesure que les dates réelles évoluent. Seuls les Administrateurs et Propriétaires de la feuille peuvent définir ou réinitialiser les références, tous les autres la voient en lecture seule. La limite tient à la portée : elle ne capture qu'une paire début/fin sur une feuille en mode Gantt, une seule références par feuille, ce qui ne convient donc pas pour un champ "valeur" arbitraire comme un prix ou un statut.
L'historique des cellules, si le besoin réel est une piste d'audit plutôt qu'une prévention. Un clic droit sur n'importe quelle cellule, puis "Afficher l'historique de la cellule", montre chaque valeur passée, qui l'a modifiée et quand, sans aucune configuration à priori. Cela n'empêche personne de modifier le champ, mais si l'objectif sous-jacent est la traçabilité ("je dois savoir si et quand ceci a changé") plutôt qu'une prévention stricte, cette fonctionnalité native répond peut-être déjà à la question que les deux solutions de contournement cherchent à résoudre.
Laquelle choisir
Commencez par les trois options natives ci-dessus : si verrouiller toute la ligne convient, utilisez l'automatisation Verrouiller la ligne et arrêtez-vous là. Si le champ est réellement une date de début ou de fin de projet, utilisez les références plutôt que de construire quoi que ce soit. Si vous n'avez besoin que d'une piste d'audit, et non d'une prévention stricte, l'historique des cellules répond déjà à ce besoin.
Si aucune de ces options ne convient, et que vous avez spécifiquement besoin qu'un seul champ soit verrouillé pendant que le reste de la ligne reste modifiable, tournez-vous vers les deux solutions de contournement. La méthode 1 est plus rapide à mettre en place si vous êtes à l'aise avec des lignes qui changent brièvement de feuille et que vous n'avez pas de rapports en aval sensibles à l'ordre des lignes. La méthode 2 demande un peu plus de configuration, mais reste le choix le plus robuste pour tout ce qui est largement partagé, utilisé dans des rapports ou inclus dans des tableaux de bord, puisque les lignes de la feuille source ne bougent jamais.
Aucune des deux ne remplace ce qu'offriraient Bridge ou une véritable piste d'audit, mais toutes deux permettent d'obtenir, en natif uniquement, un champ "valeur enregistrée une fois, non modifiable à la légère" réellement protégé, sans avoir besoin d'application premium.
Si vous essayez de mettre cela en place sur votre propre feuille et que vous souhaitez un second avis sur les formules ou la logique d'automatisation, contactez-moi, c'est exactement le type de problème d'architecture Smartsheet que j'aide mes clients à démêler.
