javascriptcsvgoogle-sheetsgoogle-apps-script

The csv import script in google sheet does not work with certain csv, why?

This script allows you to import a CSV from an email attachment into a Google Sheet. My resumes, I needed to use it to import CSVs from another collaborator. Unfortunately, the data in the first row isn't retrieved. But it is if I manually open the CSV in Google Sheets and redownload the CSV file and email it back to myself. So I figured the problem was with the CSV and not the script.

If anyone has any idea why and how to fix the problem, I'm a proponent.

Here is the files I use : files

Here is the script I use :

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/*---------------------------------------Nom des feuilles---------------------------------------------------------*/
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

const paper_1 = "CSV brute"
const paper_2 = "paramètre"
const paper_3 = "BDD - Historique Valeur"
const paper_ID = "CSV ID"



////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/*---------------------------------------RECHERCHE CSV - MAIL-----------------------------------------------------*/
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/*cherche le mail avec l'object test2 (message.getSubject() == 'test2') dans le libellés test (requete = "label:test")

label:rondes-poitiers---csv// Label - HUGO
label:rapports-de-ronde-domms//  Label - YAHYA*/

var requete ="{label:rapports-de-ronde-domms}";
function PROJET_pluto_test()
{
  var threads = GmailApp.search(requete);

  //cherche dans les 5 mails les plus récent (n<=5)
  //toutes les 15 mins un nouveau mail est reçu, l'itération permet de ne louper aucun mail
  var object_mail = SpreadsheetApp.getActive().getSheetByName("Feuille 123").getRange("N3").getValue()
  // $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
  for(var n=0;n<=5;n++)
  {
    var message = threads[n].getMessages()[0];
    Logger.log(object_mail + "  =  " + message.getSubject())
    if (message.getSubject() == object_mail)
    {
      //message pour prévenir (dans le google sheet)
      SpreadsheetApp.getActive().toast("📱➡📁 Recherche de données provenant de la tablette.");
      var attachment = message.getAttachments()[0];
      attachment.setContentTypeFromExtension();
      var ss = SpreadsheetApp.getActive();
      //importer les données dans la feuille nommée CSV (sh = ss.getSheetByName("CSV brute2"))
      var sh = ss.getSheetByName(paper_1);
      //stock les données dans la variable nommée dataString
      var dataString = attachment.getDataAsString();
      //applique la mise en forme aux données
      var csvData = CSVToArray(dataString);
      
      //supprime les données et le format de la feuille CSV
      sh.clear({ formatOnly: true, contentsOnly: true });
      
      //place les valeurs dans le google sheet
      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]));
                Logger.log("🔴" +new Array(csvData[i]))
      } 
    }
  }
}


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/*--------------------------------------MISE EN FORME DONNEES-----------------------------------------------------*/
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

// création d'une mise en forme des données pour les placer correctement dans le google sheet.
function CSVToArray( strData, strDelimiter ){ 
  strDelimiter = (strDelimiter || ",");
  var objPattern = new RegExp(
    (
      // Delimiters.
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +


      // Quoted fields.
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +


      // Standard fields.
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );
  // varible stockant les données.
  var arrData = [[]];
  // variable stockant le paterne
  var arrMatches = null;
  while (arrMatches = objPattern.exec( strData )){
                Logger.log(strData)
    // Get the delimiter that was found.
    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 );
}
Solution

  • When I saw your sample CSV files, it seems that the delimiter is different. The working CSV is ,. But. The not working CSV is ;.

    In your function CSVToArray( strData, strDelimiter ), 2nd argument is the delimiter. But, in your situation, it seems that both patterns of , and ; are used. So, how about modifying your script as follows?

    From:

    var csvData = CSVToArray(dataString);

    To:

    var csvData = CSVToArray(dataString.replace(/;/g, ","));

    or

    var csvData = Utilities.parseCsv(dataString.replace(/;/g, ","));