Classer des données issues de script dans un tableau qui se complète auto

Bonjour tout le monde,

un petit moment que j'avais pas posté de sujet "complexe" alors attention…

Voici l'état des lieux actuel :

je traite des mails pour récupérer certains éléments ( date / ID google d'un fichier de données .CSV / Période / Montant ) avec ce script :

function realt_rent() {

  var requete ="is:unread label:realT/Rents"
  var threads = GmailApp.search(requete);
  var classeurRent = SpreadsheetApp.getActiveSpreadsheet();
  var feuilleRent = classeurRent.getSheetByName('modele');
  var cellRent = feuilleRent.getRange('A2:E');
  var cellformatRent = feuilleRent.getRangeList(['A2:A','C2:C','D2:D'])

  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    var output =[]
      for (var j = 0; j < messages.length; j++) {
        var msg = messages[j].getPlainBody();
        var sub = messages[j].getSubject();
        var dat = messages[j].getDate();
        var regExp = msg.match(/[-\w]{25,}/);
        var regExp1 = sub.match(/([0-9]{4}.[0-9]{1,2}.[0-9]{1,2})/ig)[0];
        var regExp2 = sub.match(/([0-9]{4}.[0-9]{1,2}.[0-9]{1,2})/ig)[1];
        var regExp3 = msg.match(/(?<=Total Earned: |Total perçu :|Grand Total )(.*)(?= USDC De| USDC From)/ig);
        var ligne = getLastDataRowrealtrent(feuilleRent)+1 

      output.push([dat, regExp, regExp1, regExp2, regExp3]);
      }
    if (output.length > 0) {
      feuilleRent.getRange(ligne, 1, output.length, output[0].length).setValues(output)
      cellRent.sort({column: 1, ascending: false});
      cellformatRent.setNumberFormat('dd.mm.yyyy');
    }
  }
  GmailApp.markThreadsRead(threads);
}

 function getLastDataRowrealtrent(sheet,col) {
    var lastRow = sheet.getLastRow();
    var range = sheet.getRange("B" + lastRow);
    if (range.getValue() !== "") {
      return lastRow;
    } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
    }
  }

résultat :

Ensuite grâce à l'ID du .CSV, je récupère les données pour avoir le détail avec ce script : (j'ai inclus le fait d'avoir plusieurs adresses )

const id1Csv= "0x0236aB1DC08445DcDd383db7a3f13E4B3FAF6890"
const id2Csv= "0x00B591BC2b682a0B30dd72Bac9406BfA13e5d3cd"
const feuilleCsv = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("modele");
const plageCsv = SpreadsheetApp.getActiveSpreadsheet().getRange('G2:I')

function csv(){
  plageCsv.clear();
  SpreadsheetApp.getActiveSpreadsheet().getRange('G2:I');
  var fichierId = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('modele').getRange('B2').getValue();
  lireCsvRentFromId(fichierId)
}

function lireCsvRentFromId(id) {
  var result = []
  var csv = DriveApp.getFileById(id).getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(csv);
  var num1 = parseInt(id1Csv)
  var num2 = parseInt(id2Csv)
  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'){
      if (csvData[i][0]=='Investor'){
        for (var ii=0;ii<csvData[i].length;ii++){
          csvData[i][ii]=csvData[i][ii]
        }
      }
      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]!='' && result[i][j]!=0){flag = true}
      }
      if (flag) {newResult.push(result[i])}
    }
    feuilleCsv.getRange(2, 7, newResult.length, newResult[0].length).setValues(newResult)
  }catch(e){
  }
  SpreadsheetApp.flush();
}

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

un exemple de .CSV traité

et le résultat de mon script ressemble à ça :

l'objectif serait directement d'avoir un tableau évolutif comme cet exemple manuel que j'ai fait :

Je pense que vous voyez déjà ce que j'aimerai avoir.

En colonne A :

les adresses ( avec des nouveautés au fils du temps donc des nouvelles lignes )

Pour les colonnes suivantes : l'extraction des informations des .CSV que je reçois chaque semaine + les elements du mail

et si possible pouvoir additionner les valeurs si j'ai plusieurs adresses..

Est-ce possible ? je ne sais pas si il faut repartir de zéro ou travailler a partir de ce que j'ai déjà ( dans la mesure ou j'aimerai ne pas surcharger mon documents avec des tableaux "tampons".. )

Merci beaucoup !

Edit : voici un google sheet avec les 2 scripts ( le script liée au mail est forcement non fonctionnel ) & la liste de .CSV contenant le detail des Rents pour avoir un peu de matiere ainsi que 2 adresses au hasard pour faire des test https://docs.google.com/spreadsheets/d/1moGilWvDPaaNx5Dd07FdzvMbhymOJ-CZPktNkqMjWm8/edit?usp=sharing

J'avais comme idée de partir sur ce script que nous avions utilisé pour sauvegarder les changements et l'adapter a notre situation

function update() {
  var classeurAPI = SpreadsheetApp.getActiveSpreadsheet();
  var feuilleAPI = classeurAPI.getSheetByName('Update');

  // je sauvegarde ici toutes les données
  var derLigne = feuilleAPI.getLastRow()
  var valeurs = feuilleAPI.getRange('A2:H'+derLigne).getValues();

  // j'identifie tous les items en leur donnant leur n° de ligne
  var myDico = new Map();
  for (var i = 0; i < valeurs.length; i++) {
    myDico.set(valeurs[i][0],i)
  };

  // ton interrogation sans changement
  var urlAPI = "https://api.realt.community/v1/token";
  var response = UrlFetchApp.fetch(urlAPI, {
    "method": "GET",
    "headers": {
      "accept":"<*/*>",
      "X-AUTH-REALT-TOKEN": "_________________________",
      "Content-Type": "application/json"
    }
  });
  var json = response.getContentText();
  var data = JSON.parse(json);
  for (let i in data) {
    var tokenPrice = data[i].tokenPrice
    var fullname = data[i].fullName 

    // l'item existe-t-il déjà ?
    if (myDico.has(fullname) == true){
      var ligne = myDico.get(fullname)
      // sa valeur a-t-elle évolué ?
      if(tokenPrice != valeurs[ligne][1]) {
        // on décale alors l'historique des valeurs
        for (var j=7;j>1;j--){
          valeurs[ligne][j] = valeurs[ligne][j-1]
        }
        valeurs[ligne][1] = tokenPrice
      }
    }
    // nouvel item
    else {
      valeurs.push([fullname, tokenPrice, '', '', '', '', '', '']);
    }

  }
  feuilleAPI.getRange(2, 1, valeurs.length, valeurs[0].length).setValues(valeurs).sort({column: 1, ascending: false});
}

2 problèmes

-> il y a pas forcement des "nouvelles valeurs" car d'une semaine à l'autre la Rent peut être identique.
-> le fait qu'il y ai un nombre défini de colonne va me brider dans le temps

je vais faire étape par étape :

Concernant la fonction realt_rent() je vais voir si c'est possible d'avoir un résultat en colonne plutôt qu'en ligne

Concernant la fonction qui extrait les données du .CSV, je vais voir si c'est possible d'additionner les données lorsqu'il y a plusieurs adresses dans les paramètres

Ensuite je verrais comment structurer l'enregistrement comme j'aimerai

je suis tes élucubrations, mais je ne suis pas encore rentré dans le sujet, j'observe ...

je suis tes élucubrations, mais je ne suis pas encore rentré dans le sujet, j'observe ...

J'espère au moins qu'elles ont un sens 😉🙂

mais tes propos me rassure pas 😁

tu sais comment je fonctionne maintenant, j'essaye de réfléchir à partir de chose qu'on a déjà vu ici.. pour faire autre chose..

je vais faire étape par étape :

Concernant la fonction realt_rent() je vais voir si c'est possible d'avoir un résultat en colonne plutôt qu'en ligne

Cette partie = OK! ( je pense que c'était la plus simple.. )

function realt_rent() {

var requete ="is:unread label:realT/Rents"
var threads = GmailApp.search(requete);
var classeurRent = SpreadsheetApp.getActiveSpreadsheet();
var feuilleRent = classeurRent.getSheetByName('test');
var cellformatDATE = feuilleRent.getRangeList(['A1:1','A3:3','A4:4'])
var cellformatRENT = feuilleRent.getRange('A5:5')

  for (var i = 0; i < threads.length; i++) {
  var messages = threads[i].getMessages();
  var output =[]
      for (var j = 0; j < messages.length; j++) {
      var msg = messages[j].getPlainBody();
      var sub = messages[j].getSubject();
      var dat = messages[j].getDate();
      var regExp = msg.match(/[-\w]{25,}/);
      var regExp1 = sub.match(/([0-9]{4}.[0-9]{1,2}.[0-9]{1,2})/ig)[0];
      var regExp2 = sub.match(/([0-9]{4}.[0-9]{1,2}.[0-9]{1,2})/ig)[1];
      var regExp3 = msg.match(/(?<=Total Earned: |Total perçu :|Grand Total )(.*)(?= USDC De| USDC From)/ig);
      var colonne = getLastDataColumnrealtrent(feuilleRent)+1 

      output.push([dat], [regExp], [regExp1], [regExp2], [regExp3]);
      }
    if (output.length > 0) {
    feuilleRent.getRange(1,colonne,output.length).setValues(output) 
    cellformatDATE.setNumberFormat('dd.mm.yyyy'); 
    cellformatRENT.setNumberFormat('$00.000000')
    }
  }
}

 function getLastDataColumnrealtrent(sheet,col) {
    var lastColumn = sheet.getLastColumn();
    var range = sheet.getRange(1,lastColumn);
    if (range.getValue() !== "") {
      return lastColumn;
    } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getColumn();
    }
  }

En faite.. voici un bon exemple visible de ce que j'aimerai en gros mais je pense pas avoir les capacités de réussir cela !

( voici le site de la capture d'écran : https://www.cryptalloc.com/realt/ et l'adresse utilisé pour l'exemple : 0x00B591BC2b682a0B30dd72Bac9406BfA13e5d3cd )

J'ai juste une page blanche

il faut cliquer sur la page "Rents"

pardon,

je ne sais pas comment le site est conçu :D

donc une interrogation avec méthode post ... je ne sais pas faire avec google sheets d'autant que je ne vois pas trouvé comment était écrit le formulaire

j'ai même essayé avec cette adresse mais rien https://www.cryptalloc.com/realt/index.php?MODL=0x00B591BC2b682a0B30dd72Bac9406BfA13e5d3cd

c'était pour imager ce que je voulais essayer de faire.

mais j'ai il y a un gros travail de traitement de donner pour arriver a ce résultat

Bon je vais pas avoir le choix que de faire en quelques étapes.

le .CSV brut me donne trop de travail et je n'ai pas les connaissances pour faire quelques chose "tout-en-un"

il y a des données "parasites" qui m'empêche de faire des recherches simples sur chaque .CSV, comme par exemple pour 1 nom j'ai 3 variantes :

1) j'utilise le script au dessus en filtrant au maximum avec les variantes que je connais

csvData[i][ii]=csvData[i][ii].replace(" - TOTAL","").replace(" - DEPOSIT","").replace(" - REINVEST","").replace(" Rmm","").replace("OLD-","")

2) je créer un tableau propre en regroupant les lignes qui ont le même noms et en additionnant les valeurs correspondantes

=QUERY({Import!G2:H207},"Select Col2, sum(Col3) group by Col2 Order by Col2 DESC")

3) j'utilise la liste des maisons contenus dans l'API pour être toujours à jours et je fait un VLOOKUP

il faut quand même que je trouve un moyen pour enregistrer ces valeurs…

Bon ça me prends la tête quand même cette histoire car je sais que dans le temps ça sera pas tenable.. je vais poursuivre mes réflexions sur ce sujet

étapes par étapes, problèmes par problèmes…..

je vais tout faire sur une feuille. pour récupérer l'ID du .CSV présent sur mon mail / traiter le .CSV

function rent(){
  realt_rent(); 
  var feuilleCsv = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test");
  var plageCsv = SpreadsheetApp.getActiveSpreadsheet().getRange('A7:C')
  var fichierId = feuilleCsv.getRange('B2').getValue();  
  plageCsv.clear();
  lireCsvRentFromId(fichierId)
}

function realt_rent() {
  var requete ="is:unread label:realT/Rents"
  var threads = GmailApp.search(requete);
  var classeurRent = SpreadsheetApp.getActiveSpreadsheet();
  var feuilleRent = classeurRent.getSheetByName('test');
  var cellformatDATE = feuilleRent.getRangeList(['A1:1','A3:3','A4:4'])
  var cellformatRENT = feuilleRent.getRange('A5:5')
    for (var i = 0; i < threads.length; i++) {
      var messages = threads[i].getMessages();
      var output =[]
        for (var j = 0; j < messages.length; j++) {
          var msg = messages[j].getPlainBody();
          var sub = messages[j].getSubject();
          var dat = messages[j].getDate();
          var regExp = msg.match(/[-\w]{25,}/);
          var regExp1 = sub.match(/([0-9]{4}.[0-9]{1,2}.[0-9]{1,2})/ig)[0];
          var regExp2 = sub.match(/([0-9]{4}.[0-9]{1,2}.[0-9]{1,2})/ig)[1];
          var regExp3 = msg.match(/(?<=Total Earned: |Total perçu :|Grand Total )(.*)(?= USDC De| USDC From)/ig);
          var colonne = getLastDataColumnrealtrent(feuilleRent)+1 
            output.push([dat], [regExp], [regExp1], [regExp2], [regExp3]);
        }
    if (output.length > 0) {
    feuilleRent.getRange(1,colonne,output.length).setValues(output) 
    cellformatDATE.setNumberFormat('dd.mm.yyyy'); 
    cellformatRENT.setNumberFormat('$00.000000')
    }
  }
}

function getLastDataColumnrealtrent(sheet,col) {
  var lastColumn = sheet.getLastColumn();
  var range = sheet.getRange(1,lastColumn);
  if (range.getValue() !== "") {
    return lastColumn;
  }else {
  return range.getNextDataCell(SpreadsheetApp.Direction.UP).getColumn();
  }
}

function lireCsvRentFromId(id) {
  var id1Csv= "0x43a89C2f84e7e731D1787b523AE4e1c588C1Aa6A"
  var id2Csv= "0x00B591BC2b682a0B30dd72Bac9406BfA13e5d3cd"
  var feuilleCsv = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test");
  var cellformat = feuilleCsv.getRange('B8:C')
  var result = []
  var csv = DriveApp.getFileById(id).getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(csv);
  var num1 = parseInt(id1Csv)
  var num2 = parseInt(id2Csv)
  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'){
      if (csvData[i][0]=='Investor'){
        for (var ii=0;ii<csvData[i].length;ii++){
          csvData[i][ii]=csvData[i][ii].replace(" - TOTAL","").replace(" - DEPOSIT","").replace(" - REINVEST","").replace(" Rmm","").replace("OLD-","")
        }
      }
      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]!='' && result[i][j]!=0){flag = true}
      }
      if (flag) {newResult.push(result[i])}
    }
    feuilleCsv.getRange(7, 1, newResult.length, newResult[0].length).setValues(newResult)
    cellformat.setNumberFormat('$0.000000')
  }catch(e){
  }
  SpreadsheetApp.flush();
}

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

1ere question : Est-ce possible d'additionner les deux adresses directement depuis le script ?

2ieme question : Est-ce possible directement de regrouper les noms identiques ( et additionner les valeurs associés ) directement depuis ce script ? ( ce que je voulais faire QUERY sur un tableau à part )

Merci !

comme dirait Pierre Dac, OUI c'est possible

je regarderai si tu veux, mais là je suis en plein délire dans des histoires de regex et richtext

Merci c'est gentils.

je pense avoir trouver un cap a suivre pour la solution a ce que j'aimerai… on verra si c'est bien ou pas..

Pour compléter ce que j'ai en tête

Si c'est possible d'obtenir un tableau "propre" du premier coup, voici ce que j'avais en tête :

le tableau en A:B correspond a l'exécution du script mail + traitement du CSV

et ensuite un tableau report de données pour "sauvegarder" sur le modèle de ce script : https://forum.excel-pratique.com/s/goto/1081069

j'essaye de le comprendre pour pouvoir adapter car le mécanisme changerait un peu ( à savoir report de données mêmes si il n'y a pas de valeurs différentes )

ça parait clair et ça semble plausible ? ou c'est toujours des élucubrations ?

1ere question : Est-ce possible d'additionner les deux adresses directement depuis le script ?

2ieme question : Est-ce possible directement de regrouper les noms identiques ( et additionner les valeurs associés ) directement depuis ce script ? ( ce que je voulais faire QUERY sur un tableau à part )

je lis et me documente en attendant mais le langage javascript est un peu diffèrent parfois

par exemple ceci https://help.catalytic.com/docs/csv-summarize-rows-with-formulas/

Encore faut-il comprendre comment placer ces éléments

Pourquoi ce langage n'a pas qu'un lexique unique ( malgré les similitudes ) ahahah ! pas pratique pour l'apprentissage...

je clôture ce post..

je vais faire sujet par sujet et étape par étape..

ça deviendra vite illisible

c'est mieux, j'avais décroché !