Techniques pour protéger une feuille de calcul Google Sheets avec Apps Script

La protection des feuilles de calcul est un aspect crucial de la gestion des données collaboratives dans Google Sheets. Grâce à Google Apps Script, il est possible d’automatiser la gestion des protections afin de sécuriser certaines cellules ou d’établir des droits spécifiques pour différents utilisateurs. Cet article explore plusieurs techniques basées sur des scripts Google Apps Script.

Pourquoi protéger une feuille de calcul ?

Les feuilles de calcul Google permettent de collaborer efficacement, mais sans protections, elles deviennent vulnérables aux erreurs ou modifications non désirées. En utilisant Apps Script, il est possible de :

• Limiter l’accès à certaines cellules.
• Protéger des plages spécifiques contre les modifications.
• Contrôler qui peut voir ou éditer les données sensibles.

Étapes préliminaires

Créer un projet Google Apps Script

1. Ouvrez Google Apps Script.
2. Créez un projet autonome.
3. Collez les scripts dans l’éditeur de code.
4. Exécutez les fonctions nécessaires à partir de l’éditeur.

Important : Ces scripts doivent être utilisés dans un contexte contrôlé, comme l’éditeur de scripts. Attention à ne pas activer les déclencheurs automatiques sans validation.

Cas d’utilisation et scripts exemples

1. Protéger une feuille complète avec le service Spreadsheet

Ce script crée une feuille protégée où un utilisateur spécifique peut modifier uniquement une feuille nommée “Feuille 1”.

function exemple1() {
  const utilisateurA = "adresse_email_utilisateur@gmail.com"; // Remplacez par l'adresse de l'utilisateur.

  // Preparation
  const feuilleCalcul = SpreadsheetApp.create("Exemple_Feuille");
  const idFeuille = feuilleCalcul.getId();
  feuilleCalcul.getSheets()[0].setName("Feuille 1");
  feuilleCalcul.insertSheet("Feuille 2");
  feuilleCalcul.addEditor(utilisateurA);
  SpreadsheetApp.flush();

  DriveApp.getFileById(idFeuille).moveTo(
    DriveApp.getFileById(ScriptApp.getScriptId()).getParents().next()
  );

  // Protection
  feuilleCalcul.getSheets().forEach((feuille) => {
    feuille.getProtections(SpreadsheetApp.ProtectionType.SHEET).forEach((protection) => protection.remove());

    const nouvelleProtection = feuille.protect();
    nouvelleProtection.removeEditors(nouvelleProtection.getEditors());

    if (feuille.getSheetName() === "Feuille1") {
      nouvelleProtection.addEditor(utilisateurA);
    }

    if (nouvelleProtection.canDomainEdit()) {
      nouvelleProtection.setDomainEdit(false);
    }
  });
}

2. Protéger une feuille complète avec l’API Drive

Utilisant l’API avancée Drive, ce script verrouille une feuille pour tous, y compris le propriétaire, sauf pour les utilisateurs explicitement définis.

function exemple2() {
  const utilisateurA = "adresse_email_utilisateur@gmail.com";

  // Preparation
  const feuilleCalcul = SpreadsheetApp.create("Feuille_Verrouillee");
  const idFeuille = feuilleCalcul.getId();
  feuilleCalcul.getSheets()[0].setName("Feuille 1");
  feuilleCalcul.insertSheet("Feuille 2");
  feuilleCalcul.addEditor(utilisateurA);
  SpreadsheetApp.flush();

  DriveApp.getFileById(idFeuille).moveTo(
    DriveApp.getFileById(ScriptApp.getScriptId()).getParents().next()
  );

  // Protection avec Drive API
  Drive.Files.update(
    { contentRestrictions: [{ readOnly: true, ownerRestricted: true }] },
    idFeuille
  );
}

3. Protéger des plages de cellules pour des utilisateurs spécifiques

Ce script définit différentes plages protégées pour plusieurs utilisateurs sur une même feuille.

• Plage A1:B3 : protégée pour tous sauf le propriétaire.

• Plage A4:B6 : modifiable par utilisateur A uniquement.

• Plage A7:B9 : modifiable par utilisateur B uniquement.

function exemple3() {
  const utilisateurA = "adresse_email_utilisateurA@gmail.com";
  const utilisateurB = "adresse_email_utilisateurB@gmail.com";

  const configuration = {
    protections: [
      { plages: ["A1:B3"], emails: [] },
      { plages: ["A4:B6"], emails: [utilisateurA] },
      { plages: ["A7:B9"], emails: [utilisateurB] },
    ],
  };

  // Preparation
  const feuilleCalcul = SpreadsheetApp.create("Protection_Personnalisee");
  const feuille = feuilleCalcul.getSheets()[0].setName("Feuille 1");
  feuilleCalcul.addEditors([utilisateurA, utilisateurB]);
  SpreadsheetApp.flush();

  // Application des protections
  const couleurs = ["#f4cccc", "#d9ead3", "#c9daf8"];
  configuration.protections.forEach(({ plages, emails }, index) => {
    feuille
      .getRangeList(plages)
      .getRanges()
      .forEach((plage) => {
        plage.setBackground(couleurs[index]);
        const protection = plage.protect();
        protection.removeEditors(protection.getEditors());
        if (emails.length > 0) {
          protection.addEditors(emails);
        }
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }
      });
  });
}

4. Supprimer toutes les protections

Ce script supprime toutes les protections d’une feuille.

function exemple4a() {
  const idFeuille = "ID_DE_LA_FEUILLE"; // Remplacez par l'ID de votre feuille.
  const nomFeuille = "Sheet1"; // Remplacez par le nom de votre feuille.

  const feuille = SpreadsheetApp.openById(idFeuille).getSheetByName(nomFeuille);

  [SpreadsheetApp.ProtectionType.SHEET, SpreadsheetApp.ProtectionType.RANGE].forEach((typeProtection) =>
    feuille.getProtections(typeProtection).forEach((protection) => protection.remove())
  );
}

Meilleures pratiques

• Commentaires : Documentez votre code pour faciliter sa compréhension.
• Tests : Exécutez vos scripts dans un environnement de test avant de les appliquer sur des feuilles critiques.
• Gestion des erreurs : Ajoutez des validations pour éviter des exécutions involontaires.

Grâce à ces techniques, vous pouvez protéger efficacement vos feuilles de calcul Google et gérer les accès selon vos besoins. Pour approfondir ces sujets, vous pouvez explorer des APIs avancées comme l’API Sheets ou Drive pour plus de flexibilité et d’automatisation.