Google Apps Script : Automatiser l’ajout structuré de clients dans une feuille BDD avec copie intelligente de formules

 

📌 Titre :

Google Apps Script : Automatiser l’ajout structuré de clients dans une feuille BDD avec copie intelligente de formules

🔍 Objectif de la fonction

La fonction Ajouter_Client_dans_BDD() permet d’ajouter automatiquement les données d’un client préparé dans une feuille active (type formulaire) vers une feuille nommée "BDD". Elle remplit dynamiquement les colonnes selon les libellés, copie certaines formules, ajoute un enregistrement dans une feuille "Ren-Contrat" et nettoie les champs de saisie pour la prochaine entrée.

📦 Vue d'ensemble de la structure

La feuille active contient les données de saisie organisées en blocs de colonnes en paires :

  • F/G, I/J, K/L, M/N, O/P, Q/R
    Où la colonne de gauche contient le libellé (ex : "Nom", "Ville") et celle de droite la valeur saisie.

⚙️ Détail de la fonction principale

🧠 Fonction principale : Ajouter_Client_dans_BDD()

javascript

function Ajouter_Client_dans_BDD() { ... }

Étapes principales :

  1. Validation des prérequis : si la feuille n’est pas prête (A1 ≠ P ou G2 vide), on arrête.

  2. Copie des ID : les champs AB3 (ID) et AB2 (Num) sont copiés vers AB4 et AB5.

  3. Identification de la ligne cible dans la BDD.

  4. Ajout des champs fixes (ID & Num) à partir de AB4 et AB5.

  5. Remplissage des blocs dynamiques selon les en-têtes de la ligne 10.

  6. Copie des formules (ex : "P1 Mad Date Ren-Contrat", "RECHERCHE DIVERS") depuis la ligne précédente.

  7. Ajout d’un modèle de ligne dans la feuille "Ren-Contrat".

  8. Nettoyage des zones de saisie.

🔧 Fonctions secondaires

✅ remplirValeursFixes(source, target, row, headers)

Ajoute les valeurs de AB4 et AB5 (ID et Num client) dans les colonnes correspondantes de la feuille BDD, en se basant sur les en-têtes de la ligne 10.

🔁 remplirBlocs(source, target, row, headers, blocs)

Lit les libellés et les valeurs dans les blocs F:G à Q:R et insère les données selon les correspondances dans la feuille BDD.

⚙️ Optimisation :

  • Un seul appel à .getValues() pour lire tout le bloc F2:R8 (au lieu de lire cellule par cellule).

  • Utilise une fonction colLetterToIndex() pour convertir les lettres des colonnes ("F", "G", etc.) en index numériques.

🧮 copierFormules(sheet, newRow, headers, colTitre)

Copie la formule R1C1 d’une colonne désignée (ex. "P1 Mad Date Ren-Contrat") depuis la ligne précédente vers la nouvelle.

📄 ajouterRenContrat(feuilleContrat, idEcole)

Ajoute une nouvelle ligne dans la feuille "Ren-Contrat" :

  • Copie les formules de la ligne précédente.

  • Ajoute l’ID Ecole dans la bonne colonne.

🧹 nettoyerSaisie(sheet)

Efface les champs de saisie après l'ajout pour préparer la feuille à un nouveau client.

📚 Annexe : fonction d’utilité

colLetterToIndex(letter)

Convertit une lettre de colonne (ex: "F") en numéro (6), utile pour accéder à des cellules dynamiquement.

✅ Exemple d’utilisation

Quand un utilisateur prépare un client avec :

  • A1 = "P"

  • G2 renseigné (nom du VEF)

  • Des données dans les blocs F:G à Q:R
    Il peut cliquer sur un bouton ou déclencher la fonction Ajouter_Client_dans_BDD() pour :

  • Enregistrer les données dans la BDD selon les libellés.

  • Dupliquer automatiquement les formules nécessaires.

  • Ajouter un suivi dans la feuille "Ren-Contrat".

🧠 Pourquoi ce script est puissant ?

✅ Dynamique : les libellés déterminent l’emplacement — aucun lien figé.
✅ Rapide : lecture optimisée des blocs.
✅ Sécurisé : conditions de validation au début.
✅ Réutilisable : architecture modulaire et claire.

function Ajouter_Client_dans_BDD() {
 
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const source = ss.getActiveSheet();
  const target = ss.getSheetByName("BDD");
  const contrat = ss.getSheetByName("Ren-Contrat");

  const marque = source.getRange("A1").getValue();
  const nomvef = source.getRange("G2").getValue();
  const idEcole = source.getRange("AB3").getValue();
  const numEcole = source.getRange("AB2").getValue();
  if (marque !== "P" || nomvef === "") {
    SpreadsheetApp.getUi().alert("Préparation incomplète. Ajout annulé.");
    return;
  }

  source.getRange("AB4").setValue(idEcole); // Copier AB3 ➜ AB4
  source.getRange("AB5").setValue(numEcole); // Copier AB2 ➜ AB5

  const headers = target.getRange(10, 1, 1, target.getLastColumn()).getValues()[0];
  const newRow = target.getLastRow() + 1;

   remplirValeursFixes(source, target, newRow, headers);
   remplirBlocs(source, target, newRow, headers, [["F", "G"], ["I", "J"], ["K", "L"], ["M", "N"], ["O", "P"], ["Q", "R"]]);

   copierFormules(target, newRow, headers, "P1 Mad Date Ren-Contrat");
   copierFormules(target, newRow, headers, "RECHERCHE DIVERS");

  ajouterRenContrat(contrat, idEcole);

  nettoyerSaisie(source);

  target.activate();
  target.setActiveRange(target.getRange(newRow, 1));
  SpreadsheetApp.getUi().alert("Client ajouté dans BDD !");
}

function remplirValeursFixes(source, target, row, headers) {
  [["AB4", "ID  Ecole"], ["AB5", "Num"]].forEach(([cell, title]) => {
    const idx = headers.indexOf(title);
    if (idx !== -1) {
      const val = source.getRange(cell).getValue();
      target.getRange(row, idx + 1).setValue(val);
    }
  });
}

function remplirBlocs(source, target, row, headers, blocs) {
  // Lire toutes les cellules concernées d’un seul coup (F2:R8)
  const dataRange = source.getRange(2, 6, 7, 12).getValues(); // 7 lignes, colonnes F à R

  blocs.forEach(([k, v], blocIndex) => {
    const colK = colLetterToIndex(k); // Convertir lettre colonne → index
    const colV = colLetterToIndex(v);
   
    for (let i = 0; i < 7; i++) { // lignes 2 à 8 => index 0 à 6
      const header = dataRange[i][colK - 6]; // décalage car F = colonne 6
      const value  = dataRange[i][colV - 6];
      const idx = headers.indexOf(header);
      if (header && value && idx !== -1) {
        target.getRange(row, idx + 1).setValue(value);
      }
    }
  });
}

// 🔁 Convertit une lettre de colonne (ex: "F") en numéro (ex: 6)
 function colLetterToIndex(letter) {
  let col = 0;
  for (let i = 0; i < letter.length; i++) {
    col *= 26;
    col += letter.charCodeAt(i) - 64;
  }
  return col;
}


function copierFormules(sheet, newRow, headers, colTitre) {
  const idx = headers.indexOf(colTitre);
  if (idx !== -1) {
    const oldRow = newRow - 1;
    const formula = sheet.getRange(oldRow, idx + 1).getFormulaR1C1();
    if (formula) sheet.getRange(newRow, idx + 1).setFormulaR1C1(formula);
  }
}

function ajouterRenContrat(feuilleContrat, idEcole) {
  if (!idEcole) return;

  const headers = feuilleContrat.getRange(2, 1, 1, feuilleContrat.getLastColumn()).getValues()[0];
  const row = feuilleContrat.getLastRow() + 1;
  const lastRowFormules = feuilleContrat.getRange(row - 1, 1, 1, feuilleContrat.getLastColumn()).getFormulasR1C1();

  feuilleContrat.getRange(row, 1, 1, feuilleContrat.getLastColumn()).setFormulasR1C1(lastRowFormules);

  const idx = headers.indexOf("ID  Ecole");
  if (idx !== -1) feuilleContrat.getRange(row, idx + 1).setValue(idEcole);
}

function nettoyerSaisie(sheet) {
  const plages = ["H1", "G2:G8", "J2:J8", "L1:L8", "N2:N8", "P2:P8", "R2:R8", "S3", "V2:V8", "F1", "AB4", "AB5"];
  plages.forEach(range => sheet.getRange(range).clearContent());
}


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