CSV to Gmail to Sheet

Hello à tous,

J'ai trouvé de-ci de-là des modop pour créer sous AppsScript une requête qui permet, suite à la réception d'un mail contenant une PJ format CSV, de créer un workflow qui dit : je récupère le dernier message d'un thread avec un CSV en PJ, et je mets à jour un Gsheet qui est sur mon drive.

Voici le script avec une erreur déjà ligne 2

function importCSVFromGmail() { 
  var threads = GmailApp.getUserLabelByName('Dashboard Updates').getThreads(0,1);     
  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];

  if (attachment.getContentType() === "text/csv") {                            
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheetByName("GESTION_REBUTS");
    var dataString = attachment.getDataAsString();
    var csvData = CSVToArray(dataString);

    sh.clearContents().clearFormats();                                         
    var lastRowValue = sh.getLastRow();
    for (var i = 0; i < csvData.length; i++) {
       sh.getRange(i+lastRowValue+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    } 
  }
  message.markRead();                                                          
  message.unstar();                                                            
}

function CSVToArray( strData, strDelimiter ){ 
  strDelimiter = (strDelimiter || ",");
  var objPattern = new RegExp(
    (
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );

  var arrData = [[]];
   var arrMatches = null;
   while (arrMatches = objPattern.exec( strData )){
    var strMatchedDelimiter = arrMatches[ 1 ];
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
    ){
      arrData.push( [] );
    }
    if (arrMatches[ 2 ]){
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );
    } else {
      var strMatchedValue = arrMatches[ 3 ];
    }
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }
  return( arrData );
}

Si quelqu'un a une idée ?

Bonjour,

L'erreur signifie a priori qu'il n'a pas trouvé ce "label"

GmailApp.getUserLabelByName('Dashboard Updates')

Est-ce que

Dashboard Updates

fait bien partie des labels/libellés de ton gmail ?

Pour vérifier, tu peux leschecker comme suit :

function listOfLabels() {
  var labels = GmailApp.getUserLabels();
  var tableau = []
  for (var i = 0; i < labels.length; i++) {
    tableau.push([labels[i].getName()]);
  }
  console.log(tableau)
}

Yes, j'ai essayé à nouveau en refaisant un label propre, nouveau mail, et là ça passe à présent . Bon le souci c'est...qu'il ne se passe rien. ça exécute et ça me dit terminé mais mon Ghseet reste vide. Pas d'update à constater.

es-tu sûr du type mime du fichier ? car parfois tu as application//xlsx

pour mettre au point, il vaut mieux y aller phase par phase et regarder ce qu'il y a en pièces jointes

function testImportCSVFromGmail() { 
  var threads = GmailApp.getUserLabelByName('Dashboard Updates').getThreads();     
  var message = threads[0].getMessages()[0];
console.log (message.getAttachments().length)
  var attachment = message.getAttachments()[0];
console.log (attachment.getContentType())                                                            
}

Le MIME était inconnu (octet-stream). J'ai modifié le mail - je me suis joins la PJ par l'envoi d'un nouveau mail (je regarderai ce qui bloque plus tard pour que la PJ ne soit pas reconnue comme ça). ça passe, mais du coup next step bloquée sur le sheetname. Pour info le Gsheet est stocké sur mon drive (et comme je n'ai pas compris si c'était le sheet ou la feuille que j'appelai, ils portent le même nom)

Si le script n'est pas "dans le fichier", enfin je veux dire attaché au fichier, il faut alors l'appeler en utilisant son id

var ss = SpreadsheetApp.openById('SPREADSHEET_ID')

on peut aussi moyennant une fonction, l'appeler par le nom, mais il faut savoir que dans Google Sheet, l'identifiant est l'ID, et plusieurs fichiers pourraient avoir le même nom

var ss = SpreadsheetApp.openById(getFileId('nom du fichier'))

en ajoutant cette fonction

function getFileId(nomDuFicherRecherche) {
  var files = DriveApp.getFilesByName(nomDuFicherRecherche);
  while (files.hasNext()) {
    var file = files.next();
    return (file.getId())
  }
}

Le fichier et la feuille peuvent avoir le même nom, il faut juste les appeler comme ceci éventuellement

  var ss = SpreadsheetApp.openById(getFileId('nom du fichier'))
  var sh = ss.getSheetByName('nom de la feuille')

Nickel.

J'ai gardé l'option Sheet ID + nom de la feuille. ça marche bien sauf sur 2 points :

• Le premier le fameux MIME non reconnu. En gros en étape 1 de l'extérieur je schedule une requête à partir d'un outil à qui je dis : send mail (adresse) de tel requête, à tel format. Et ça part dans ma messagerie. Mais force est de constater que la PJ n'est pas considérée au bon format. Pour le test j'ai enregistré en local la PJ reçu et je me suis renvoyé à la mano le mail. Et la ça marche, c'est bien un csv qu'il comprend...

• Le second est que mon CSV fait d'origine 9-10Mo, environ 90K lignes. or là seulement 8-9000 lignes sont présentes in fine dans le script (le script fini par me dire temps dépassé). De plus l'ajout dans le Gsheet, que je peux observer en temps réel en ouvrant le fichier, implémente ligne par ligne (trop long...) en y insérant de lignes vide qui plus est. Du coup je n'ai que 10% grossomodo des données qui sont là.

Dans le premier cas, plutôt que de tester selon le mime, teste selon le nom du fichier, ou a minima l'extension .csv avant de parser.

Sinon, il faut revoir le code qui exporte le fichier csv.

Pour ce qui est du second soucis, ceci n'est pas bon du tout, je ne sais pas de où cela vient mais c'est très chronophage !

    for (var i = 0; i < csvData.length; i++) {
       sh.getRange(i+lastRowValue+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    } 

voici ce qui est normalement suffisant ... à adapter à ton cas !

//importer fichier csv attaché à un mail
//--------------------------------------
function myfunk() {
  let ts = GmailApp.getInboxThreads();
  ts.forEach(t => {
    let msgs = t.getMessages();
    msgs.forEach(m => {
      if(m.getAttachments().length > 0) {
        let csv = m.getAttachments()[0].getDataAsString();
        const ss = SpreadsheetApp.getActive();
        const sh = ss.getSheetByName('Sheet1');
        const vs = Utilities.parseCsv(csv);
        sh.getRange(1,1,vs.length,vs[0].length).setValues(vs);
      }
    }) 
  })
}

reste à voir quel délimiteur est utilisé, mais là aussi on peut le faire de façon globale

reste aussi à voir de com bien de colonne tu as besoin, il faut sans doute limiter le nombre de colonne de ta feuille afin de disposer de plus de lignes

il faudrait presque que tu puisses m'envoyer un fichier csv exemple si tu veux aller plus loin

Oui je vois ce que tu veux dire. Je vais essayer d'être clair / synthétique.

En 1. la vue du Gmail (classique), un filtre dans gmail pour balancer le mail dans le bon libellé (idéalement il faudra que j'écrase les mails déjà traités automatiquement pour ne pas les stocker (sauvons les serveurs et la planète).

En 2. Le fichier CSV ci-joint (il faut imaginer 80K lignes du même type).

L'objectif : Mettre un jour la première feuille de mon sheet (qui lui même sera interrogé par la suite) que l'on pourrait appeler par son ID.

Benjamin G67 a écrit :

En 1. la vue du Gmail (classique), un filtre dans gmail pour balancer le mail dans le bon libellé (idéalement il faudra que j'écrase les mails déjà traités automatiquement pour ne pas les stocker (sauvons les serveurs et la planète).

En 2. Le fichier CSV ci-joint (il faut imaginer 80K lignes du même type).

pour le 1. bien sûr, j'ai exhumé un script de ma bibliothèque, mais il faudra puiser dans les libellés, voire même prendre les non-lus, genre

  var requete ="is:unread {label:Test1 label:Test2}"
  var threads = GmailApp.search(requete);

et tu peux épurer quotidiennement tes mails en automatique (ce que je fais aussi par ailleurs avec une fréquence hebdo)

je regarderai demain pour parser le fichier csv globalement et non ligne à ligne

J'ai mis ce fichier sur le drive (j'ai renommé c'est plus simple)

J'ai appliqué ceci, c'est hyper-speed

function importCSVFromGoogleDrive() {
  var file = DriveApp.getFilesByName("forum.csv").next();
  var csvString = file.getBlob().getDataAsString()
  csvString = csvString.replace(/,/g, ".")
  csvString = csvString.replace(/;/g, ",")
  var csvData = Utilities.parseCsv(csvString);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CSV GMAIL');
  sheet.clear();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

je vais me l'envoyer par mail, puis faire le même traitement en partant de gmail demain

Ah yes top. Je viens de tester en remplaçant juste la déclaration de la variable pour remettre un appel par ID et ça marche très bien. Le fichier originel fait en fait 215K lignes ). Il passe à 200K lignes (6minutes) de façon très rapide , je pense que je vais m'en contenter. Est-ce le fait de l'appeler depuis le Gdrive qui facilite le parse ? Affaire à suivre

Benjamin G67 a écrit :

Est-ce le fait de l'appeler depuis le Gdrive qui facilite le parse ? Affaire à suivre

Pas du tout. C'est juste que je n'utilise pas cette boucle chronophage

    for (var i = 0; i < csvData.length; i++) {
       sh.getRange(i+lastRowValue+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    } 

faire autant de

setValues

ne peut que ralentir fortement.

Quant à utiliser function CSVToArray( strData, strDelimiter ) que j'vais déjà vue sur github, je pense qu'il faut que les programmeurs arrêtent de fumer la moquette ! Il faut faire simple !

Je peux récupérer les premières lignes de la première fonction pour taper le importCSVFromGmail plutôt que drive ?

je pense qu'il faut que les programmeurs arrêtent de fumer la moquette ! Il faut faire simple !

Benjamin G67 a écrit :

Je peux récupérer les premières lignes de la première fonction pour taper le importCSVFromGmail plutôt que drive ?

a priori oui, mais il faut que je teste comment "raccorder" les 2 bouts peut-être, à moins que tu y arrives, ce n'est pas forcément très complexe je présume

je vais m'envoyer le fichier !

Voici mon test ... nickel !

Mon libellé est ici done et ma feuille CSV GMAIL

function myfunk() {
  var requete = "is:unread {label:done}" // à adapter
  var threads = GmailApp.search(requete);
  threads.forEach(t => {
    let msgs = t.getMessages();
    msgs.forEach(m => {
      if (m.getAttachments().length > 0) {
        let csvString = m.getAttachments()[0].getDataAsString();
        csvString = csvString.replace(/,/g, ".")
        csvString = csvString.replace(/;/g, ",")
        var csvData = Utilities.parseCsv(csvString);
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CSV GMAIL'); // à adapter
        sheet.clear();
        sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
      }
    })
  })
}

Et le drame arriva

Il te faut data studio ! https://support.google.com/datastudio/answer/6370353?hl=fr#zippy=%2Cau-sommaire-de-cet-article

mais que je ne connais pas

Sinon, avant de copier le fichier, y a t'il moyen de supprimer des colonnes ?

ou alors il faut essayer de couper en 2 et mettre sur 2 fichiers ... en fonction de quel critère ?

Désolé pour le délai de réponse et merci pour le retour...En fait j'ai la même erreur, mais avec un fichier short en ligne. Pourtant même localisation qu'avant, même format CSV. Je n'ai pas compris le rapport avec DS.