Google script - Sélection prix dans BdD

Le 05/07/2022 à 15:56

Bonjour,

Je vous remercie de l'intérêt que vous apporterez à ma demande.

Mon fichier de travail comporte aujourd'hui un script qui fonctionne relativement bien, mais qui ne couvre pas complétement mon besoin.

Ce script est lancé via le bouton en A1:B6 de l'onglet "Mat & Comp Quick Search" du fichier ci-dessous:

https://docs.google.com/spreadsheets/d/1Hvu1BurYHug9F8UeuZmbP4IR4hQaNx4OIUJOWQj-Y9Q/edit?usp=sharing

Ce script fait en quelque sorte une recherche V pour chacune de mes références listées en colonne A de la feuille "Datas from xxxx", dans le fichier BdD suivant:

https://docs.google.com/spreadsheets/d/1IN1ATpco0Ol6eCpmj0Iqhp_eUXaf42MclcJSRD9fZNs/edit?usp=sharing

Puis le script insère les données pour chacune des références unique entre la case A9:I199, et en colonne J ajoute un commentaire "Selected by Script".

Puis, via un formatage conditionnel, ces ref comportant un prix sont surlignées en verts, et celles ne comportant pas de prix surlignées en rouges.

J'aurais besoin de votre aide pour développer un second script qui ferait le process suivant.

L'utilisateur cliquerait sur le bouton "Search" en ligne 200, et déclencherait le script suivant:

1- ligne rouge par ligne rouge, le script ferait une recherche de la ref colonne A dans la BdD, mais en modifiant la recherche.

2- L'idée que cette recherche se fasse selon des boucles de recherche selon diverses REGEX:

- par exemple, en enlevant les caractères spéciaux à la ref

- et en enlevant le dernier digit

- et en enlevant l'avant dernier digit et le dernier

- et ainsi de suite

3- Le script afficherait les sélections trouvés dans le tableau A204 : Ixxx.

--> Si rien trouvé mettre commentaire en D204 'Pas de prix trouvé"

4- Si trouvé, L'utilisateur choisirait une ligne de la sélection proposé par le script et en appuyant sur le bouton Validate.

Le script collerait les infos dans le tableau se trouvant en haut de l'onglet et en joutant en colonne J "Ajouté manuellement'".

5- Puis automatiquement le script referait la boucle des opérations 1 à 4, jusqu'à la dernière ligne rouge

J'espère que mes explications sont claires et vous remercie pour votre aide.

Un lecteur assidu de ce forum et en admiration sur la qualité des réponses.

Le 06/07/2022 à 11:03

Bonjour,

un peu compliqué, mais tu peux faire ceci (le résultat est dans une feuille test ... je te laisse tricoter les données en fonction des colonnes)

function myFunction() {
  var src = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1IN1ATpco0Ol6eCpmj0Iqhp_eUXaf42MclcJSRD9fZNs/edit');
  var dst = SpreadsheetApp.getActiveSpreadsheet();

  var lastActiveRowDst = dst.getSheetByName('Mat & Comp Quick Search').getLastRow();
  var data1 = dst.getSheetByName('Mat & Comp Quick Search').getRange("A9:J199" + lastActiveRowDst).getValues();

  var lastActiveRowBdd = src.getLastRow();
  var data2 = src.getSheetByName('Import from CSV').getRange("B1:AD" + lastActiveRowBdd).getValues();

  data1.forEach(d => {
    if (d[9] == '' && d[0] != '') {
      for (i = d[0].length - 1; i > 0; i--) {
        var ref = d[0].substring(0, i)
        var result = data2.filter(d => d[0] == ref)
        if (result.length) {
          dst.getSheetByName('test').appendRow(result.flat())
        }
      }
    }
  })
}
image
Le 06/07/2022 à 15:04

Merci Steelson pour ton aide.

ton script insère bien les valeurs demandées mais les mets en bloc (toutes les lignes rouges).

Il faudrait qu'il s'interrompt pour chaque ligne rouge et qu'il me propose de valider la sélection que je décide être la bonne.

j'avais penser que lorsqu'il le script colle la sélection de choix possible à partir de la ligne 204, que mette mon curseur sur une ligne choisie et qu'en appuyant sur le bouton "Validate", le script colle ces valeurs dans le tableau du haut. Du coup la ligne passerait au vert.

Puis le script, continuerait ligne rouge par ligne rouge.

Mes connaissances Javascript étant limitées, je n'arrive pas à continuer à coder !

Merci pour votre aide

Le 06/07/2022 à 16:10

Je ne saurais pas "arrêter" le script et le relancer, trop complexe !

Par contre, je peux ajouter à la recherche le n° de ligne (entre 9 et 199), et ensuite une case à cocher en face du résultat qui renverrai alors les informations sélectionnées.

Il faut que tu me dises dans les informations que j'ai relevées (dans l'onglet test), lesquelles vont dans quelle colonne.

Le 06/07/2022 à 16:56

désolé mais je ne vois d'onglet test.

Mais, ce que j'ai fait dans mon premier script doit répondre à tes interrogations.

        var range= dst.getSheetByName('Mat & Comp Quick Search').getRange("A9:J199" + lastActiveRowDst);
        range.getCell(j+1,4).setValue(data2[i][0]); //Col index 0 de la BdD doit être coller en col index 4 de l'onglet "Mat & Comp Quick Search"
        range.getCell(j+1,5).setValue(data2[i][1]);//Col index 1 de la BdD doit être coller en col index 5 de l'onglet "Mat & Comp Quick Search"
        range.getCell(j+1,6).setValue(data2[i][2]);//Col index 2 de la BdD doit être coller en col index 6 de l'onglet "Mat & Comp Quick Search"
        range.getCell(j+1,7).setValue(data2[i][3]);//Col index 3 de la BdD doit être coller en col index 7 de l'onglet "Mat & Comp Quick Search"
        range.getCell(j+1,8).setValue(data2[i][4]);//Col index 4 de la BdD doit être coller en col index 8 de l'onglet "Mat & Comp Quick Search"
        range.getCell(j+1,9).setValue(data2[i][5]);//Col index 5 de la BdD doit être coller en col index 9 de l'onglet "Mat & Comp Quick Search"
        range.getCell(j+1,10).setValue("Selected by Script");

//Ajouter en col index 0 de l'onglet "Mat & Comp Quick Search", la col index 0 de la ligne rouge
//Ajouter en col index 1 de l'onglet "Mat & Comp Quick Search", la col index 1 de la ligne rouge

Parfait pour la case à cocher. j'ai ajouté la case à cocher en colonne J

Le 06/07/2022 à 17:43
VINCENT31000000 a écrit :

désolé mais je ne vois d'onglet test.

oui, il faut le créer

dst.getSheetByName('test').appendRow(result.flat())

je regarde ton code, je mets les résultats dans ton onglet (on oublie test qui était juste un ... test)

Le 07/07/2022 à 06:29

mets en J204 et suivant une case à cocher

pour l'instant je n'ai fait que la recherche, reste à faire le choix et l'affectation

function searchPartial() {
  var src = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1IN1ATpco0Ol6eCpmj0Iqhp_eUXaf42MclcJSRD9fZNs/edit');
  var dst = SpreadsheetApp.getActiveSpreadsheet();

  var lastActiveRowDst = dst.getSheetByName('Mat & Comp Quick Search').getLastRow();
  dst.getSheetByName('Mat & Comp Quick Search').getRange("A204:J" + lastActiveRowDst).clearContent();
  var data1 = dst.getSheetByName('Mat & Comp Quick Search').getRange("A9:J199").getValues();

  var lastActiveRowBdd = src.getLastRow();
  var data2 = src.getSheetByName('Import from CSV').getRange("B1:AD" + lastActiveRowBdd).getValues();

  var result = []
  data1.forEach((d, row) => {
    if (d[9] == '' && d[0] != '') {
      for (i = d[0].length - 1; i > 0; i--) {
        var ref = d[0].substring(0, i)
        var x = data2.filter(d => d[0] == ref).map(d => [+row + 9, d[0], d[1], d[2], d[3], d[4], d[5], false]).flat()
        if (x.length) result.push(x)
      }
    }
  })
  if (result.length) {
    dst.getSheetByName('Mat & Comp Quick Search').getRange(204,3,result.length,result[0].length).setValues(result)
  }
}
Le 07/07/2022 à 10:57

Merci Steelson. Cela fonctionne bien.

J'ai modifié TRES légèrement le code, et bute sur l'intégration de la case à cocher à intégrer dans la loupe ! Eh oui, l'élève n'est pas prêt de dépasser le maitre !

De plus, est il possible que tu me modifies mon code initial afin que lorsque le script insère le premier array en case A9, que le script insère le nombre de ligne correspondant à la longueur de l'array.

Cela permettra d'éviter d'avoir des lignes vides comme dans cet exemple commun (ligne 33 à 199).

Merci encore

Le 07/07/2022 à 13:26
VINCENT31000000 a écrit :

bute sur l'intégration de la case à cocher à intégrer dans la loupe !

dans la loupe ?

il suffit de mettre des cases à cocher sur la colonne J à partir de 204

image
Le 07/07/2022 à 13:48

une fois les cases cochées, tu peux valider comme suit

function valider() {
  var dst = SpreadsheetApp.getActiveSpreadsheet();
  var lastActiveRowDst = dst.getSheetByName('Mat & Comp Quick Search').getLastRow();
  dst.getSheetByName('Mat & Comp Quick Search').getRange("C204:J" + lastActiveRowDst).getValues().filter(r => r[7]).forEach(r => {
    dst.getRange('D' + r[0] + ':I' + r[0]).setValues([r.splice(1, 6)])
  })
}
Le 08/07/2022 à 10:35

Salut Steelson,

impeccable cela marche bien...j'ai adapté le script à mon nouveau formulaire, dans fichier d'origine.

Serait-il possible d'intégrer que tu m'aides pour les étapes suivantes?

1- dans mon code initial lancé via le bouton en A1:B6 :

Le fichier serait un template avec à l'ouverture 15 lignes entre la case A9 et le tableau se trouvant en A200 (Cela permettra d'éviter d'avoir des lignes vides comme dans cet exemple commun "ligne 33 à 199")

Le script nsèrerait le premier array en case A9, puis le script insère le nombre de ligne correspondant à la longueur de l'array.

2-Est-il possible de rendre mon script plus rapide. Car du fait, que ma BdD fait 250000 lignes il me faut a peu prés 2 minutes pour récupérer les infos

Merci encore de ton aide très apprécié!

Le 08/07/2022 à 12:39

Il faudrait décomposer cela dans des nouveaux topics. 1 problème = 1 topic.

point 1 : j'ai quand même des interrogations : faut-il prévoir une remise à zéro ? et je ne suis pas favorable à cela, sans compter que cela risque d'alourdir encore un peu plus le temps d'exécution

point 2 : le problème se situe ici

  var lastActiveRowBdd = src.getLastRow();
  var data2 = src.getSheetByName('Import from CSV').getRange("B1:AD" + lastActiveRowBdd).getValues();

  for (var j = 0; j < data1.length; j++) {
    for (var i = 0; i < data2.length; i++) {
      if (data1[j][0] == data2[i][0] && data2[i][3] != "") {
        var range = dst.getSheetByName('Mat & Comp Quick Search').getRange("A9:J199" + lastActiveRowDst);
        range.getCell(j + 1, 4).setValue(data2[i][0]);
        range.getCell(j + 1, 5).setValue(data2[i][1]);
        range.getCell(j + 1, 6).setValue(data2[i][2]);
        range.getCell(j + 1, 7).setValue(data2[i][3]);
        range.getCell(j + 1, 8).setValue(data2[i][4]);
        range.getCell(j + 1, 9).setValue(data2[i][5]);
        range.getCell(j + 1, 10).setValue("Selected by Script");
      }
    }
  }

et probablement par ordre d'importance

  1. ne faire qu'un seul setValue après la boucle
  2. ne pas faire une recherche de la dernière ligne à chaque pas de la boucle, sachant qu'en plus ici il ne sert pas à rien puisque tu as déjà spécifié 199 !
  3. pourquoi appeler les colonnes de B à AD si tu n'utilises qu'une petite partie
Le 08/07/2022 à 13:52

essaie

  ////////////////////////////////////////////////////////////////////////////////
  //Set prices from Bdd
  ////////////////////////////////////////////////////////////////////////////////

  var src = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1IN1ATpco0Ol6eCpmj0Iqhp_eUXaf42MclcJSRD9fZNs/edit');
  var dst = SpreadsheetApp.getActiveSpreadsheet();

  var data1 = dst.getSheetByName('Mat & Comp Quick Search').getRange("A9:J199").getValues();

  var lastActiveRowBdd = src.getLastRow();
  var data2 = src.getSheetByName('Import from CSV').getRange("B1:G" + lastActiveRowBdd).getValues();

  for (var j = 0; j < data1.length; j++) {
    for (var i = 0; i < data2.length; i++) {
      if (data1[j][0] == data2[i][0] && data2[i][3] != "") {
        // on reprend la ligne j en la complétant
        data1[j] = [data1[j][0],data1[j][1],data1[j][2],...data2[i], 'Selected by Script']
      }
    }
  }
  dst.getSheetByName('Mat & Comp Quick Search').getRange("A9:J199").setValues(data1);

  //Toast message to inform that Values have been set from Bdd (match perfect)
Le 10/07/2022 à 20:00

Merci encore une fois Steelson.

Effectivement, ton code est bien réduit et aurait du être plus rapide. Je comprends tes explications pour l'améliorer.

Par surprise le temps d'exécution est plus long !!!!! Mais je reste sur ta version, qui est très bien.

Du coup je solde ce topic, et en ouvre un autre.

Bonne soirée

Le 11/07/2022 à 04:42
VINCENT31000000 a écrit :

Par surprise le temps d'exécution est plus long !!!!! Mais je reste sur ta version, qui est très bien.

je ne comprends pas bien ! est-ce que les données par ailleurs étaient strictement les mêmes ?

Le 11/07/2022 à 06:50

Ben, la structure de la BdD et du fichier de recherche sont identique mais la BdD est à la limite de ce que peut supporter Google sheet.

Quand je dis que c'est plus long, ce n'est pas tout à fais juste car ma version était à 65 secondes pour extraire les données alors que la tienne était à 67 secondes...on peut dire que c'est identique (pour 92 références)

Rechercher des sujets similaires à "google script selection prix bdd"