Déclenchement automatique d'une fonction

Le 11/04/2022 à 15:13

Bonjour

je souhaiterais que ma fonction se déclenche automatiquement lorsqu'une cellule de la colonne G est remplie. Je sais qu'il faut utiliser On edit mais je n'arrive pas à la mettre en place

function test2(){ 
  var app= SpreadsheetApp;
  var feuille= app.getActiveSpreadsheet().getActiveSheet();
  var somme=feuille.getRange(2,2).getValue();
  var nombre=1;
  var moyenne=0;

  for (var i=2; i <= feuille.getLastRow(); i++){

    if((feuille.getRange(i-1,1).getValue() == feuille.getRange(i,1).getValue())) {

      somme= somme + feuille.getRange(i,2).getValue();
      nombre= nombre + 1;

    }
    if((feuille.getRange(i-1,1).getValue() != feuille.getRange(i,1).getValue())) {
       nombre =1;
       somme= feuille.getRange(i,2).getValue();

    }
    moyenne= somme/nombre;
    feuille.getRange(i,3).setValue(nombre);
    feuille.getRange(i,4).setValue(somme);
    feuille.getRange(i,5).setValue(moyenne);
    feuille.getRange(10,10).setValue(moyenne);
  }
}
Le 11/04/2022 à 16:12

Bonjour,

peux-tu partager un fichier, cela aide à y voir plus clair et mettre au point

pour partager https://www.sheets-pratique.com/fr/cours/partage

merci

Le 12/04/2022 à 09:32

Okay j'ai reussi à mettre la fonction on edit en place mais j'aimerais trouver un moyen pour que la fonction s'exécute plus rapidement, c'est à dire sans rescanner l'ensemble des valeurs à chaque fois, avez vous une idée pour faire cela?

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

Le 12/04/2022 à 13:58

En fait tu n'exploites pas le event ... mais peux-tu expliquer ce que doit faire cette fonction ?

Pour accélérer, il faut éviter les feuille.getRange() et les remplacer par un import global de la feuille comme feuille.getDataRange().getValues() et travailler sur cette pseudo-feuille virtuelle (les indices commencent ici à 0 alors que sur la feuille ils commencent à 1)

Le 12/04/2022 à 14:37

La fonction permet de calculer la moyenne du poids au fur et à mesure qu'on ajoute une ligne mais si la valeur de "ref" change alors la moyenne se remet à zero et recommence jusqu'au prochain changement de référence. Le problème est que je sais pas exploiter la fonction on edit et j'aimerais que la boucle for ne rescan pas à chaque fois toutes les valeurs pour que ce soit plus rapide mais je ne vois pas comment faire?

Le 13/04/2022 à 13:19

Pourquoi vouloir le faire avec un script ? et pourquoi pas avec une fonction ? faut-il encore y parvenir mais cela aurait l'avantage de ne pas solliciter le serveur à chaque nouvelle mesure.

Le 13/04/2022 à 14:37

je travaille sur une macro car après je vais rajouter une fonction qui permet d'afficher la date et l'heure automatiquement lorsque je rentre la référence et donc dans tous les cas je dois faire une macro. De plus, j'aimerais que la moyenne s'affiche dans une cellule seulement et qu'elle s'actualise au fur et à mesure qu'on ajoute des valeurs et je ne suis pas sure de reussir à mettre en place en fonction

Le 13/04/2022 à 16:17

ok je vais reprendre, mais, question

berangered a écrit :

Bonjour

je souhaiterais que ma fonction se déclenche automatiquement lorsqu'une cellule de la colonne G est remplie. }

il n'y a pas de données en G ?

Le 13/04/2022 à 17:18

Etant donné que les modifications peuvent intervenir en colonne A ou B, il faut en effet tout recalculer. J'ai pris une méthode plus speed.

function onEdit(event) {
  var feuille = event.source.getActiveSheet();
  var cellule = event.source.getActiveRange();
  if (feuille.getName() != 'test') { return }
  if (cellule.getColumn() > 2) { return }
  var data = feuille.getRange('A2:E' + feuille.getLastRow()).getValues()
  var somme = 0
  var nombre = 0
  var ref = ''
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] != ref) {
      somme = 0
      nombre = 0
      ref = data[i][0]
    }
    somme += data[i][1]
    nombre += 1
    data[i][2] = nombre
    data[i][3] = somme
    data[i][4] = somme / nombre
  }
  feuille.getRange('A2:E' + feuille.getLastRow()).setValues(data)
  feuille.getRange('J10').setValue(somme / nombre)
  SpreadsheetApp.getActive().toast('Données mises à jour !')
}
Le 14/04/2022 à 10:58

Ahh top !! mais j'ai un problème aucune valeur ne s'affiche dans J10

Le 14/04/2022 à 11:54

Forcément, les colonnes ne sont plus les mêmes !

Redonne un jeu de valeur même très réduit.

Le 14/04/2022 à 14:53

Ah c'est bon en fait ça fonctionne! Est ce que vous pouvez m'expliquer comment marche data[i][0] ? car je ne l'ai jamais vu auparavant

Le 14/04/2022 à 17:34

Quand tu charge des données dans un array (ici data) par

var data = feuille.getRange('A2:E' + feuille.getLastRow()).getValues()

les données de la feuille vont de 1 à 5 en colonne (A à E) et 1 à getLastRow() en ligne

dans data, les indices commencent à 0, donc 0 à 4 en colonne (second indice) et 0 à getLastRow()-1 en ligne

donc, quand tu écris

data[i][0]

tu fais appel à la première colonne, c'est-à-dire A ici qui est la référence

bien sur, si ton chargement est

var data = feuille.getRange('C2:G' + feuille.getLastRow()).getValues()
alors l'indice 0 var correspondre toujours à la première colonne qui sera la C
Le 15/04/2022 à 10:15

Merci beaucoup pour ces explications! J'ai essayé de modifier ce que vous m'avez donné pour que la moyenne se remette à zero si il y a changement de la date ou/et de la référence mais ça ne fonctionne pas car la moyenne se remet toujours à zero peut importe s'il y a changement ou non

https://docs.google.com/spreadsheets/d/129veaMpNrBfPM3TQXsiIGC_gzWutT547dGW7pYRmbtg/edit?usp=sharing

Je l'ai travaillé sur ce nouveau document

Le 15/04/2022 à 12:47

Pour mettre au point, surtout avec onEdit, n'hésite pas à mettre

  Browser.msgBox('ici')

et à le déplacer petit à petit pour voir où cela coince. C'est rustique mais efficace.

Tu as mis

 (feuille.getName() == pro)

c'est quoi la valeur de pro ? ou bien il faut mettre

 (feuille.getName() == 'pro')

Je suppose que c'est pour les test ..

if (feuille.getName() != 'test') { return }

mais dans ce cas cela va à l'oppose de

 (feuille.getName() == 'pro')

et rien ne se passe !

In fine, en mettant le nom de la feuille à pro, ceci fonctionne

function onEdit(event) {
  var pro = "test";
  var feuille = event.source.getActiveSheet();
  var cellule = event.source.getActiveRange();

  if ((cellule.getColumn() == 3) && (feuille.getName() == 'pro') && (cellule.getRow() > 1)) {

    if ((cellule.offset(0, -2).getValue() == '') && (cellule.offset(0, -1).getValue() == '')) {
      cellule.offset(0, -2).setValue(Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy"));
      cellule.offset(0, -1).setValue(Utilities.formatDate(new Date(), "GMT+2", "hh:mm"));
    }
  }
  // if (feuille.getName() != 'test') { return }
  if (cellule.getColumn() > 6) { return }

  var data = feuille.getRange('A2:G' + feuille.getLastRow()).getValues()

  var somme = 0
  var nombre = 0
  var ladate = ''
  var ref = ''

  for (var i = 0; i < data.length; i++) {

    if (data[i][0] != ladate) { // pas compris !
      somme = 0
      nombre = 0
      ladate = data[i][0]
    }

    if (data[i][2] != ref) {
      somme = 0
      nombre = 0
      ref = data[i][2]
    }

    somme += data[i][3]
    nombre += 1
    data[i][4] = nombre
    data[i][5] = somme
    data[i][6] = somme / nombre
  }
  feuille.getRange('A2:G' + feuille.getLastRow()).setValues(data)
  feuille.getRange('J2').setValue(somme / nombre)
  SpreadsheetApp.getActive().toast('Données mises à jour !')
}

par habitude j'ai changé date en ladate car Date est un nom réservé ... on ne sait jamais !

Le 15/04/2022 à 14:11

J'ai vu d'où venait mon erreur.

 if (data[i][0] != ladate) { 
      somme = 0
      nombre = 0
      ladate = data[i][0]
    }

c'est au niveau de cette partie. En effet, j'aimerais que si la date de la ligne précédente est différente que celle de la ligne actuelle alors la moyenne et la somme reviennent à zéro et que nombre soit égal à1. Du coup pour cela j'ai essayé de faire comme pour la variable 'ref'. Mais ça ne fonctionne pas car même si la date est la même que la ligne précédente , la moyenne se remet à zéro du coup je ne vois pas ce qui cloche

Le 15/04/2022 à 16:23

Oui, il y a un piège ... tes paramètres de script ne sont pas bons,

image

remplace par Europe/Paris

Donc si tu travailles le matin, google considère que c'est la veille car "il" se situe lui à New-York

Pour régler cela

https://forum.excel-pratique.com/sheets/google-script-remplissage-et-creation-quittances-puis-envoie...

Reste à tester !

Les dates, c'est hyper bien fait, hyper intelligent de la part de Google car cela permet bien de travailler en collaboratif. Par contre, il faut être soigneux dans les réglages, et ici c'est assez mesquin de la part de google.

Le 15/04/2022 à 16:28

Il vaut mieux aussi passer en

getDisplayValues

et pour les valeurs, les multiplier par 1 dans ce cas, sinon cela fait 1121 au lieu de 5 (1+1+2+1)

pour assurer le coup

function onEdit(event) {
  var pro = "test";
  var feuille = event.source.getActiveSheet();
  var cellule = event.source.getActiveRange();

  if ((cellule.getColumn() == 3) && (feuille.getName() == 'pro') && (cellule.getRow() > 1)) {

    if ((cellule.offset(0, -2).getValue() == '') && (cellule.offset(0, -1).getValue() == '')) {
      // on peut ne mettre que new Date() et mettre le format dans la feuille
      cellule.offset(0, -2).setValue(Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy"));
      cellule.offset(0, -1).setValue(Utilities.formatDate(new Date(), "GMT+2", "hh:mm"));
    }
  }
  // if (feuille.getName() != 'test') { return }
  if (cellule.getColumn() > 6) { return }

  var data = feuille.getRange('A2:G' + feuille.getLastRow()).getDisplayValues()

  var somme = 0
  var nombre = 0
  var ladate = ''
  var ref = ''

  for (var i = 0; i < data.length; i++) {

    if (data[i][0] != ladate) {
      somme = 0
      nombre = 0
      ladate = data[i][0]
    }

    if (data[i][2] != ref) {
      somme = 0
      nombre = 0
      ref = data[i][2]
    }

    somme += data[i][3]*1
    nombre += 1
    data[i][4] = nombre
    data[i][5] = somme
    data[i][6] = somme / nombre
  }
  feuille.getRange('A2:G' + feuille.getLastRow()).setValues(data)
  feuille.getRange('J2').setValue(somme / nombre)
  SpreadsheetApp.getActive().toast('Données mises à jour !')
}
Le 21/04/2022 à 10:14

Merci bcp !! ça fonctionne

Rechercher des sujets similaires à "declenchement automatique fonction"