Mise à jour du stock produit via fonction onEdit + scan EAN

Bonjour,

Je travaille sur googlesheet pour gérer une BDD produit.

J'aimerai pouvoir effectuer une gestion simples des stocks mais je suis une quille en Google Apps Script...

C'est pourquoi je viens demander de l'aide pour m'aider dans cette tâche.

https://docs.google.com/spreadsheets/d/11HIXhRu8IP2Qgo8RXI319nkY5VceSX8QtaV6xzGl-gg/edit?usp=sharing

L'objectif est pourtant simple :

Scanner les codes barres de mes produits afin de mettre à jour les stocks.

Dans le fichier exemple ci-dessus ; si je scanne le code barre EAN 3168930159810, alors la quantité du produit correspondant a l'EAN scanné ("7UP Mojito - canette slim - 33cl x 24" dans l'exemple) doit passer de 3 à 4 en colonne F.

Aussi simplement que cela... mais je n'y arrive pas...

Une subtilité réside dans le fait que si je scan un code-barre EAN correspondant a plusieurs produits (lignes 3,4 et 13,14 en jaune dans l'exemple), alors l'addition doit être effectuer en colonne F du produit dont l'EAN scanné correspond ET dont le SKU (colonne A) fait strictement 23 caractères de long (ligne 3 et 13 dans l'exemple).

D'après mes recherches il me semble que je devrais passer par une fonction onEdit() mais je ne comprends pas comment;

Voila, j'espère que c'est clair.

Merci beaucoup par avance pour votre précieuse aide.

Balek

Bonjour,

tu n'as pas besoin du menu pour autoriser ici.

insert 2 lignes avant les en-têtes et réserve la cellule B1 à la saisie du scan comme ici

script :

function onEdit(e) {
  var sh = e.source.getActiveSheet()
  var rng = e.source.getActiveRange()
  if (sh.getName() == 'CATAnew' && rng.getA1Notation() == 'B1' && rng.getValue() != '') {
    var data = sh.getRange('A4:E' + sh.getLastRow()).getValues().filter(r => r[4] == rng.getValue())
    if (data.length == 0) return;
    sh.getRange('A4:E' + sh.getLastRow()).getValues().forEach((r, i) => {
      if (r[0].length == 23 && r[4] == rng.getValue()) {
        sh.getRange('F' + (+i + 4)).setValue(+sh.getRange('F' + (+i + 4)).getValue() + 1)
      }
    })
  }
}

Bonjour Steelson,

Merci, en soit cela rempli ma demande mais il y a un un problème majeur : le délai d'exécution.

En effet, je n'ai actuellement que 300 produits et pourtant il faut compter plusieurs secondes pour que le produit soit ajouté au stock ce qui amène un autre problème; si je scan plus d'un code barre toutes les 3 secondes, alors parfois ton code ne s'exécute pas et certaines unités ne sont pas entrées en stock...

As-tu une idée de comment améliorer la rapidité d'exécution ? Quitte a se que l'on repense un peu ou totalement notre gsheet pour cela.

Merci par avance,

Balek

Le délai d'exécution est essentiellement dû à la latence de ta connexion internet. En effet, les scripts sont exécutés (rapidement) côté serveur et le résultat est renvoyé.

Fais un test https://fast.com/fr/ et mets une copie d'écran du résultat. Il faudrait avoir au minimum 20Mbps

Je pense que c'est plutôt du a la taille de mon fichier (nombre de colonnes, lignes, MFC...)

Ci-dessous mon résultat sur "ma plus mauvaise connexion".

Wahoo, en effet ... donc le fichier est en cause.

Je vais proposer de faire la recherche par fonction et incrémenter les valeurs par le script.

Tu penses que si je passais par un excel en local pour les opérations d'entrée et sortie de stock, puis que j'importais le tout vers mon gsheet, ton code transformé en VBA serait quasi immédiat a s'exécuter ?

encore une fois, je suis ouvert a toute proposition pour arriver à mes fins, si je peux te simplifier la vie pour m'aider ^^

Je ne suis pas favorable au mélange Excel/Google Sheets. Je vais regarder l'autre solution dont je parlais.

En E6, mets cette formule

=MATCH(B1;E4:E;0)+3

Trie ta feuille sur la colonne A à partir de la ligne 4 (ce qui semble déjà être le cas)

le script devient

function onEdit(e) {
  var sh = e.source.getActiveSheet()
  var rng = e.source.getActiveRange()
  if (sh.getName() == 'CATAnew' && rng.getA1Notation() == 'B1' && rng.getValue() != '') {
    var row = rng.offset(0,3).getValue()
    sh.getRange('F' + row).setValue(+sh.getRange('F' + row).getValue() + 1)
  }
}

"En E6, mets cette formule" --> Tu veux dire en E1 on est d'accord ?

J'essaie dans l'heure, merci.

"

oui bien vu, on est d'accord

Bon, ça marche quand même mieux y'a pas photo mais toujours un peu long a exécuter ^^

Du coup j'ai "une solution" pour pallier ce problème :

Ajouter en ligne 1 une case me permettant de saisir une quantité pour les grosse entrée de stock ? (que je tape "10" et ça ajoute 10 au stock du produit).

Ainsi, mes gars n'auront pas de frustration a attendre quelques secondes pour les petites quantités scanables.

Aussi, pour que ton code soit viable pour nous;

- peux-tu le dupliquer mais cette fois ci-pour saisir des sorties (donc -1 au scan au lieu de +1 (une petite modif du code + une autre case de saisie d'EAN j'imagine ?)

- Après chaque scan d'EAN, la casé sélectionnée est B2 (logique), il faudrait que la case B1 soit resélectionnée automatiquement après exécution de ton code (je pense que je saurais faire mais j'ai peur que ton code ne s'exécute pas avant que la case B1 soit sélectionnée de nouveau)

D'avance, merci beaucoup !

P.S : on a + de 200 commandes à traiter demain et ma gestion de stock est actuellement HS...

ok, tu auras cela demain avant 8h

Prends une copie https://docs.google.com/spreadsheets/d/1vSHG2vaKXQNyNYnoHLaxyPCDhhVQkx7UaHL1NhwvG4Q/copy

Scanne en E1 pour les entrées (le curseur revient en E1)

Scanne en E3 pour les sorties (le curseur revient en E3)

Si plus de 1 unité, change auparavant la valeur entrée en C1 ou la valeur sortie en C3, elle revient à 1 automatiquement après le scan.

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  if (sh.getName() != 'CATAnew') return;
  var rng = e.source.getActiveRange();
  if (rng.getA1Notation() == 'E1' && rng.getValue() != '') {
    var row = rng.offset(0, 2).getValue();
    sh.getRange('F' + row).setValue(sh.getRange('F' + row).getValue() * 1 + rng.offset(0, -2).getValue() * 1)
    rng.offset(0, -2).setValue(1)
    rng.clearContent()
    sh.setActiveSelection('E1')
  }
  if (rng.getA1Notation() == 'E3' && rng.getValue() != '') {
    var row = rng.offset(0, 2).getValue();
    sh.getRange('G' + row).setValue(sh.getRange('G' + row).getValue() * 1 - rng.offset(0, -2).getValue() * 1)
    rng.offset(0, -2).setValue(1)
    rng.clearContent()
    sh.setActiveSelection('E3')
  }
  if (rng.getA1Notation() == 'C1') {sh.setActiveSelection('E1')}
  if (rng.getA1Notation() == 'C3') {sh.setActiveSelection('E3')}
}

en G1

=iferror(MATCH(E1;E6:E;0)+5)

et en G3

=iferror(MATCH(E3;E6:E;0)+5)

Autre possibilité ... ne pas faire de calcul en même temps que le scan. Dans ce cas, c'est simple, fais une feuille à une colonne pour les entrées et une feuille à une colonne pour les sorties et scanne les codes. On peut ensuite faire le traitement en bloc qui sera de reporter le nombre de scans dans l'onglet principal.

Salut Steelson,

Comment te dire que tu m'as sauvé la vie,

Merci BEAUCOUP pour ton travail rapide et super efficace.

Mes gars ont essayé et ils sont conquis :)

En revanche, j'aimerai bien voir (si c'est vite fait sinon tant pis) la solution dans laquelle tu "gardes en tampon" les scans pour que l'exécution soit absolument immédiate, puis que les entrée et sorties sont réintégrées dans un seconds temps avec calculs sur une autre feuille. Ma curiosité et la recherche de perfection...

Encore merci, sincèrement,

Balek

ok, réponse demain ...

L'idée est simplement la suivante : un onglet Entrées, un onglet Sorties avec une seule colonne et avec répétition des mêmes codes scannés le cas échéant

et un script qui va sur demande de l'opérateur déverser ensuite les quantités unitaires dans les colonnes de CATAnew

Bon je n'ai pas fait le script par manque de temps, mais si tu es intéressé je le ferai dans les prochains jours.

Tu pourrais déjà simuler ce que donnerai cette procédure en scannant, dans ce cas il est évident que c'est quasi-instantané.

Salut Steelson,

On est effectivement très chauds pour voir la solution avec calcul a part, oui.

Aussi, cela fait maintenant une semaine que l'on utilise ton code;

les produits que je vends se vendent par lot (6, 12, 24, etc.) Or il arrive plutôt fréquemment (genre dans 10-15% des cas) que ces quantités de lots ne correspondent pas à ce que l’on trouve dans le commerce (comme des bouteilles d’Evian vendues par 9 alors qu’on les trouve par lot de 6 dans le commerce.

Autant il nous est possible de gérer dans la pratique lors du colisage, autant d’un point de vue gestion informatique ça nous amène à un problème si on veut faire la gestion de notre stock via le scan des EAN.

On y a réfléchi avec un collègue et on a trouvé deux pistes possibles :

• Soit on adapte les sorties en créant des EAN personnalisés qui correspondra au produit entré (par exemple un EAN spécial pour correspondre à 1,5 lot de 6 bouteilles d’Evian, voire à 0,5)
• Soit on fait correspondre chaque EAN de lot à l’EAN unitaire du produit par un quotient (EAN lot de 6 bouteilles d’Evian) = 6 x (EAN d’une bouteille d’Evian)

Bien sûr si tu as de meilleures idées je reste ouvert à tes propositions.

Merci encore !!

Balek

Je ne sais pas si c'est une meilleure idée, mais dans ce cas on pourrait sans quitter le pistolet de scan, scanner d'abord l'EAN puis scanner une quantité à partir d'une grande feuille qui donnera en code barre les quantités usuelles.