Google Apps Script : importer automatiquement un CSV depuis une pièce jointe de Gmail vers un Google Sheets

Vous recevez tous les jours un fichier CSV en pièce jointe de la part d’un de vos collègues. Ce fichier contient des données que vous souhaitez importer dans un Google Sheets. Et bien pourquoi ne pas automatiser cette tâche chronophage avec un peu de code ? 

Google Apps Script est là pour vous ! 

---

Avant toute chose posons un contexte : 

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. Ces derniers sont répertoriés par le chef de production dans un logiciel. Le chef de production, appelons-le Ghislain, extrait quotidiennement ces données du logiciel sous format CSV (Comma Separator Value : fichier de données séparées par une virgule) et envoie tous les jours à 9h00 un fichier en pièce jointe d’un mail à Olivier. 

Olivier doit donc manuellement :

Beaucoup d’étapes pour un simple transfert de données !

Le contexte est posé, voyons comment automatiser tout ceci avec Google Apps Script, la plateforme de développement de Google  :

Fonction de récupération dans un tableau Sheets des données d’une pièce jointe de type CSV située dans le dernier email d’un destinataire donné

Tout d’abord créons un script à partir de notre Google Sheets.

Voir cet article sur la mise en place d’un script

Ensuite créons notre première fonction :

function RECUP_CSV_PIECE_JOINTE(mail)

Notre fonction utilise le paramètre mail qui permettra d’indiquer l’adresse mail à partir de laquelle nous voulons trier les messages pour récupérer notre fichier CSV. Nous appellerons la fonction en mettant l’adresse mail de Ghislain dans le paramètre.

Trouver le bon message

Il faut retrouver le message contenant le fichier CSV en pièce jointe en provenance de notre collègue Ghislain.

Pour cela, nous allons donc utiliser la classe GmailApp. 

GmailApp permet :

• de chercher des fils de discussion dans Gmail ;
• de récupérer des listes de messages suivant des critères de recherches ;
• d’extraire des données en pièce jointe de messages.

Un fil de discussion est composé de messages ayant le même objet. Ces messages eux-même peuvent contenir des pièces jointes.

Exemple : Dans votre fil de discussion intitulé GESTION REBUTS comme ci dessous, vous trouverez donc des messages. Le mail du 20 juillet sera le premier dans la liste des messages et celui du jour même à 8h01 sera le dernier.

var fil_email=GmailApp.search('from:'+mail +' filename:csv' ,0,1)[0]

• La variable fil_email va contenir le dernier fil de discussion en provenance de l’adresse mail contenue dans la variable mail. Depuis la classe GmailApp, la fonction search, qui renvoie un tableau d’objets de type fil de discussion, utilise les paramètres suivants :
  •  from: indique l’adresse mail de l’expéditeur du message (ce sera celle de Ghislain dans notre exemple) 
  • filename:csv : recherche une pièce jointe au format CSV
    ATTENTION : laissez un espace entre from et filename
  • 0 :  position de départ pour la recherche dans la liste des fils de discussion. Ici nous démarrons à 0 dans la liste de notre boîte de réception pour récupérer le plus récent
  • 1 : nombre de fil de discussion à récupérer. Nous souhaitons retrouver le dernier fil de discussion uniquement, il nous faut donc indiquer 1 dans le nombre de fils de discussion à récupérer
• [0] permet récupérer le premier élément (index 0) dans le tableau renvoyé par la fonction search. 

var dernier_email= fil_email.getMessages()[fil_email.getMessages().length-1]

La variable dernier_email récupère le dernier message du fil de discussion sélectionné, soit le message le plus récent. Pour accéder au tableau des messages du fil, nous utilisons la fonction getMessages() à partir de la variable fil_email. Puis nous accédons au dernier élément (c’est à dire le dernier index) de ce tableau avec fil_email.getMessages().length-1

Récupérer la pièce jointe du dernier message

Utilisons la classe GmailAttachment afin de pouvoir récupérer la pièce jointe. 

var piece_jointe=dernier_email.getAttachments()[0]

piece_jointe récupère la première pièce jointe (à l’index 0) de la liste des pièces jointes du dernier message grâce à la fonction getAttachments(). Cette fonction renvoie un tableau d’objet de type pièce jointe (GmailAttachment).

Transformer le CSV en texte lisible par Sheets

var txt=piece_jointe.getDataAsString().replace(/"/g, '')

• Avec la fonction getDataAsString(), nous récupérons la pièce jointe sous format texte dans la variable txt.
• replace(/"/g, '') permet de supprimer les guillemets doubles pour une mise en forme plus lisible du CSV.

var csvData = Utilities.parseCsv(txt,",")

• On effectue la séparation du CSV avec la fonction parseCsv(texte, séparateur) de la classe Utilities. Cette séparation nous renvoie un tableau de données.
• return csvData renvoie les données du fichier CSV ! 

Et voilà la fonction globale : 

function RECUP_CSV_PIECE_JOINTE(email){
  // Recherche les messages venant de l’adresse “email” et qui ont une pièce jointe au format CSV
  var fil_email=GmailApp.search('from:'+mail +' filename:csv' ,0,1)[0]
// Prends le dernier message
  var dernier_email= fil_email.getMessages()[fil_mail.getMessages().length-1]
  //Recupère la première pièce jointe
  var piece_jointe=dernier_email.getAttachments()[0]
  
//Mise en forme des données et transformation en texte
  var txt=piece_jointe.getDataAsString().replace(/"/g, '')

//Séparation des données pour renvoyer un tableau
  var csvData = Utilities.parseCsv(txt,",")

//Renvoie le tableau de données
  return csvData
}

Fonction d’insertion des données dans un Google Sheets

Dans la première fonction, nous avons réussi à récupérer un tableau de données à partir des informations du fichier CSV. Ces données doivent donc maintenant être ajoutées dans un Google Sheets. 

function IMPORT_DATA(csvData)

L’entête contient le paramètre csvData qui sera notre tableau de données récupéré dans la fonction précédente. 

Recherche de la feuille Sheets

var REBUTS=SpreadsheetApp.getActive().getSheetByName("GESTION REBUTS")

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

Insertion des données dans la feuille

if(csvData.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.

REBUTS.getRange(REBUTS.getLastRow()+1, 2, csvData.length, csvData[0].length).setValues(csvData)

• REBUTS.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 REBUTS.getLastRow()+1 pour nous placer à la ligne après celle où sont situés les dernières données dans l’onglet.
• csvData.length : renvoie le nombre de lignes
• csvData[0].length : renvoie le nombre de colonnes 
• setValues(csvData) : va insérer les données csvData dans la plage de cellules identifiée par getRange().

Voici le résultat final :

function IMPORT_DATA(csvData){
 //Recupère l’onglet GESTION REBUTS
 var REBUTS=SpreadsheetApp.getActive().getSheetByName("GESTION REBUTS")
 //Vérification que le tableau possède des données
 if(csvData.length>0){
 //Insertion des données
    REBUTS.getRange(REBUTS.getLastRow()+1, 2, csvData.length, csvData[0].length).setValues(csvData)
    Logger.log("Import terminé")
 }

 else{
    Logger.log("Aucune données dans le csv")
 }
}

Appel des deux fonctions précédentes dans une seule et même fonction MAIN()

Nous avons créé deux fonctions : une pour renvoyer un tableau à partir du fichier CSV en pièce jointe d’un mail et une pour insérer un tableau dans un Google Sheets.

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

function MAIN(){
  //L'email de Ghislain, le chef de production
  var mail="ghislain.sanjuan@sango-co.com"
  //L’appel des deux fonctions
  IMPORT_AVEC_FILTRE_ID(RECUP_CSV_PIECE_JOINTE(mail))
}

La fonction MAIN() s’explique alors comme suit :

var mail="ghislain.sanjuan@sango-co.com"

L’adresse mail de l’émetteur est renseignée dans la variable mail. Ici cette variable prendra la valeur du mail de Ghislain.

IMPORT_AVEC_FILTRE_ID(RECUP_CSV_PIECE_JOINTE(mail))

L’appel des deux fonctions de manière imbriquée permet de rassembler les lignes de code. 

• RECUP_CSV_PIECE_JOINTE est appelée en premier pour renvoyer le tableau de données à partir du CSV. 
• Ce tableau est alors passé en paramètre de la fonction IMPORT_AVEC_FILTRE_ID

Maintenant, nous allons utiliser un déclencheur pour planifier l’appel de la fonction MAIN().

Planifier l’appel de la fonction MAIN()

Dans le cas de cette article, nous supposons que le mail est reçu quotidiennement. il est donc envisageable de déclencher la fonction MAIN à la même fréquence. Nous créons donc un déclencheur de type horaire :

• Accédez au déclencheur du script 

• En bas à droite cliquez sur “Ajouter un déclencheur” : 

• Définissez les paramètres comme suit en se basant sur le fait que le mail est reçu avant 10h :

• Cliquez sur Enregistrer en bas à droite :

Si jamais un erreur survient lors de l’exécution de la fonction MAIN(), le déclencheur vous enverra une notification quotidienne. Si vous le souhaitez vous pouvez également changer les paramètres de notification d’échec à partir de la liste ci dessous 

Enfin voici le code complet comprenant les trois fonctions :

function RECUP_CSV_PIECE_JOINTE(mail){
  // Recherche les messages venant de l’adresse “email” et qui ont une piece jointe au format CSV
  var fil_email=GmailApp.search('from:'+email +' filename:csv' ,0,1)[0]
// Prends le dernier message
  var dernier_email= fil_email.getMessage()[fil_mail.getMessages().length-1]
  //Recupère la première pièce jointe
  var piece_jointe=dernier_email.getAttachments()[0]
  
//Mise en forme des données et transformation en texte
  var txt=piece_jointe.getDataAsString().replace(/"/g, '')

//Séparation des données pour renvoyer un tableau
  var csvData = Utilities.parseCsv(txt,",")

//Renvoie le tableau de données
  return csvData
}



function IMPORT_DATA(csvData){
 //Récupère l’onglet GESTION REBUTS
 var REBUTS=SpreadsheetApp.getActive().getSheetByName("GESTION REBUTS")
 //Vérification que le tableau possède des données
 if(csvData.length>0){
 //Insertion des données
    REBUTS.getRange(REBUTS.getLastRow()+1, 2, csvData.length, csvData[0].length).setValues(csvData)
    Logger.log("Import terminé")
 }

 else{
    Logger.log("Aucune données dans le csv")
 }
}


function MAIN(){ 
//L'email de Ghislain, le chef de production
  var mail="ghislain.sanjuan@sango-co.com"
  //L’appel des deux fonctions
  IMPORT_DATA(RECUP_CSV_PIECE_JOINTE(mail))
}

La tâche chronophage et source d’erreurs qu’effectuait Olivier a disparu !

Ce script pourrait être amélioré en informant par mail Olivier qu’un nouveau fichier CSV a été ajouté et combien de rebuts y sont contenus… mais c’est une autre histoire !

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

---