Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets et Apps Script : comment incrémenter une liste automatiquement / solution 2 avec un script

Google Sheets et Apps Script : comment incrémenter une liste automatiquement / solution 2 avec un script

Dans la série sur la création automatique d’un nouvel ID, voici la solution 2 utilisant un script. La solution 1 utilisant uniquement des formules est à lire ici. La création d’information dans une ou plusieurs […]

personnes ont consulté cet article

4 minutes

Rédigé par Antoine MARTIN - il y a 2 ans

Ce que vous allez découvrir

  • Solution 2 :
    script permettant d'écrire un ID qui suit le dernier ID donné de la colonne.

Google Sheets et Apps Script : comment incrémenter une liste automatiquement / solution 2 avec un script

Dans la série sur la création automatique d’un nouvel ID, voici la solution 2 utilisant un script. La solution 1 utilisant uniquement des formules est à lire ici.
La création d’information dans une ou plusieurs cellules d’une table de données est un problème récurrent dans la gestion de données. Christelle l’avait évoqué en exemple dans cet excellent article sur l’utilisation de bibliothèque. Je vais décortiquer ici la création d’un nouvel ID répondant à une syntaxe précise (Année + numéro du dernier ID incrémenté de « 1 »).


Solution 2 :
script permettant d’écrire un ID qui suit le dernier ID donné de la colonne.

Script intégral

function onEdit() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetDevisAuto = ss.getSheetByName("DEVIS NUMEROTATION AUTO");
  const sheetDevisAutoId = sheetDevisAuto.getSheetId();
  const rangeEdited = sheetDevisAuto.getActiveCell();
  const rangeEditedSheetId = rangeEdited.getSheet().getSheetId();
  // Si la cellule modifiée est dans le sheet Devis Auto
  if (sheetDevisAutoId == rangeEditedSheetId) {
    const rangeEditedCol = rangeEdited.getColumn();
    // si  et si colonne de titre (3)
    if (rangeEditedCol == 3) {
      const rangeEditRow = rangeEdited.getRow();
      const devisId = sheetDevisAuto.getRange(rangeEditRow, 1).getValue();
      // si aucun ID renseigné
      if (devisId == "") {
        //recherche du dernier devis de l'année
        const thisDate = new Date();
        const thisYear = thisDate.getFullYear();
        // recueil des devis
        const devisIdsValues = sheetDevisAuto.getRange(2, 1, sheetDevisAuto.getLastRow(), 1).getValues();
        // recherche du plus grand nombre aprés le "-" de l'année.
        let numMax = 0;
        const devisIds = devisIdsValues.map(function (id) {
          const thisId = id[0];
          const thisIdSplit = thisId.split("-");
          if (thisIdSplit[0] == thisYear) {
            const thisNum = parseInt(thisIdSplit[1], 10);
            if (numMax < thisNum) {
              numMax = thisNum;
            }
          }
        })
        const newNum =  "00"+(numMax+1) ;
        const newDevisId = thisYear + "-" + newNum.substr(-3);
        sheetDevisAuto.getRange(rangeEditRow, 1).setValue(newDevisId);
        sheetDevisAuto.getRange(rangeEditRow, 2).setValue(thisDate);
      }
    }
  }
}

Explications pas à pas

Créer une fonction onEdit() qui rappelons-le, se déclenche à chaque modification sans besoin d’ajouter un déclencheur.

function onEdit() {

Il faut s’assurer que la cellule modifiée est bien une cellule qui doit déclencher le script : récupérez l’ID de la feuille recueillant les devis d’une part et celui de la feuille qui accueille la cellule modifiée d’autre part.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetDevisAuto = ss.getSheetByName("DEVIS NUMEROTATION AUTO");
  const sheetDevisAutoId = sheetDevisAuto.getSheetId();
  const rangeEdited = sheetDevisAuto.getActiveCell();
  const rangeEditedSheetId = rangeEdited.getSheet().getSheetId(); 

Si ce sont les mêmes on continue.

if (sheetDevisAutoId == rangeEditedSheetId) {

Ensuite il faut s’assurer que la cellule modifiée est celle du titre (dans mon exemple c’est la troisième colonne). Je récupère donc le numéro de colonne et le compare à « 3 ».

    const rangeEditedCol = rangeEdited.getColumn();
    if (rangeEditedCol == 3) {

Ensuite, je m’assure qu’il s’agit bien d’un nouveau devis. En effet, il ne faut surtout pas attribuer un nouvel ID dès qu’on modifie le titre d’un devis qui a déjà un identifiant. Je récupère pour cela la valeur présente dans la première colonne de la même ligne et vérifie qu’elle est bien nulle :

      const rangeEditRow = rangeEdited.getRow();
      const devisId = sheetDevisAuto.getRange(rangeEditRow, 1).getValue();
      // si aucun ID renseigné
      if (devisId == "") {

Je peux enfin commencer à fabriquer l’ID du nouveau devis.

Commençons par récupérer l’année en cours :

        const thisDate = new Date();
        const thisYear = thisDate.getFullYear();

Ensuite récupérons les ID de devis déjà renseignés :

const devisIdsValues = sheetDevisAuto.getRange(2, 1, sheetDevisAuto.getLastRow(), 1).getValues();

Il faudra trouver parmi les ID commençant par l’année en cours, le plus grand nombre situé après le « -« . Je crée tout d’abord la valeur maximum qui pour l’instant est « 0 ».

let numMax = 0;

Je lance ensuite une boucle avec « MAP » sur tous les ID de devis déjà renseignés.

        const devisIds = devisIdsValues.map(function (id) {

Attention, les valeurs sont dans une colonne, elles sont donc listées dans un tableau de tableaux. La boucle renvoie un tableau correspondant à chaque ligne qui ne contient qu’un seul élément. Il faut donc chercher l’index 0 de ce tableau. Je découpe le devis en deux parties avec la fonction split et le séparateur « – » :

          const thisId = id[0];
          const thisIdSplit = thisId.split("-");

Par exemple « 2021-015 » sera transformé en un tableau : [ « 2021 » , « 015 » ]. Je compare l’index 0 (le premier élément) de ce tableau à l’année en cours :

 if (thisIdSplit[0] == thisYear) {

Si le devis commence par l’année en cours, je transforme l’index 1 (le deuxième élément) en nombre (avec parseInt sur une base 10) pour pouvoir le manipuler mathématiquement :

   const thisNum = parseInt(thisIdSplit[1], 10);

Si la valeur maximale est plus petite que le numéro du devis que la boucle est en train de lire, alors je lui attribue cette nouvelle valeur :

        if (numMax < thisNum) {
          numMax = thisNum;
        }

Je ferme la condition sur la comparaison avec l’année en cours et je ferme la boucle « Map »

    }
})

Nous avons à présent l’année en cours et la valeur maximale qu’il faut ré-assembler pour créer le nouvel ID. Le numéro est sur trois chiffres : donc j’incrémente la valeur maximale trouvée dans la boucle, puis je rajoute deux « 0 » au début de la chaîne et je la recoupe sur les trois derniers caractères.
Exemple : si « 12 » est le dernier numéro attribué, je rajoute 1 : (12 + 1 = 13), je concatène deux « 0 » sans oublier les guillemets sinon le script va ajouter la valeur 00 en nombre : (« 00 » + 13 = « 0013 »), puis je récupère les trois derniers caractères : substr(-3). Enfin, je concatène l’année en cours, le signe « – » et le nouveau numéro :

        const newNum =  "00"+(numMax+1) ;
        const newDevisId = thisYear + "-" + newNum.substr(-3); 

Enfin, il ne me reste plus qu’à écrire ce devis, et ketchup sur la frite, j’en profite aussi pour écrire la date de la création du devis dans la deuxième colonne :

        sheetDevisAuto.getRange(rangeEditRow, 1).setValue(newDevisId);
        sheetDevisAuto.getRange(rangeEditRow, 2).setValue(thisDate);
      }
    }
  }
}

Pensez à enregistrer le script et à le lancer une première fois directement pour valider les autorisations. Merci pour vos retours et témoignages d’utilisation de ce script, n’hésitez pas à laisser un commentaire 🙂

Besoin d'un peu plus d'aide sur Sheets ?

Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !

Découvrir nos formations Sheets

Rédacteur

Photo de profil de l'auteur
Antoine MARTIN

Consultant et formateur sur les outils bureautiques, j'ai intégré l'équipe de Numericoach en 2020. J'accompagne les utilisateurs de Google Workspace à trouver des solutions répondant à leurs besoins. Mes domaines de prédilections sont les outils Sheets, Docs, Slides et Google Apps Script.

S’abonner
Notification pour
guest
4 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
Jean MICHEL-LÉVY
Jean MICHEL-LÉVY

Chapeau !!! Beau script et qualités pédagogiques au top ! Merci.

Cet avis vous a été utile ?

Antoine MARTIN

Merci Jean ! Bonne journée 🙂

Cet avis vous a été utile ?

Bravo Antoine ! Excellent article que je viens de savourer.

Cet avis vous a été utile ?

ETHEVE
ETHEVE

Bonjour,
Je fais actuellement un projet recherche en lien avec mes études de master. Je début sur sheet et je souhaite savoir comment faire un suivi de numéro ? Sans utiliser la liste déroulante :-/ . Le projet consiste a écrire un rapport sur sheet avec des numéros suivis comme :
05.1 PROJET1 ;
05.1.1 …
0.5.2 ; PROJET 2;
05.1.2….

Cet avis vous a été utile ?