Sommation colonne sous condition avec script

Le 11/06/2022 à 17:26

Bonjour ,

Je rencontre une problématique avec un script que j'ai écrit. ce script fait la somme de x colonnes de n tableaux distincts

J'ai effectué plusieurs simulation de ce script afin de vous exposer cette problématique :

- Vérification de la colonne date de référence, qui est la colonne A de la feuille de calcul.

A partir de cette colonne de référence, une comparaison sera effectuée avec les dates contenues dans les n tableaux suivants voir fichier.

 for (let i=0;i<datacolumnDate_fix.length-3;i++) 
    {
      console.log(Utilities.formatDate(new Date(datacolumnDate_fix[i][0]),"GMT","dd-MM-YYYY"));
    }

on retrouve bien les x bonnes dates dans cette colonne de référence.

______________________________________________________

Verification des dates contenues dans les n tableaux:

En faisant cette simulation, on retrouve les bonnes dates pour chacun des n tableaux, il y a une exception lorsque la colonne date d'un des tableaux est vide. La fonction Utilities.formatDate(new date (" vide")) retourne une valeur erronée de type date 01-01-1970.

for (let i=0;i<datacolumnDate_fix.length-3;i++) 
    {
      //console.log(Utilities.formatDate(new Date(datacolumnDate_fix[i][0]),"GMT","dd-MM-YYYY"));
      // Boucle de parcours des colonne 
        for (let j=0;j<28;j++) 
        {
          //console.log("val de j="+j);
          console.log(Utilities.formatDate(new Date(datacolumnDate_Value[i][myNUMCol]),"GMT","dd-MM-YYYY"),"  oooooo "+j);
           myNUMCol=myNUMCol+4;
         }
    }

Je pense que cette valeur erronée, est à l'origine du problème suivant :

------------------------------------------------------------------------------------------

Vérification de la boucle interne sous condition avec cette partie de code

for (let i=0;i<datacolumnDate_fix.length-3;i++) 
    {
      //console.log(Utilities.formatDate(new Date(datacolumnDate_fix[i][0]),"GMT","dd-MM-YYYY"));
      // Boucle de parcours des colonne 
        for (let j=0;j<28;j++) 
        {
          //console.log("val de j="+j);
          //console.log(Utilities.formatDate(new Date(datacolumnDate_Value[i][myNUMCol]),"GMT","dd-MM-YYYY"),"  oooooo "+j);

            //Comparaison date ref et date colonne data
          if(Utilities.formatDate(new Date(datacolumnDate_Value[i][myNUMCol]),"GMT","dd-MM-YYYY")==
              Utilities.formatDate(new Date(datacolumnDate_fix[i][0]),"GMT","dd-MM-YYYY"))
                {
                    //datacolumnDate_Value = sheet.getRange(4,myNUMCol,sheet.getLastRow(),1).getValues();
                    console.log(datacolumnDate_Value[i][myNUMCol+1]," oooooo "+j);

                    //sommation de l'ensemble des valeurs d ligne
                    //mySunValue=mySunValue+datacolumnDate_Value[i][myNUMCol+1];
                   //Selecteur de tableau n
                    myNUMCol=myNUMCol+4;
                }
        }

Lorsque je simule cette autre partie du script, afin d'afficher les valeurs dont je dois faire la somme, je constate que la boucle sur les 28 tableaux existants s'arrête à 6, elle affiche les valeurs pour j allant de 0 jusqu'à 6 et pour j allant de 7 à 27 rien ne se passe. Cela n'est pas normal, sachant les deuxvérifications précédentes des dates.

Je pense que le fait d'avoir une valeur erronée qui est retournée au niveau de cette condition en est la cause, mais sachant que c'est une date, il ne devrait pas y avoir d'erreur, ni d'arrêt de la boucle, je ne comprends pas trop.

Voici la condition :

if(Utilities.formatDate(new Date(datacolumnDate_Value[i][myNUMCol]),"GMT","dd-MM-YYYY")==
              Utilities.formatDate(new Date(datacolumnDate_fix[i][0]),"GMT","dd-MM-YYYY"))
                {

Pouvez s'il vous plait m'aider sur cette problématique, le reste du script fonctionne très bien, il y a juste cet arrêt qui est anormal. voici un fichier exemple :

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

Je précise aussi que toute les colonnes date ne commence pas à la même date et on souhaite faire une sommation par date d'où ma condition.

Merci d'avance



Le 12/06/2022 à 03:23

Bonjour,

j'étais fixé là-dessus

const datacolumnDate_fix = sheet.getRange(4, 1, sheet.getLastRow(), 1).getValues();

généralement, on met la longueur moins le point de départ, c'est-à-dire

const datacolumnDate_fix = sheet.getRange(4, 1, sheet.getLastRow() - 3, 1).getValues();

mais in fine tu corriges en mettant les -3 dans la boucle, donc ce n'est cela la cause

si tu considères que c'est bien la valeur absente qui est la cause (on voit que la colonne AC est complètement vierge, alors fais un test sur la valeur avant de faire la boucle

  for (let i = 0; i < datacolumnDate_fix.length - 3; i++) {
    for (let j = 0; j < 28; j++) {
      if (datacolumnDate_Value[i][myNUMCol]!=''){
        // ici je réalise la comparaison et le calcul
      }
Le 12/06/2022 à 05:39

La raison est que tu ne balayes pas toutes les colonnes !

for (let j=0;j<28;j++)

Comme il s'agit de groupes de 4 colonnes ... essaie

for (let j=0;j<4*28;j+=4)
Le 12/06/2022 à 15:34

Bonjour,

Oui, j'ai effectivement constaté qu'il me manquait une condition pour couvrir le fait que la colonne date soit vide:

si tu considères que c'est bien la valeur absente qui est la cause (on voit que la colonne AC est complètement vierge, alors fais un test sur la valeur avant de faire la boucle

for (let i = 0; i < datacolumnDate_fix.length - 3; i++) {
for (let j = 0; j < 28; j++) {
if (datacolumnDate_Value[i][myNUMCol]!=''){
// ici je réalise la comparaison et le calcul
}

Au lieu de la condition, j'ai trouvé en cherchant un peu hier celle-ci et j'ai rajoute un else sur le test des dates

 if (datacolumnDate_Value[i][myNUMCol] == "") 
          {
             mySunValue=mySunValue;
             myNUMCol=myNUMCol+4;
          }

La raison est que tu ne balayes pas toutes les colonnes !

C'est exacte, je ne balaye pas toute les colonne de la feuille de calcul, je réalise des sauts de colonne multiple de 4 a l'aide de cette variable qui pointe sur ma première colonne de la feuille de calcul.

 myNUMCol=myNUMCol+4;

Pour la somme, j'utilise exactement le même principe, si je veux par exemple sommer la colonne 4 de chaque tableaux, j'utilise :

myNUMCol+3

Avec cette technique, je pourrai peut être ajouté le numéro de colonne à sommer en paramètre.

Je rencontre une nouvelle problèmatique :

A l'origine, mon but de réaliser la sommation en fonction de la date, et donc pour une date donnée, je dois pouvoir retrouver toutes les valeurs correspondantes à cette date et effectuer la sommation.

Sachant que j'ai des tableaux où la colonne "date" ne sont pas de même dimension et pouvant commencer par des dates différentes des autres en fonction de la donnée chargé, pour exemple les tableaux repérés par T, X, Z, donc les valeurs sont non pris en compte dans mon calcul, voir fichier.

Mon script mise à jour effectue la sommation mais ne prend pas en compte cette spécificité.

J'aimerais rajouter cette spécificité au script actuel. Pouvez m'aidez à le faire?

j'ai mise à jour le fichier envoyé.

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

l'onglet "compare" montre la sommation fait avec VLookup et la sommation avec le script , ainsi le résultat de leur comparaison. On voit qu'à partir de la date 22/10/2021, on est NOK.

Merci d'avance,

Le 12/06/2022 à 16:52

Effectivement, comme tu parcours chaque ligne et ensuite tu regard dans la ligne la même date, si celle-ci est décalée, elle est oubliée !

Personnellement, j'aurais mis bout à bout les données dans une longue liste de 4 colonnes, donc E4:H sous A4:D, et les autres à la suite.

J'aurais ensuite cherché toutes les dates uniques

Et j'aurais enfin somme les valeurs pour chaque date trouvée.

Le 12/06/2022 à 17:31

Voici une proposition de script

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hist.Taux");
  var data = sheet.getRange('A4:DH' + sheet.getLastRow()).getValues()

  // map va constituer une sorte de répertoire de dates uniques avec des valeurs
  // 3 méthodes : .set , .has , .get
  var dates = new Map()
  for (col = 0; col < data[0].length; col += 4) {
    for (ligne = 0; ligne < data.length; ligne++) {
      if (data[ligne][col] != '') {
        ladate = Utilities.formatDate(new Date(data[ligne][col]), "GMT+1", "yyyy-MM-dd")
        if (dates.has(ladate)) {
          console.log(ladate)
          dates.set(ladate, dates.get(ladate) + data[ligne][col + 3])
        }
        else {
          console.log('new ' + ladate)
          dates.set(ladate, data[ligne][col + 3])
        }
      }
    }
  }

  var sommes = []
  // on mets ici le répertoire en tableau que l'on va trier
  dates.forEach(function (value, key) { sommes.push([key, value]) })
  sommes = (sommes.sort())

  // on transfère
  const comp = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("compare");
  comp.getRange('G2:H' + (sommes.length + 1)).setValues(sommes)
}
Le 13/06/2022 à 08:10

Bonjour,

je vous remercie pour le script.

j'ai effectué un test et j'ai cette erreur

capture d ecran 2022 06 13 a 07 56 04

C'est lors de l'écriture dans le feuille de calcul "compare", la dernière ligne du script.

Le 13/06/2022 à 10:28

je l'ai fait tourner sur une copie du fichier que tu as donné et cela fonctionne parfaitement

Au passage, supprime les 2 lignes console.log pour aller plus rapidement

image
Le 13/06/2022 à 14:22

Ok, merci beaucoup

Rechercher des sujets similaires à "sommation colonne condition script"