QUERY et REGEXREPLACE pour projet d'extraction

Hello La team !

Je recherche à extraire des données (que j'automatiserai ensuite avec une API de gestion de données) dans une feuille à part de mon GSheet (ici joint).

Ces données seraient le résultat de requêtes qui appelleraient, en fonction de 2 ou 3 conditions, des noms + valeurs. En disant cela je fais appel à de la query, du select, du where, etc. J'ai des bribes de résultats mais c'est...nul.

La requête :

Dans l'exemple ici joint, je souhaiterais obtenir le nom des conseillers (en Col A) qui sont à l'origine de remises forcées (correspondant au code 7 ou 9 en Col F) dont le montant est supérieur à 50€ (>50 en Col H) et dont le statut est "Disponible" ou "Validé" (Col J).

Le résultat :

A côté de la liste de noms qui en resort (une ligne en dessous de l'autre), j'aurais voulu avoir le numéro de commande associé (de la Col A) et le montant remisé (toujours notre Col H).

Cela ressemblerait à ça :

J'ai des choses comme ça (une dizaine de formulation différentes, mais bon ça n'aboutie pas : =QUERY(A1:J44;"Select A, H where H > 50";2)

Par avance merci de votre regard :-)

Ben

Bonjour,

désolé, mais si c'est bien du google sheets, un fichier excel n'est pas vraiment utile !

peux-tu mettre un lien vers un fichier exemple anonymisé ? https://www.sheets-pratique.com/fr/cours/partage

Oui bien sûr !

https://docs.google.com/spreadsheets/d/1jMhuhBWwcM-j-G4i-qGdDChnPssjHj6vEJYsiF7V2_c/copy

La plus grosse difficulté est de ne pas avoir le nom sur la même ligne ! Cela va donner une formule du coup assez complexe.

A mettre en A2 d'une nouvelle feuille

=query({ ArrayFormula(lookup(row(Forum!A2:A);row(Forum!A2:A)/if(left(Forum!A2:A;10)="Conseiller";1;0);Forum!A2:A))\Forum!A2:J };"select * where (Col7=7 or Col7=9) and Col9>=50 and (Col11='Validé' or Col11='Disponible')";0)

La partie la plus complexe est l'ajout d'une ligne fictive qui est le nom du conseiller

ArrayFormula(lookup(row(Forum!A2:A);row(Forum!A2:A)/if(left(Forum!A2:A;10)="Conseiller";1;0)

Waouh, canon, ça marche parfaitement , merci ! . La next step idéale serait de pouvoir "synthétiser" ou "concaténer" en quelque sorte les conseillers à l'origine de remises, car le mail leur serait adressé automatiquement à partir de leur nom (avec une formule qui génère l'adresse mail à partir des cellules en A du type : =regexreplace(CONCATENATE(LOWER(regexreplace(PROPER(A1);"Conseiller : ";""));"@monmail.com");" ";".")

Exemple : Si la même personne fait 12 remises (donc 12 lignes vont se mettre l'une en dessous de l'autre, il va malheureusement recevoir 12 mails différents. Or j'aimerais faire un mail par conseiller (par Cellule A unique donc) qui vient me donner l'ensemble des remises concernnées.

https://docs.google.com/spreadsheets/d/1jMhuhBWwcM-j-G4i-qGdDChnPssjHj6vEJYsiF7V2_c/copy

une proposition

tout dépend des informations que tu souhaites envoyer

var feuille = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Requête')
function sendEmail() {
  var listeEmails = feuille.getRange('L2:L' + feuille.getLastRow()).getValues().flat().filter(onlyUnique)
  listeEmails.forEach(function (qui) {
    var data = feuille.getRange('A2:L' + feuille.getLastRow()).getDisplayValues().filter(r => r[11] == qui)
    MailApp.sendEmail({
      to: qui,
      subject: 'Remises ...',
      htmlBody: tableHTML(data)
    })
  })
};
function tableHTML(plage) {
  // Source : https://www.sheets-pratique.com/fr/codes/fonction-table
  return '<table>' + plage.map(l => '<tr><td>' + l.join('</td><td>') + '</td></tr>').join('') + '</table>';
}
function onlyUnique(value, index, self) {
  return self.indexOf(value) === index;
}

Ah oui tu es allé super loin . Je pensais plutôt à ça :

J'irai seulement ensuite avec une API tierce (Integromat ou Zappier) automatiser les envois (l'api cherche simplement les valeurs des cellules, ex : 'D' pour le destinataire, Vous avez fait 'C' de remise sur la ou les commandes 'B'. etc.

Une idée ?

Merci bcp

je vais voir pour rassembler les données

Benjamin G67 a écrit :

J'irai seulement ensuite avec une API tierce (Integromat ou Zappier) automatiser les envois

ah bon ! pourquoi se compliquer la vie ?

Benjamin G67 a écrit :

Vous avez fait 'C' de remise sur la ou les commandes 'B'. etc.

j'a juste pris brutalement les données, je n'ai pas réfléchi à ce qu'il y avait dedans

ah bon ! pourquoi se compliquer la vie ?

Si tu arrives à m'éclairer oui ce serait top ;-)

Il est possible d'ajouter en N2

=ARRAY_CONSTRAIN(transpose({
transpose(unique(A2:A));
arrayformula(regexreplace(trim(query(arrayformula(if(A2:A=transpose(unique(A2:A));B2:B&"; ";));;9^9));"[;\s]+$";""));
arrayformula(regexreplace(trim(query(arrayformula(if(A2:A=transpose(unique(A2:A));I2:I&"; ";));;9^9));"[;\s]+$";""))
});counta(unique(A2:A));3)

mais avec cette formule bien alambiquée, je pense que cela ne serait pas raisonnable et pas maintenable !

alors que le faire dans le script, c'est plus facile !

var feuille = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Requête')
function sendEmail() {
  var listeEmails = feuille.getRange('L2:L' + feuille.getLastRow()).getValues().flat().filter(onlyUnique)
  listeEmails.forEach(function (qui) {
    var somme=0
    var affaires = '<br>'
    feuille.getRange('A2:L' + feuille.getLastRow()).getDisplayValues().filter(r => r[11] == qui).forEach(function(ligne){
      affaires += ligne[1] + '<br>'
      somme = somme + (ligne[8].replace(',','.'))*1
    })
    MailApp.sendEmail({
      to: qui,
      subject: 'Remises ...',
      htmlBody: 'Affaire(s) :'+affaires+'<br>pour un montant de : '+somme
    })
  })
};
function onlyUnique(value, index, self) {
  return self.indexOf(value) === index;
}

avec un trigger quotidien à 8:00

J'avoue que j'ai fait la solution d'ajout en N2 . En tout cas ça marche très bien, j'ai inséré une ou deux choses en plus.
Question subsidiaire : Je génère l'adresse mail avec partir d'une valeur de cellule native ex : Conseiller : JEAN-PAUL DE CARLI ou Conseiller :ROBERT FLANCHON

Cela marche quand la cellule correspond à PRENOM NOM. Par contre quand PRENOM NOM COMPOSE cela ne marche pas... Cela me donne jean-paul.de.carli@monmail.com alors que ce devrait être jean-paul.decarli@monmail.com

=regexreplace(CONCATENER(MINUSCULE(regexreplace(NOMPROPRE(N1:N);"Conseiller : ";""));"@monmail.com");" ";".")

On voit bien que le problème provient du fait que je mets un point (".") entre chaque espace alors que je souhaiterais un seul point entre prénom et nom et ensuite accoler le reste : Conseiller : PATRICIA DU MONTAIGNE --> patricia.dumontaigne@monmail.com
Une idée ?

Benjamin G67 a écrit :

Cela marche quand la cellule correspond à PRENOM NOM. Par contre quand PRENOM NOM COMPOSE cela ne marche pas...

Il y a tellement de particularités, avec ou sans point, avec initiale du prénom seulement, avec un suffixe ... que la seule façon de s'en sortir est de faire une table qui donne la relation entre le conseiller et son adresse email

Et si je te dis qu'il n y a que deux cas de figures possibles : PRENOM NOM et PRENOM NOM COMPOSE, cela simplifie-t-il la démarche. Genre CAMILLE DUPONT et CAMILLE DUPONT MARGO ?

ok, et qui donnent quoi en terme d'adresse email ?

• Conseiller : CAMILLE DUPONT >> ??
• Conseiller : CAMILLE DUPONT MARGO >> ??

ok, et qui donnent quoi en terme d'adresse email ?

Conseiller : CAMILLE DUPONT >> ??

Conseiller : CAMILLE DUPONT MARGO >> ??

camille.dupont@monmail.com

camille.dupontmargo@monmail.com

Dans ce cas, à partir de

Conseiller : CAMILLE DUPONT MARGO

on peut écrire

function email(nom) {
  var tab = [...nom.matchAll(/[A-Z]+/g)].flat();
  var adresseEmail = tab[1].toLowerCase() + '.' + tab[2].toLowerCase() + (tab.length == 4 ? tab[3].toLowerCase() : '') + '@monmail.com'
  return (adresseEmail)
}

Ce qui donne globalement

var feuille = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Requête')
function sendEmail() {
  var listeEmails = feuille.getRange('A2:A' + feuille.getLastRow()).getValues().flat().filter(onlyUnique)
  listeEmails.forEach(function (qui) {
    var somme = 0
    var affaires = '<br>'
    feuille.getRange('A2:K' + feuille.getLastRow()).getDisplayValues().filter(r => r[0] == qui).forEach(function (ligne) {
      affaires += ligne[1] + '<br>'
      somme = somme + (ligne[8].replace(',', '.')) * 1
    })
    MailApp.sendEmail({
      to: email(qui),
      subject: 'Remises ...',
      htmlBody: 'Affaire(s) :' + affaires + '<br>pour un montant de : ' + somme
    })
  })
};
function onlyUnique(value, index, self) {
  return self.indexOf(value) === index;
}
function email(nom) {
  var tab = [...nom.matchAll(/[A-Z]+/g)].flat();
  var adresseEmail = tab[1].toLowerCase() + '.' + tab[2].toLowerCase() + (tab.length == 4 ? tab[3].toLowerCase() : '') + '@monmail.com'
  return (adresseEmail)
}

Et si je souhaitais toujours (shame on me, i know) l'avoir plutôt comme une fonction au sein de gsheet sans passer par du script, que donnerait :

function email(nom) { var tab = [...nom.matchAll(/[A-Z]+/g)].flat(); var adresseEmail = tab[1].toLowerCase() + '.' + tab[2].toLowerCase() + (tab.length == 4 ? tab[3].toLowerCase() : '') + '@monmail.com' return (adresseEmail)}

?

Je sais, faut vraiment que je me mette au script.

question à laquelle je m'attendais

tu peux mettre dans ta feuille =email(A2)

ou

=LOWER(index(split(A2;" ");3))&"."&substitute(regexextract(LOWER(TEXTJOIN(".";;REGEXEXTRACT(A2;REGEXREPLACE(A2;"([A-Z]{2,})";"($1)"))));"\.(.*)");".";"")&"@monemail.com"