🔧 Google Apps Script Avancé : Modifier un Client avec Suivi Automatisé des Changements

🔧 Google Apps Script Avancé : Modifier un Client avec Suivi Automatisé des Changements

 function ModifierClient_dans_BDD_DS() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getActiveSheet();
  const targetSheet = ss.getSheetByName("BDD");
  const interventionSheet = ss.getSheetByName("Liste intervention");
  const ui = SpreadsheetApp.getUi();

  const ligneCible = Number(sourceSheet.getRange("F1").getValue());
  const nomClient = sourceSheet.getRange("G2").getValue();
  const idEcoleSource = sourceSheet.getRange("H1").getValue();
  const personne = sourceSheet.getRange("C1").getValue();

  if (!ligneCible || !nomClient || isNaN(ligneCible)) {
    ui.alert("❌ Ligne cible invalide ou nom client vide.");
    return;
  }
  if (ligneCible < 11 || ligneCible > 100000) {
    ui.alert("❌ Ligne de modification hors plage autorisée.");
    return;
  }

  const headers = targetSheet.getRange(10, 1, 1, targetSheet.getLastColumn()).getValues()[0];
  const colIndexByName = Object.fromEntries(headers.map((h, i) => [h, i]));
  const idColIndex = colIndexByName["ID  Ecole"];
  if (idColIndex === undefined) {
    ui.alert("❌ Colonne 'ID  Ecole' introuvable.");
    return;
  }

  const idEcoleBDD = targetSheet.getRange(ligneCible, idColIndex + 1).getValue();
  if (idEcoleBDD !== idEcoleSource) {
    ui.alert(`⚠️ L'ID Ecole de la ligne ${ligneCible} ne correspond pas (${idEcoleBDD} ≠ ${idEcoleSource}).`);
    return;
  }

  const confirmation = ui.alert("Confirmation", "Voulez-vous vraiment modifier ce client dans la BDD ?", ui.ButtonSet.YES_NO);
  if (confirmation !== ui.Button.YES) return;

  // Fonction pour comparer correctement les dates
  const compareValues = (oldVal, newVal) => {
    if (oldVal instanceof Date && newVal instanceof Date) {
      return oldVal.getTime() === newVal.getTime();
    }
    return oldVal === newVal;
  };

  // Fonction pour formater les dates dans les observations
  const formatDate = d => {
    if (!(d instanceof Date)) return d;
    return Utilities.formatDate(d, ss.getSpreadsheetTimeZone(), "dd/MM/yyyy");
  };

  const valeurs = {
    "Nom": nomClient
  };

  // Récupérer les anciennes valeurs AVANT les mises à jour
  const ancienneValeurMad = targetSheet.getRange(ligneCible, colIndexByName["Mad Dt MNT"] + 1).getValue();
  const ancienneValeureMad = targetSheet.getRange(ligneCible, colIndexByName["eMad Dt MNT"] + 1).getValue();
  const ancienneValeurVM = targetSheet.getRange(ligneCible, colIndexByName["Version Mad"] + 1).getValue();
  const ancienneValeurSMS = targetSheet.getRange(ligneCible, colIndexByName["SMS Dt MNT"] + 1).getValue();
  const ancienneValeurSMSTP = targetSheet.getRange(ligneCible, colIndexByName["SMS TP"] + 1).getValue();

  const dynRanges = [
    "G1|H1",
    "F2|G2", "F3|G3", "F4|G4", "F5|G5", "F6|G6", "F7|G7", "F8|G8",
    "I2|J2", "I3|J3", "I4|J4", "I5|J5", "I6|J6", "I7|J7", "I8|J8",
    "K2|L2", "K3|L3", "K4|L4", "K5|L5", "K6|L6", "K7|L7", "K8|L8",
    "M2|N2", "M3|N3", "M4|N4", "M5|N5", "M6|N6", "M7|N7", "M8|N8",
    "O2|P2", "O3|P3", "O4|P4", "O5|P5", "O6|P6", "O7|P7", "O8|P8",
    "Q2|R2", "Q3|R3", "Q4|R4", "Q5|R5", "Q6|R6", "Q7|R7", "Q8|R8",
    "U8|V8", "S2|S3"
  ];

  const cellsToRead = dynRanges.flatMap(p => p.split("|"));
  const cellObjects = sourceSheet.getRangeList(cellsToRead).getRanges();
  const cellValues = cellObjects.map(r => r.getValue());

  for (let i = 0; i < dynRanges.length; i++) {
    const [label, val] = [cellValues[i * 2], cellValues[i * 2 + 1]];
    if (label) valeurs[label] = val;
  }

  // Stocker les nouvelles valeurs pour comparaison ultérieure
  let nouvelleValeurMad = valeurs["Mad Dt MNT"];
  let nouvelleValeureMad = valeurs["eMad Dt MNT"];
  let nouvelleValeurVM = valeurs["Version Mad"];
  let nouvelleValeurSMS = valeurs["SMS Dt MNT"];
  let nouvelleValeurSMSTP = valeurs["SMS TP"];

  // Appliquer toutes les mises à jour en une seule passe
  const updates = [];
  for (const [colName, val] of Object.entries(valeurs)) {
    const colIndex = colIndexByName[colName];
    if (colIndex !== undefined) {
      updates.push({ col: colIndex + 1, value: val });
    }
  }
  updates.forEach(u => targetSheet.getRange(ligneCible, u.col).setValue(u.value));

  // Préparer les en-têtes pour la feuille d'intervention
  const intHeaders = interventionSheet.getRange(1, 1, 1, interventionSheet.getLastColumn()).getValues()[0];
  const colID = intHeaders.indexOf("ID  Ecole");
  const colDate = intHeaders.indexOf("DATE Suivie");
  const colSuivie = intHeaders.indexOf("SUIVIE");
  const colPers = intHeaders.indexOf("Personne");
  const colObs = intHeaders.indexOf("Observation");
  const colNum = intHeaders.findIndex(h => h === "N°" || h === "Num" || h === "Numéro");
 
  // Fonction pour ajouter une intervention seulement si nécessaire
  const ajouterIntervention = (colonne, ancienneVal, nouvelleVal) => {
    // Vérifier si la valeur a réellement changé
    if (!compareValues(ancienneVal, nouvelleVal) && nouvelleVal !== "") {
      interventionSheet.insertRows(2, 1);
     
      if (colID !== -1) interventionSheet.getRange(2, colID + 1).setValue(idEcoleSource);
      if (colDate !== -1) {
        const dateSuivie = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), "dd/MM/yyyy");
        interventionSheet.getRange(2, colDate + 1).setValue(dateSuivie);
      }
      if (colSuivie !== -1) interventionSheet.getRange(2, colSuivie + 1).setValue(colonne);
      if (colPers !== -1) interventionSheet.getRange(2, colPers + 1).setValue(personne);
      if (colObs !== -1) {
        let observation = `Avant : ${formatDate(ancienneVal)} | Après : ${formatDate(nouvelleVal)}`;
        if (colonne === "SMS Dt MNT") {
          observation += ` | ${nouvelleValeurSMSTP}`;
        }
        interventionSheet.getRange(2, colObs + 1).setValue(observation);
      }
      if (colNum !== -1 && colIndexByName["Num"] !== undefined) {
        const valNum = targetSheet.getRange(ligneCible, colIndexByName["Num"] + 1).getValue();
        interventionSheet.getRange(2, colNum + 1).setValue(valNum);
      }
      return true;
    }
    return false;
  };

  // Ajouter les interventions seulement pour les valeurs qui ont changé
  ajouterIntervention("Mad Dt MNT", ancienneValeurMad, nouvelleValeurMad);
  ajouterIntervention("eMad Dt MNT", ancienneValeureMad, nouvelleValeureMad);
  ajouterIntervention("Version Mad", ancienneValeurVM, nouvelleValeurVM);
  ajouterIntervention("SMS Dt MNT", ancienneValeurSMS, nouvelleValeurSMS);

  // Plages à vider
  sourceSheet.getRange("F1").setValue("");
  const zones = ["H1", "G2:G8", "J2:J8", "L1:L8", "N2:N8", "P2:P8", "R2:R8", "S3", "V2:V8"];
  zones.forEach(range => {
    sourceSheet.getRange(range).clearContent();
  });

  sourceSheet.getRange("A1").setValue("");
  ui.alert("✅ Client modifié avec succès dans la BDD !");
}

Dans ce tutoriel, nous allons décortiquer une fonction puissante : ModifierClient_dans_BDD_DS(). Elle permet de :

  • Modifier des données dans une base (BDD)

  • Comparer les anciennes et nouvelles valeurs

  • Enregistrer automatiquement un suivi d’intervention en cas de changement

  • Nettoyer les champs de saisie après traitement

📌 Objectif de la fonction

Cette fonction vise à :

  1. Sécuriser l’accès à la ligne cible.

  2. Vérifier la cohérence entre les feuilles (ex. : ID École).

  3. Appliquer les modifications provenant d’un formulaire.

  4. Journaliser toute modification de certains champs sensibles (Mad Dt MNT, Version Mad, SMS Dt MNT, etc.) dans une feuille de suivi (Liste intervention).

🧩 Étape 1 : Initialisation et sécurité

js

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getActiveSheet();
const targetSheet = ss.getSheetByName("BDD");
const interventionSheet = ss.getSheetByName("Liste intervention");
const ui = SpreadsheetApp.getUi();

Ces lignes récupèrent les feuilles actives du classeur et l'interface utilisateur pour afficher des alertes.

Ensuite, on récupère des informations critiques :

js

const ligneCible = Number(sourceSheet.getRange("F1").getValue());
const nomClient = sourceSheet.getRange("G2").getValue();
const idEcoleSource = sourceSheet.getRange("H1").getValue();
const personne = sourceSheet.getRange("C1").getValue();

Des vérifications de sécurité sont faites pour éviter de modifier une ligne par erreur :

js

if (!ligneCible || !nomClient || isNaN(ligneCible)) {
  ui.alert("❌ Ligne cible invalide ou nom client vide.");
  return;
}
if (ligneCible < 11 || ligneCible > 100000) {
  ui.alert("❌ Ligne de modification hors plage autorisée.");
  return;
}

🔍 Étape 2 : Vérification de l'ID et confirmation

On s’assure que la ligne ciblée correspond bien à l’école active :

js

const headers = targetSheet.getRange(10, 1, 1, targetSheet.getLastColumn()).getValues()[0];
const colIndexByName = Object.fromEntries(headers.map((h, i) => [h, i]));
const idColIndex = colIndexByName["ID  Ecole"];
const idEcoleBDD = targetSheet.getRange(ligneCible, idColIndex + 1).getValue();

Et on demande une confirmation à l’utilisateur :

js

const confirmation = ui.alert("Confirmation", "Voulez-vous vraiment modifier ce client dans la BDD ?", ui.ButtonSet.YES_NO);
if (confirmation !== ui.Button.YES) return;

🧠 Étape 3 : Préparation et lecture des données

On crée un objet valeurs qui contiendra tous les libellés et leurs nouvelles valeurs saisies par l’utilisateur :

js

const dynRanges = [ ... ]; // liste de paires cellules libellé | valeur

On lit ces cellules en une passe optimisée :

js

const cellObjects = sourceSheet.getRangeList(cellsToRead).getRanges();
const cellValues = cellObjects.map(r => r.getValue());

for (let i = 0; i < dynRanges.length; i++) {
  const [label, val] = [cellValues[i * 2], cellValues[i * 2 + 1]];
  if (label) valeurs[label] = val;
}

🧮 Étape 4 : Comparaison des anciennes valeurs

Avant de modifier quoi que ce soit, on capture les anciennes valeurs des champs sensibles :

js

const ancienneValeurMad = targetSheet.getRange(ligneCible, colIndexByName["Mad Dt MNT"] + 1).getValue();

Et on récupère les nouvelles à comparer après :

js

let nouvelleValeurMad = valeurs["Mad Dt MNT"];

Une fonction compareValues permet de gérer à la fois les dates et les valeurs simples :

js

const compareValues = (oldVal, newVal) => {
  if (oldVal instanceof Date && newVal instanceof Date) {
    return oldVal.getTime() === newVal.getTime();
  }
  return oldVal === newVal;
};

✏️ Étape 5 : Mise à jour des données dans BDD

On applique tous les changements en une boucle, selon les noms de colonnes :

js

const updates = [];
for (const [colName, val] of Object.entries(valeurs)) {
  const colIndex = colIndexByName[colName];
  if (colIndex !== undefined) {
    updates.push({ col: colIndex + 1, value: val });
  }
}
updates.forEach(u => targetSheet.getRange(ligneCible, u.col).setValue(u.value));

🗃️ Étape 6 : Journalisation dans Liste intervention

Seuls les changements significatifs déclenchent une entrée dans la feuille Liste intervention.

js

const ajouterIntervention = (colonne, ancienneVal, nouvelleVal) => {
  if (!compareValues(ancienneVal, nouvelleVal) && nouvelleVal !== "") {
    // Insertion en haut de liste
    interventionSheet.insertRows(2, 1);
    ...
    return true;
  }
  return false;
};

Cette fonction ajoute :

  • l’ID École

  • la date du jour

  • le nom du champ modifié

  • la personne responsable

  • une observation claire Avant / Après

  • le numéro client si disponible

🧼 Étape 7 : Nettoyage final

Une fois le client modifié, on vide les champs de saisie pour éviter les erreurs :

js

sourceSheet.getRange("F1").setValue("");
const zones = ["H1", "G2:G8", ..., "S3", "V2:V8"];
zones.forEach(range => {
  sourceSheet.getRange(range).clearContent();
});

Et on affiche une confirmation :

js

ui.alert("✅ Client modifié avec succès dans la BDD !");

✅ Bilan

Cette fonction combine sécurité, performance et traçabilité. Grâce à une architecture propre et modulaire, elle peut facilement être :

  • étendue (ajout d’autres champs à surveiller)

  • adaptée à d’autres feuilles

  • améliorée (ajout de logs, notifications email, etc.)


Commentaires

Enregistrer un commentaire

Posts les plus consultés de ce blog

🛠 Utilisation de CustomUIEditor pour Personnaliser le Ruban Excel

Image
  🛠 Utilisation de CustomUIEditor pour Personnaliser le Ruban Excel 📥 1. Téléchargement de CustomUIEditor CustomUIEditor est un outil gratuit qui permet de modifier le ruban d'Excel (ou Word) en ajoutant des onglets, groupes et boutons personnalisés via du code XML. → Voici comment le télécharger : Rendez-vous sur ce lien de confiance (ou recherchez "CustomUIEditor for Office 2007" sur Google). Téléchargez le fichier ZIP. Extrayez-le puis installez-le sur votre ordinateur (double-cliquez sur setup.exe ). ✨ Il fonctionne même avec les versions récentes d'Excel (2013, 2016, 365). ⚙️ Instructions de téléchargement et installation Clique sur le lien ci-dessus pour lancer le téléchargement du fichier  OfficeCustomUIEditorSetup.exe . Une fois téléchargé, lance l’exécutable pour installer l’éditeur. La plupart du temps, l’installation se fait rapidement sans intervention complexe.
Lire la suite

🗓️ Simulateur de Congés et Calendrier Collaboratif sous Google Sheets

Image
🗓️ Simulateur de Congés et Calendrier Collaboratif sous Google Sheets ✨ Objectif du simulateur Ce simulateur a été conçu pour gérer efficacement les congés du personnel d'une école ou d'une petite structure. Il permet : D'enregistrer les périodes de congé prises par chaque salarié. De calculer automatiquement les jours éligibles, hors dimanches et jours fériés. De visualiser les chevauchements de congés sur un agenda annuel. D'avoir un affichage par personne ou par date (vue calendrier). ⚡ Structure globale Le simulateur est composé de deux grandes parties : Agenda individuel (par personne) avec dates, jours fériés, dimanches exclus. Calendrier global condensé où les colonnes représentent les dates, et les lignes les personnes. 📋 Partie 1 : Simulation individuelle (feuille principale) ✅ Saisie des périodes de congés Période 1 : Date début N4 et date fin O4 Période 2 : (facultatif) Date début N5 et date fin O5 🔹 Affichage des dates dans la colo...
Lire la suite

Explication VBA : Facturation automatique Gestion client

Mettre à jour automatiquement les noms des colonnes (en-têtes)  dans un tableau structuré Excel appelé  Client ModifierEntetesEtLibelles Sub ModifierEntetesEtLibelles()     Dim ws As Worksheet     Set ws = Param_CL_et_PRIX ' Feuille contenant les tableaux     Dim tblClients As ListObject     Set tblClients = ws.ListObjects("Clients")     Dim tblParam As ListObject     Set tblParam = ws.ListObjects("param_nom_service_produit")     Dim i As Long     Dim ancienNom As String, nouveauNom As String     Dim cell As Range     ' Parcourir chaque...
Lire la suite