Verouillage automatique de certaines cellule

Bonjour la communauté,

Je cherche à créer un script qui me permettrais de verrouiller certaines cellules lorsque dans mon tableau, dans la colonne A, j'entre un utilisateur. Pour faire simple, voici un exemple concret :

Utilisateur 1 est rentré dans la cellule A2. Automatiquement, les cellules B2, D2 et E2 se verrouillent pour qu'uniquement cet utilisateur (adresse mail) puisse modifier ces cellules. Si Utilisateur 2 est indiqué dans la cellule A3, celui-ci ne peut en aucun cas pouvoir modifier les cellules B2, D2 et E2 mais uniquement les siennes. C'est à dire B3, D3 et E3.

J'ai essayé de passer par une IA mais le code qu'il m'a fourni ne fonctionne qu'a moitié. Lorsque j'entre un utilisateur, celui-ci ne peut même plus modifié ces propres cellules.

Voici le code fourni par l'IA :

function onEdit(e) {
var sheet = e.source.getActiveSheet();

// Vérifier que nous sommes sur la feuille "BotaniK"
if (sheet.getName() !== "BotaniK") return;

var range = e.range;
var editedColumn = range.getColumn();
var editedRow = range.getRow();
var currentUser = Session.getActiveUser().getEmail();
var ownerEmail = e.source.getOwner().getEmail();

// Vérifier si une cellule dans la colonne A a été éditée
if (editedColumn === 1 && editedRow > 1) {
var userEmail = range.getValue();

if (userEmail) {
var rangesToProtect = [
sheet.getRange(editedRow, 2), // Colonne B
sheet.getRange(editedRow, 4), // Colonne D
sheet.getRange(editedRow, 5) // Colonne E
];

// Supprimer toute protection existante sur ces cellules
rangesToProtect.forEach(function(cellRange) {
var protections = cellRange.getProtections(SpreadsheetApp.ProtectionType.RANGE);
protections.forEach(function(protection) {
protection.remove();
});
});

// Appliquer la protection et autoriser seulement l'utilisateur à éditer ces cellules
rangesToProtect.forEach(function(cellRange) {
var protection = cellRange.protect().setDescription('Protected Range');
protection.addEditor(userEmail); // Autoriser l'utilisateur spécifique
protection.addEditor(ownerEmail); // Autoriser le propriétaire à éditer aussi
protection.removeEditors(protection.getEditors().filter(function(email) {
return email !== userEmail && email !== ownerEmail; // Retirer tous les autres éditeurs sauf le propriétaire
}));
if (protection.canDomainEdit()) {
protection.setDomainEdit(false); // Désactiver l'édition pour le domaine entier
}
});
}
}

// Vérifier si une cellule des colonnes B, D, ou E est modifiée
if (editedColumn === 2 || editedColumn === 4 || editedColumn === 5) {
var authorizedEmail = sheet.getRange(editedRow, 1).getValue();

// Autoriser le propriétaire de la feuille à toujours modifier les cellules
if (currentUser !== authorizedEmail && currentUser !== ownerEmail) {
e.range.setValue(e.oldValue); // Réinitialiser la cellule à la valeur précédente
SpreadsheetApp.getUi().alert("Vous n'êtes pas autorisé à modifier cette cellule.");
}
}
}

Je précise que le propriétaire de la feuille doit pouvoir modifié toutes les cellules mentionné avant.

Je vous serai vraiment reconnaissant si quelqu'un pouvais m'aider à résoudre ce problème !

Avis au amateur de défi :)

Bonjour !

À tester :

function onEdit(e){
  const sheet = e.source.getActiveSheet();
  const col = e.range.getColumn();
  const row = e.range.getRow();
  const owner = e.source.getOwner().getEmail();
  console.log(owner)
  const user = e.range.getValue();
  const rangeToProtect = [
    2, // colonne B
    4, // colonne D
    5, // colonne E
  ]

  if(sheet.getName()!=="BotaniK" || col!==1 || row<=2 ){
    console.info("edition sur mauvaise plage");
    return;
  }
  if(testEmail(user)===false){
    console.info('adresse email invalide');
    SpreadsheetApp.getUi().alert("Il n'y a pas d'adresse email valide en ligne "+row);
    return;
  }

  const protectedRanges = sheet.getRangeList(rangeToProtect.map(col => `R${row}C${col}`)).getRanges();
  protectedRanges.forEach((range) => {
    protection = range.protect().setDescription(`Protection : ${user}`);
    protection.removeEditors(protection.getEditors());
    protection.addEditor(user);
    protection.addEditor(owner);
  });
}

function testEmail(email){
  const regex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  return regex.test(email);
}

Merci !!

On ne remplacera jamais l'humain :).

Cela fonctionne correctement maintenant. Il faudrait juste que lorsque j'enlève l'utilisateur ou le remplace par un autre, ça efface les protections des plages situées sur la droite. Car au bout d'un moment je vais me retrouver avec des centaines de protections ! Et j'ai peur que certains utilisateurs entrent en conflits. De plus, j'aimerais ajouter un éditeur qui fonctionnerait comme le propriétaire, il doit pouvoir modifier n'importe quelles cellules.

Si vous pouvez encore me donner quelques minutes de votre temps.

L'IA s'en sort bien, mais à des difficultés avec la vue global d'une problématique, et va souvent proposer une solution qui fonctionne pour un probleme X sans prévoir les variations Y et Z, c'est une béquille, un support d'aide.

Voici une modification avec quelques lignes qui suppriment les protections sur les ranges, je l'ai saisie sans faire de test, à voir si ca marche en l'état :

function onEdit(e){
  const sheet = e.source.getActiveSheet();
  const col = e.range.getColumn();
  const row = e.range.getRow();
  const owner = e.source.getOwner().getEmail();
  console.log(owner)
  const user = e.range.getValue();
  const rangeToProtect = [
    2, // colonne B
    4, // colonne D
    5, // colonne E
  ]

  if(sheet.getName()!=="BotaniK" || col!==1 || row<=2 ){
    console.info("edition sur mauvaise plage");
    return;
  }
  if(testEmail(user)===false){
    console.info('adresse email invalide');
    SpreadsheetApp.getUi().alert("Il n'y a pas d'adresse email valide en ligne "+row);
    return;
  }

  rangeToProtect.forEach(col => {
    const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    protections.forEach(protection => {
      if (protection.getRange().getRow() === row && protection.getRange().getColumn() === col) {
        protection.remove();
      }
    });
  });

  const protectedRanges = sheet.getRangeList(rangeToProtect.map(col => `R${row}C${col}`)).getRanges();
  protectedRanges.forEach((range) => {
    protection = range.protect().setDescription(`Protection : ${user}`);
    protection.removeEditors(protection.getEditors());
    protection.addEditor(user);
    protection.addEditor(owner);
  });
}

function testEmail(email){
  const regex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  return regex.test(email);
}

Que dire à part que ça fonctionne à merveille !

J'essaie souvent de faire des scripts pour google sheets mais je part de 0. Depuis que l'IA est disponible au commun des mortels, j'ai forcement progressé au moins pour la compréhension de certaines lignes. Le soucis est que je bloc souvent sur un problème que l'IA ne parviens pas à résoudre.

En tout cas, merci pour votre aide précieuse, vous m'avez fait gagné un temps énorme !!