Google Apps Script : comment partager des informations spécifiques à des collaborateurs ?

0

Olivier travaille dans une usine de production d’arbres à came et vilebrequins pour moteurs de camions. Il est en charge de la qualité et donc de la gestion des rebuts. Pour traiter ces rebuts, Olivier affecte à chacun des collaborateurs de son équipe un ou plusieurs rebuts à réparer. Il effectue cette opération chaque lundi, pour la semaine, avec son document Google Sheets (Cliquez sur ce lien pour obtenir une copie) comprenant deux onglets : 

  • GESTION FINANCIÈRE : cette onglet regroupe des informations financières issues de la feuille LISTE REBUTS

Les collaborateurs d’Olivier : Julie, Paul et Sophie, ne doivent pas avoir accès à cette feuille. Pour respecter cette dernière contrainte, Olivier ne partage donc pas son Google Sheets avec ses collaborateurs (Voir article sur les paramètres de partages

Pour que son équipe puisse voir les différents rebuts qu’ils ont à traiter, chaque personne possède un Google Sheets copié à partir d’un modèle (Cliquez sur ce lien pour obtenir une copie). 

Dans ce dernier on retrouve deux onglets : 

  • LISTE REBUT : contient la liste des rebuts affectés à un collaborateur en particulier. Dans cet onglet, l’utilisateur indiquera le statut de réparation de chaque rebut.
    • Exemple : dans le Google Sheets de Paul se trouve uniquement les rebuts affectés à Paul 
  • INFORMATIONS PERSONNELLES : contient les informations personnelles du propriétaire du fichier auxquelles les autres collaborateurs n’ont pas accès à l’exception d’Olivier

Pour organiser la semaine et le travail de chacun, Olivier effectue, chaque Lundi, les opérations suivantes :

  1. Import de la liste des rebuts en provenance d’un e-mail du chef de production. Cette opération est automatisée grâce à Google Apps Script (Voir article Google Apps Script : importer automatiquement un CSV depuis une pièce jointe de Gmail vers un Google Sheets)
  2. Affectation des rebuts à chaque collaborateurs
  3. Pour chaque collaborateur
    1. Copie de la liste des rebuts qui lui sont affectés
    2. Insertion des données dans le document du collaborateur, à la suite des données existantes
    3. Vérification qu’il n’y ait pas de doublons, c’est à dire un rebut à traiter indiqué sur plusieurs lignes
    4. Ajout du statut à traiter pour chaque rebut

Des opérations chronophages et avec peu de valeur ajoutée. Heureusement, Google nous donne la possibilité de les automatiser avec son outil de développement Google Apps Script.

Récupération de la liste des rebuts sous la forme d’un tableau de données

Tout d’abord créons un script à partir du Google Sheets utilisé par Olivier (Voir cet article sur la mise en place d’un script)

Ensuite déclarons notre première fonction : 

function recuperationDonnees()

Cette fonction aura pour but de récupérer et renvoyer un tableau de données correspondant à la liste des rebuts avec leurs informations. 

Recherche de l’onglet LISTE REBUT du Google Sheets d’Olivier

var REBUTS = spreadsheetApp.getActive().getSheetByName("LISTE REBUTS")
  • La classe SpreadsheetApp permet d’intervenir sur le fichier Google Sheets. 
  • getActive()  : permet de récupérer le Google Sheets à partir duquel est exécuté le script
  • getSheetByName("LISTE REBUTS")  : permet de récupérer un onglet du Google Sheets avec son nom. Ici nous supposons que l’onglet concerné est intitulé “LISTE REBUTS” ( ATTENTION : les majuscules et minuscules sont importantes dans la recherche de l’onglet)
  • var REBUTS : La variable REBUTS est l’onglet du Google Sheets à partir duquel nous récupérons la liste des rebuts

Création d’un tableau de données à partir des données de l’onglet LISTE REBUTS

var tableauRebuts=REBUTS.getRange("A2:E").getValues()
  • REBUTS.getRange(A1Notation) : renvoie la plage avec les coordonnées écrites comme dans les formules d’un Google Sheets. Dans notre cas nous souhaitons l’ensemble de la liste des rebuts. Nous démarrons donc à la ligne 2 de la colonne A pour ne pas avoir les entêtes. Nous allons ensuite jusqu’à la colonne E sans ligne de fin pour être certains de récupérer toute les lignes
  • getValues() : cette fonction renvoie un tableau de données. ATTENTION : ne pas confondre avec getValue()  (sans “s”) qui renvoie une seule valeur et non un tableau
  • var tableauRebuts : récupère le tableau de données 

Suppression des colonnes du tableau non nécessaires pour les collaborateurs 

Le tableau contenu dans la variable tableauRebuts possède une colonne qui ne va pas être utile aux collaborateurs : la date d’import. Voyons comment supprimer cette colonne dans notre tableau. 

for(var i=0 ; i<tableauRebuts.length;i++)
  • for() : déclaration d’une boucle For permettant de parcourir les lignes notre tableau de données 
    • var i=0 : dans Google Apps Script, un tableau démarre à l’index 0. Notre première ligne aura donc l’index 0 
    • i<tableauRebuts.length : ce paramètre représente la condition de sortie de la boucle For. Dans notre cas nous voulons parcourir l’ensemble des lignes du tableau. Pour ce faire nous définissons que l’itérateur i s’arrête à la dernière ligne du tableau c’est à dire celle au dernier index. Or comme les tableaux démarrent à l’index 0, le dernier index est égal aux nombre de lignes moins un. 
      • i : notre itérateur 
      • tableauRebuts.length : renvoie le nombre de ligne du tableau 
      • i<tableauRebuts.length : Nous vérifions que l’itérateur est strictement inférieur au nombre de lignes
    • i++ : notre itérateur sera incrémenté de 1 à chaque itération
tableauRebuts[i].splice(3,1)
  • tableauRebuts[i] : renvoie la ligne à l’index i du tableau
  • splice(3,1)  : cette méthode supprime la colonne 4 de la ligne i  

Renvoi du tableau modifié

return tableauRebuts

return permet de renvoyer une ou plusieurs données depuis une fonction. Ici nous renvoyons notre tableau de données après suppression de la colonne date d’import

Voilà la fonction : 

function recuperationDonnees() 
{  
var REBUTS=SpreadsheetApp.getActive().getSheetByName("LISTE REBUTS") ; 
var tableauRebuts=REBUTS.getRange("A2:F").getValues() ;
for(var i=0 ; i<tableauRebuts.length;i++)
{ 
tableauRebuts[i].splice(3,1) ;
 } 
  return tableauRebuts ;
}

Copie des données du tableau par collaborateur 

La première fonction nous renvoie la liste des rebuts sous la forme d’un tableau. Il faut maintenant répartir les données de ce tableau à chaque collaborateur. 

Application de deux filtres sur le tableau des rebuts 

FIltre sur les rebuts affectés au collaborateur 

Pour débuter, créons une nouvelle fonction : 

function envoiDonneesAuCollaborateur(tableau,nom, id)

Cette fonction permettra d’envoyer les données filtrées dans le Google Sheets du collaborateur. Trois paramètres seront présents : 

  • Le tableau sera notre liste global des rebuts sous forme de tableau 
  • Le nom sera utilisé pour savoir quel collaborateur sera concerné par le filtre et l’envoi des données
  • id contiendra l’identifiant du Google Sheets du collaborateur.Vous trouverez l’identifiant d’un Google Sheet dans son url : 

Olivier aura accès en écriture au Google Sheets de ses collaborateurs

Effectuons le premier filtre :

var tableauPremierFiltre=tableau.filter(x=>x[3]==nom)
  • filter(x=>x[3]==nom) : cette fonction permet d’appliquer un filtre suivant la condition définie en paramètre 
    • x : indique que le filtre est appliqué sur le tableau courant soit tableau
    • => : indique que la condition qui suit doit être vérifiée pour chaque ligne du tableau
    • x[3]==nom : la condition du filtre. Ici nous souhaitons que la valeur à l’index 3 (quatrième colonne correspondant à l’affectation) de la ligne du tableau soit égale à la valeur du paramètre nom. Attention : nous avons supprimé une colonne dans la première fonction, la colonne des affectations a donc été décalée.

Filtre sur les rebuts existants dans le Google Sheets du collaborateur

Nous devons maintenant filtrer sur les données déjà présentes dans le Google Sheets du collaborateur. 

if(tableauPremierFiltre.length>0){
  • tableauPremierFiltre.length : le nombre de lignes de notre tableau tableauPremierFiltre
  • tableauPremierFiltre.length>0 : Condition du i. Si le premier tableau récupéré possède un nombre de lignes supérieur à zéro alors nous passons à la suite du script. Sinon aucune action n’est effectuée.
var gsCollaborateur=SpreadsheetApp.openById(id).getSheetByName("LISTE REBUTS")
  • SpreadsheetApp : la classe permettant d’intervenir sur le fichier Google Sheets.
  • openById(id) : la fonction permettant de récupérer le document Google Sheets du collaborateur par son identifiant, qui est en paramètre de la fonction envoiDonneesAuCollaborateur.Ce document sera placé dans la variable gsCollaborateur
  • getSheetByName("LISTE REBUTS") : permet de récupérer un onglet du Google Sheets avec son nom. Ici nous supposons que l’onglet concerné est intitulé “LISTE REBUTS”.
var donneesCollaborateur=gsCollaborateur.getRange("A:D").getValues().filter(x=>x[0]!="")
  • getRange("A2:D") : renvoie la plage avec les coordonnées écrites comme dans les formules d’un Google Sheets. Dans notre cas nous souhaitons l’ensemble de la liste des rebuts déjà présents dans le Google Sheets du collaborateur. Nous démarrons donc à la ligne 2 de la colonne A pour ne pas avoir les entêtes. Nous allons ensuite jusqu’à la colonne D sans ligne de fin pour être certain  de récupérer toutes les lignes
  • getValues() :cette fonction renvoie un tableau de données. 
  • filter(x=>x[0]!="") : cette fonction permet d’appliquer un filtre suivant la condition définie en paramètre 
    • x : indique que le filtre est appliqué sur le tableau courant
    • => : indique que la condition qui suit doit être vérifiée pour chaque ligne du tableau
    • x[0]!="" : la condition du filtre. Ici nous souhaitons que la valeur à l’index 0 (première  colonne correspondant à l’identifiant du rebut) de la ligne du tableau ne soit pas vide, soit différent d’un caractère vide. Ce filtre permet de ne travailler que sur des données non vides.
  • donneesCollaborateur : tableau des rebuts du collaborateur
var tableauDeuxiemeFiltre=[]
  • Déclaration d’un deuxième tableau vide qui accueillera les données à importer dans le Google Sheets du collaborateur  
for(var i=0; i<tableauPremierFiltre.length;i++){
  • for(var i=0; i<tableauPremierFiltre.length;i++) : déclaration d’une boucle For permettant de parcourir les lignes notre premier tableau de données filtré  
var identifiantRebut=tableauPremierFiltre[i][0]
  • tableauPremierFiltre[i][0] : permet de récupérer la valeur de la première colonne de la ligne i. Cette valeur correspond à l’identifiant de notre rebut de la ligne i
    • Exemple : Dans la ligne suivante, l’identifiant ijBWi18 est situé dans la première colonne, soit à l’index 0
IDTYPE PIECECAUSE REBUT
ijBWi18VILEBREQUINUsinage boulon 889 incorrect
if(donneesCollaborateur.map(x=>x[0]).some(x=>x==identifiantRebut)==false){
  • donneesCollaborateur.map(x=>x[0]) : récupère les données de la première colonne du tableau dans le Google Sheets du collaborateur. Ces données sont alors organisées comme une liste d’identifiants des rebuts
    • map(position) : cette fonction permet d’extraire une seule colonne d‘un tableau 
      • x : indique que l’extraction est appliquée sur le tableau courant
      • => : indique que la position qui suit doit servir pour l’extraction 
      • x[0]  : indique sur quelle colonne l’extraction est effectuée. Ici nous voulons extraire les identifiant se situant à l’index 0 (première colonne). 
  • some(x=>x==identifiantRebut) : cette méthode vérifie que la valeur de identifiantRebut n’est pas contenue dans le tableau à partir duquel est appelé some. Ici ce tableau est liste des d’identifiants des rebuts récupéré avec donneesCollaborateur.map(x=>x[0])
    • some(condition) : cette méthode teste si au moins un élément de la liste répond à la condition. Elle renvoie un booléen indiquant le résultat du test de la condition.
      • x : indique que la condition est appliquée sur le tableau courant
      • => : indique que la condition qui suit doit servir pour le test
      • x==identifiantRebut : teste l’égalité avec identifiantRebut et tous les éléments de la liste. Si au moins un élément vérifie cette condition, true sera renvoyé, false  sinon. Dans notre cas, nous voulons les identifiants non présents, c’est à dire ceux renvoyant  false au test
  • if(condition==false) : Si notre condition est fausse alors, nous pouvons insérer la données dans notre deuxième tableau. Notre condition est fausse si aucun identifiant déjà présente n’est égal à l’identifiant de la ligne i.
tableauDeuxiemeFiltre.push(tableauPremierFiltre[i])
  • tableauDeuxiemeFiltre.push(donnees) : la méthode push ajoute une ligne au tableau. Cette nouvelle ligne contient les données du paramètre donnees
    • tableauPremierFiltre[i] : la ligne i du tableau tableauPremierFiltre est ajoutée dans le deuxième tableau. 

Copie des données filtrées à la suite des données existantes dans le Google Sheets du collaborateur

Nous avons récupéré dans tableauDeuxiemeFiltre :

  • La liste des rebuts filtrés pour un collaborateur 
  • La liste des rebuts n’étant pas déjà listés sur le Google Sheets du collaborateur

Ce tableau contient les données comme suit si l’on a filtré sur le collaborateur Paul par exemple :

lqOn44ARBRE A CAMEErreur marquage N° sériePaul
MVAFS29VILEBREQUINRayurePaul
DtEeP22VILEBREQUINRayurePaul
qOaAm23VILEBREQUINMarquage N° seriePaul
IvxXz51ARBRE A CAMEAccroche 8719 casséePaul
jsQns74ARBRE A CAMERayurePaul
milsy79VILEBREQUINRayurePaul
ncpWx56VILEBREQUINUsinagePaul

Il faut maintenant : 

  • Ajouter un statut “A traiter” aux rebuts dans tableauDeuxiemeFiltre. Ce statut sera visible sur le Google Sheet du collaborateur lors de l’import
  • Insérer ce tableau à la suite des données du Google Sheets du collaborateur

Ajouter un statut “A traiter”

for(var i=0 ; i<tableauDeuxiemeFiltre.length;i++){
tableauDeuxiemeFiltre[i][3]="A traiter"
  • Parcours de notre tableau
  • La colonne à l’index 3 de notre tableau est égale au nom du collaborateur. Nous pouvons donc remplacer cette donnée par le statut à traiter et ceci pour chaque ligne du tableau

Insertion du tableau à la suite du tableau existant

if(tableauDeuxiemeFiltre.length>0){
  • Si le tableau de données contient bien des données alors nous procédons à l’import du tableau, sinon aucune action n’est effectuée.
gsCollaborateur.getRange(gsCollaborateur.getLastRow()+1,1,tableauDeuxiemeFiltre.length,tableauDeuxiemeFiltre[0].length).setValues(tableauDeuxiemeFiltre)
  • gsCollaborateur.getRange(ligne,col,nb_ligne,nb_col) : renvoie la plage avec 
    • En cellule en haut à gauche la cellule définie au coordonnées (ligne,col) 
    • Un nombre de lignes et colonnes données (nb_ligne et nb_col). Dans notre cas nous voulons insérer les données après la dernière ligne où il y a des données dans l’onglet. Pour cela,nous utilisons gsCollaborateur.getLastRow()+1 pour nous placer à la ligne après celle où sont situées les dernières données dans l’onglet.
  • tableauDeuxiemeFiltre.length : renvoie le nombre de lignes
  • tableauDeuxiemeFiltre[0].length : renvoie le nombre de colonnes 
  • setValues(tableauDeuxiemeFiltre) : cette méthode insère les données tableauDeuxiemeFiltre dans la plage de cellules identifiée par getRange().

Voilà la fonction complète : 

function envoiDonneesAuCollaborateur(tableau,nom,id){ 
 
  var tableauPremierFiltre=tableau.filter(x=>x[3]==nom) ;
 
  if(tableauPremierFiltre.length>0){
   
    var gsCollaborateur=SpreadsheetApp.openById(id).getSheetByName("LISTE REBUTS") ;
   
    var donneesCollaborateur=gsCollaborateur.getRange("A:D").getValues().filter(x=>x[0]!="") ;
   
    var tableauDeuxiemeFiltre=[] ;
   
    for(var i=0; i<tableauPremierFiltre.length;i++){

      var identifiantRebut=tableauPremierFiltre[i][0] ;
     
      if(donneesCollaborateur.map(x=>x[0]).some(x=>x==identifiantRebut)==false){

        tableauDeuxiemeFiltre.push(tableauPremierFiltre[i]) ;

      }

    }
   
    for(var i=0 ; i<tableauDeuxiemeFiltre.length;i++){

      tableauDeuxiemeFiltre[i][3]="A traiter" ;

    }
   
    if(tableauDeuxiemeFiltre.length>0){
      gsCollaborateur.getRange(gsCollaborateur.getLastRow()+1,1,tableauDeuxiemeFiltre.length,tableauDeuxiemeFiltre[0].length).setValues(tableauDeuxiemeFiltre) ;

    }

  }

}

Création d’une fonction appelant les deux fonctions précédentes pour chaque collaborateur

Nous avons créé deux fonctions : une pour récupérer le tableau global des rebuts depuis le Google Sheets d’Olivier et une pour insérer un tableau dans le Google Sheets d’un collaborateur.

Nous allons donc construire une troisième fonction appelant envoiDonneesAuCollaborateur et ReceperationDonnees  afin de finaliser notre script :

function EnvoiDonneesCollaborateurs(){

  envoiDonneesAuCollaborateur(recuperationDonnees(),"Julie","zefozefbau88AJE1") ;

  envoiDonneesAuCollaborateur(recuperationDonnees(),"Sophie","iadualoslmcgj61t") ;

  envoiDonneesAuCollaborateur(recuperationDonnees(),"Paul","aej7abdja80062hf") ;

}

La fonction EnvoiDonneesCollaborateurs se décompose alors comme suit : 

  • Appel de la fonction recuperationDonnees qui renvoie le tableau global des rebuts 
  • Ce tableau est alors passé en paramètre. Deux autres paramètres sont ajoutés dans l’appel de la fonction envoiDonneesAuCollaborateur 
    • Le nom du collaborateur 
    • L’identifiant du Google Sheets du collaborateur
  • Réitération de cet appel imbriqué pour chaque collaborateur 

Voici le code complet comprenant les trois fonctions :

function recuperationDonnees() {  

var REBUTS=SpreadsheetApp.getActive().getSheetByName("LISTE REBUTS") ;
 
  var tableauRebuts=REBUTS.getRange("A2:E").getValues() ;  

  for(var i=0 ; i<tableauRebuts.length;i++){
   
    tableauRebuts[i].splice(3,1) ;
   
  }
 
  return tableauRebuts ;
}


function envoiDonneesAuCollaborateur(tableau,nom,id){ 
 
  var tableauPremierFiltre=tableau.filter(x=>x[3]==nom) ;
 
  if(tableauPremierFiltre.length>0){
   
    var gsCollaborateur=SpreadsheetApp.openById(id).getSheetByName("LISTE REBUTS") ;
   
    var donneesCollaborateur=gsCollaborateur.getRange("A:D").getValues().filter(x=>x[0]!="") ;
   
    var tableauDeuxiemeFiltre=[] ;
   
    for(var i=0; i<tableauPremierFiltre.length;i++){

      var identifiantRebut=tableauPremierFiltre[i][0] ;
     
      if(donneesCollaborateur.map(x=>x[0]).some(x=>x==identifiantRebut)==false){

        tableauDeuxiemeFiltre.push(tableauPremierFiltre[i]) ;

      }

    }
   
    for(var i=0 ; i<tableauDeuxiemeFiltre.length;i++){

      tableauDeuxiemeFiltre[i][3]="A traiter" ;

    }
   
    if(tableauDeuxiemeFiltre.length>0){
      gsCollaborateur.getRange(gsCollaborateur.getLastRow()+1,1,tableauDeuxiemeFiltre.length,tableauDeuxiemeFiltre[0].length).setValues(tableauDeuxiemeFiltre) ;

    }

  }

}


function EnvoiDonneesCollaborateurs(){
 
  envoiDonneesAuCollaborateur(recuperationDonnees(),"Julie", "1zFByYXpWF_G_ps0rE") ;
 
  envoiDonneesAuCollaborateur(recuperationDonnees(),"Sophie","9W1epi4j1nku8") ;
 
  envoiDonneesAuCollaborateur(recuperationDonnees(),"Paul","-vbZzq-C2IQ3o")  ;
 
}

Une partie de l’échange d’informations entre Olivier et ses collaborateurs est à présent automatisée.

Un envoi par e-mail des nouveaux rebuts à chaque collaborateur pourrait perfectionner ce script… peut être dans un prochain épisode ! 

Avez-vous un cas d’automatisation auquel Google Apps Script pourrait répondre ? Merci pour vos commentaires.


Vous avez un projet autour de G Suite
et vous souhaitez nous solliciter :

formation | développement | paramétrage G Suite | interventions | …


Si vous avez trouvé une faute d’orthographe, veuillez nous en informer en sélectionnant le texte en question et en appuyant sur Ctrl + Entrée .

LAISSER UN COMMENTAIRE

S'il vous plaît entrez votre commentaire!
S'il vous plaît entrez votre nom ici

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.