Query + importRange ou Data ?

Le 28/09/2021 à 17:43

Bonjour,

j'ai déjà utilisé la fonction ImportRange avec d'autres feuilles de calculs mais la je me retrouve face a une complexité..

Comment faire pour récupérer seulement quelques informations d'un .CSV stocké sur un drive ?

Pour rappel Mike, nous avions ( enfin tu, car c'est loin de mon niveau ) fait un script pour extraire une multitude de données sur un csv complexe.

ici : https://forum.excel-pratique.com/sheets/connection-avec-drive-et-importation-auto-158775

avec ce script :

const id1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('param').getRange('B6').getValues()
const id2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('param').getRange('C6').getValues()
const f = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('csv');

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('** MENU **')
    .addItem('Lister les fichiers csv', 'listeFichiersUnDossier')
    .addItem('Lire un fichier csv', 'lireCSV')
    .addItem('Compiler les fichiers csv d\'un dossier', 'compilCSV')
    .addToUi();
}

function compilCSV() {
  f.clear();
  SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(f);
  SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(f);
  var dossierId = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('param').getRange('dossier').getValue();
  var folder = DriveApp.getFolderById(dossierId);
  var fichiers = folder.getFiles();
  while (fichiers.hasNext()) {
    var fichier = fichiers.next();
    if (fichier.getMimeType() == 'text/csv'){
      lireCsvFromId(fichier.getId())
    }
  }
}

function lireCSV(){
  f.clear();
  SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(f);
  var fichierId = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('param').getRange('fichier').getValue();
  lireCsvFromId(fichierId)
}

function lireCsvFromId(id) {
  var result = []
  var csv = DriveApp.getFileById(id).getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(csv);
  var num1 = parseInt(id1)
  var num2 = parseInt(id2)
  for (var i = 0; i < csvData.length; i++) {
    //if(idX.indexOf(csvData[i][0])!=-1 || csvData[i][0]=='Investor'){
    if(parseInt(csvData[i][0])==num1 || parseInt(csvData[i][0])==num2 || csvData[i][0]=='Investor'){
      result.push(csvData[i]);
    }
  }
  try{
    result = transpose(result)
    var newResult = []
    for (var i=0;i<result.length;i++){
      var flag = false
      for (var j=1;j<result[0].length;j++){
        if(result[i][j]!=''){flag = true}
      }
      if (flag) {newResult.push(result[i])}
    }
    var n = f.getLastRow()+1;
    f.getRange(n, 1, 1, 1).setValue(id);
    f.getRange(n+1, 2, newResult.length, newResult[0].length).setValues(newResult)
  }catch(e){

  }
  SpreadsheetApp.flush();
}

function listeFichiersUnDossier() {
  var dossier = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('param').getRange('dossier').getValue();
  var feuille = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('liste');
  feuille.clear();
  SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(feuille);
  feuille.appendRow(["nom", "date mise à jour", "URL", "id"]);
  var folder = DriveApp.getFolderById(dossier);
  var data = [];
  var fichiers = folder.getFiles();
  while (fichiers.hasNext()) {
    var fichier = fichiers.next();
    if (fichier.getMimeType() == 'text/csv'){
      data = [ 
        fichier.getName(),
        fichier.getLastUpdated(),
        fichier.getUrl(),
        fichier.getId()
      ];
    feuille.appendRow(data);
    }
  }
}

function transpose(a){
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

j'ai pu adapter la fonction "ListeFichiersUnDossier" facilement car je la comprend, donc pour le moment je peux obtenir les ID et les URL des fichiers présent dans le dossier, je l'ai un peu modifier a ma sauce ^^"

En revanche la fonction lireCsvFromId je n'y arrive pas... ( et c'est le plus important pour reconstruire les données du CSV )

étant donnée que j'ai une faible quantité d'infos a récupérer, je pense qu'un Query + importRange est envisageable ? la seul contrainte est que le fichier est en .CSV donc peut-être pas possible.

j'ai essayé de synthétisé au mieux les données voulut, ainsi que 2 .csv importés dans sheet pour l'exemple ( la structure est identique pour tout les fichiers )

https://docs.google.com/spreadsheets/d/1OiKPcpmPtBoDxuUVWFHAc-LUOlizIsQ12sN8sPl-xEE/edit?usp=sharing

Donc je sais pas ce qui est le mieux, et que je peux comprendre le mieux surtout !

merci !

Le 28/09/2021 à 18:27

Bonjour Alex,

Appliquer un query sur une fonction customisée revient à faire la fonction et dans un autre onglet appliquer le query.

Ou alors on reste en script d'un bout à l'autre, ou alors on applique ...

IMPORTFEED

https://support.google.com/docs/answer/3093337?hl=fr qui peut être encapsulé par un query. Là cela devient intéressant. Et si tu as déjà les ID des fichiers, c'est déjà bien avancé !

Regarde déjà de ton côté car tu as les fichiers csv ... et si difficultés essaie de les décrire avec un fichier test.

Le 28/09/2021 à 18:41

Ou alors on reste en script d'un bout à l'autre, ou alors on applique ...

le but n'etait pas de faire un melange des deux. c'etait plus de comprendre cette partie ci-dessous pour faire la même chose et adapter si c'etait necessaire

function lireCsvFromId(id) {
  var result = []
  var csv = DriveApp.getFileById(id).getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(csv);
  var num1 = parseInt(id1)
  var num2 = parseInt(id2)
  for (var i = 0; i < csvData.length; i++) {
    //if(idX.indexOf(csvData[i][0])!=-1 || csvData[i][0]=='Investor'){
    if(parseInt(csvData[i][0])==num1 || parseInt(csvData[i][0])==num2 || csvData[i][0]=='Investor'){
      result.push(csvData[i]);
    }
  }
  try{
    result = transpose(result)
    var newResult = []
    for (var i=0;i<result.length;i++){
      var flag = false
      for (var j=1;j<result[0].length;j++){
        if(result[i][j]!=''){flag = true}
      }
      if (flag) {newResult.push(result[i])}
    }
    var n = f.getLastRow()+1;
    f.getRange(n, 1, 1, 1).setValue(id);
    f.getRange(n+1, 2, newResult.length, newResult[0].length).setValues(newResult)
  }catch(e){

  }
  SpreadsheetApp.flush();

IMPORTFEED

je connais pas ! je vais regarder et je reviendrais ici, si besoins :)

merci

Le 29/09/2021 à 02:20

Bonjour, un peu d'explication, autant que je m'en souvienne ...

function lireCsvFromId(id) {
  var result = []

// on charge le fichier identifié par son ID
  var csv = DriveApp.getFileById(id).getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(csv);

// je ne sais plus de où cela vient, il doit y avoir une définition plus haut dans le script alors
  var num1 = parseInt(id1)
  var num2 = parseInt(id2)
  for (var i = 0; i < csvData.length; i++) {
// si dans la première colonne on trouve l'u des 2 numéros ou le texte Investor, alors on retient la ligne en la mettant dans result
    if(parseInt(csvData[i][0])==num1 || parseInt(csvData[i][0])==num2 || csvData[i][0]=='Investor'){
      result.push(csvData[i]);
    }
  }

  try{

// on tranpose la matrice reconstituée
    result = transpose(result)
    var newResult = []

// pour chaque ligne (donc en fait dans chaque colonne de la matrice directement issue du csv) on regarde s'il y a une valeur
    for (var i=0;i<result.length;i++){
      var flag = false
      for (var j=1;j<result[0].length;j++){
// s'il y a en effet une valeur, alors on mettra toute la "ligne" dans une nouvelle matrice, c'est pourquoi on fixe au départ à false et dès qu'on trouve une valeur non vide on met le flag à true
        if(result[i][j]!=''){flag = true}
      }
      if (flag) {newResult.push(result[i])}
    }
    var n = f.getLastRow()+1;
    f.getRange(n, 1, 1, 1).setValue(id);

// j'avoue que je suis un peu étonné que la nouvelle matrice ne soit pas transposée car c'était l'image réduite de result transposée ... à voir
    f.getRange(n+1, 2, newResult.length, newResult[0].length).setValues(newResult)
  }catch(e){

  }
  SpreadsheetApp.flush();
Le 29/09/2021 à 11:45

Merci pour ces explications ! je commence a comprendre,

// j'avoue que je suis un peu étonné que la nouvelle matrice ne soit pas transposée car c'était l'image réduite de result transposée ... à voir

effectivement il y avais bien une fonction transpose, je ne l'avais pas mise car finalement elle ne concernait pas le "ciblage" des données a extraire.

alors on retient la ligne en la mettant dans result

on peux choisir les colonnes plutôt ?

Voici un début de résultat

image

je veux être sur de pouvoir récupérer les infos, et après je verrais la mise en forme ( si jamais je peux faire des en-tête et récupérer les infos sous chaque colonnes )

la seul chose que ne sais pas comment récupérer c'est celle-ci. pas de mots clés comme "investor", "From", etc...

image

Merci !

( ps : je ne remets pas le script, il est dispo dans le sheet que j'ai partagé )

Le 29/09/2021 à 13:07
IsAlex a écrit :

alors on retient la ligne en la mettant dans result

on peux choisir les colonnes plutôt ?

oui bien sûr, selon quel critère ?

probablement quelque chose comme ceci pour les colonnes 1, 4 et 5

result.push([csvData[i][0],csvData[i][3],csvData[i][4]]);
Le 29/09/2021 à 13:19

Pour récupérer la ligne, il faudrait tester s'il s'agit d'une date en faisant

(csvData[i][0] instanceof Date)
if(parseInt(csvData[i][0])==num1 || parseInt(csvData[i][0])==num2 || csvData[i][0]=='Investor' || (csvData[i][0] instanceof Date) )

néanmoins cela ne fonctionne pas probablement qu'il s'agit en fait de texte ! je regarde ..

Le 29/09/2021 à 13:26

essaie aussi

if(parseInt(csvData[i][0])==num1 || parseInt(csvData[i][0])==num2 || csvData[i][0]=='Investor' || (i==1) )

mais cela perturbe peut-être le reste, comme je n'ai plus le fil conducteur en tête !

Le 29/09/2021 à 14:03

mais cela perturbe peut-être le reste, comme je n'ai plus le fil conducteur en tête !

ok je regarde tout ça dans l'après-midi. Merci

( le script est fonctionnel sur le Google sheet que j'avais mis plus haut, les adresses et l'ID du drive sont dessus )

Le re-voici

https://docs.google.com/spreadsheets/d/1OiKPcpmPtBoDxuUVWFHAc-LUOlizIsQ12sN8sPl-xEE/edit?usp=sharing

Le 29/09/2021 à 15:14

Je ne me suis pas encore occupé de la date, mais je commence a avoir un résultat qui me convient !

je n'arrive pas a enlever cette 1ere ligne avec le nom des en-tetes !

image

je sais que ça doit etre un +1 tout bete a rajouter quelque part mais.. ^^"

PS :

// si dans la première colonne on trouve l'u des 2 numéros ou le texte Investor, alors on retient la ligne en la mettant dans result

j'ai essayé de seulement garder :

if(parseInt(csvData[i][0])==num1 || parseInt(csvData[i][0])==num2)

mais ça ne me trouve rien de tout, etrange

j'ai même essayé de les remettre en entier directement

Le 29/09/2021 à 15:46

et comme ceci

if(parseInt(csvData[i][0])==num1 || parseInt(csvData[i][0])==num2 || csvData[i][0]=='From')
Le 29/09/2021 à 15:49

et comme ceci

Comme ça c'est ok ! je vais m'attaquer a la date maintenant, voir si je peux pas la mettre dans une autre colonne :) merci !

c'est etonnant que je sois obliger de rajouter le csvData[i][0]=='From' pour trouver les adresses, ou alors j'ai fait une faute de frappe toute a l'heure

Le 29/09/2021 à 16:07

Pour avoir la date, j'ai leurré le système

var flagDate=false
  for (var i = 0; i < csvData.length; i++) {
    //if(idX.indexOf(csvData[i][0])!=-1 || csvData[i][0]=='Investor'){
    if(flagDate){
      result.push([csvData[i][0],'_','_','_']);
      flagDate=!flagDate
    }
    if (csvData[i][0]=='From') {flagDate=true}
    if(parseInt(csvData[i][0])==num1 || parseInt(csvData[i][0])==num2){ 
      result.push([csvData[i][0],csvData[i][1],csvData[i][7],csvData[i][8]]);
    }
  }

le flagDate se met à true quand il rencontre from, et du coup, la ligne suivante est prise en compte

mais j'ai ajouté fictivement _ dans les cellules adjacentes sinon le traitement suivant ne rencontrant rien supprimait cette ligne

et du coup on n'a plus besoin de from sauf pour initialiser flagDate

Le 29/09/2021 à 17:06

je t'ai vu venir faire un tour :D

bon j'ai récupérer ce qui me manquait au final.

le top du top serait que l'adresse soit sur une sur la colonne E pour que ça fasse qu'une ligne mais je pense que je ne pourrais pas tout avoir étant donnée que c'est du au "transpose(result) que j'ai remis.

je continuerai a regarder plus tard

bonne fin de journée et merci

image
Le 02/10/2021 à 12:23

Bonjour !

Bon.. je suis pas si mal !

const id1 = "adresse1"
const id2 = "adresse2"
const f = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');

function compilCSV() {
  f.clear();
  SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(f);
  var dossierId = "id du dossier";
  var folder = DriveApp.getFolderById(dossierId);
  var fichiers = folder.getFiles();
  while (fichiers.hasNext()) {
    var fichier = fichiers.next();
    if (fichier.getMimeType() == 'text/csv'){
      lireCsvFromId(fichier.getId())
    }
  }
}

function lireCsvFromId(id) {
  var result = []
  var csv = DriveApp.getFileById(id).getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(csv);
  var num1 = parseInt(id1)
  var num2 = parseInt(id2)
  for (var i = 0; i < csvData.length; i++) {
    if(parseInt(csvData[i][0])==num1 || parseInt(csvData[i][0])==num2 || (i==0) || (i==1) ){ 
      result.push([csvData[i][0]], [csvData[i][1]], [csvData[i][7], csvData[i][8]]);
    }
  }

  try{
    result = transpose(result)
    var newResult = []
    for (var i=0;i<result.length;i++){
      var flag = false
      for (var j=1;j<result[0].length;j++){
        if(result[i][j]!=''){flag = true}
      }
      if (flag) {newResult.push(result[i])}
    }
    var n = f.getLastRow()+1;
    f.getRange(n, 1, 1, 1).setValue(id);
    f.getRange(n, 2, newResult.length, newResult[0].length).setValues(newResult)
  }catch(e){

  }
  SpreadsheetApp.flush();

  f.getRange('E:E').activate();
  f.getActiveRangeList().setNumberFormat('dd.MM.yyyy');
}
function transpose(a){
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}
image

les colonnes B,D,F,G serait superflue mais j'ai pas trouvé de solutions pour les supprimer sans sacrifier autre chose..

le liens de l'autre google sheet est erroné, j'ai eu un petit bug ^^" voici le nouveau :

https://docs.google.com/spreadsheets/d/1n-l3T7YZrndi-R1rB191ZN_75ZTwOivdnVioGY5c2mA/edit?usp=sharing

Rechercher des sujets similaires à "query importrange data"