Discussion :

[Jonhy7]

Bonjour à tous!

Je sollicite votre aide pour un problème de programmation. Je suis débutant et j'apprends à utiliser Javascript pour essentiellement l'utiliser sur Google Script afin de manipuler Google Sheet.

J'ai écrit quelques lignes pour réaliser une action simple : mettre une valeur dans une colonne B du tableur en fonction de la valeur d'une colonne A de ce même tableur. Donc tout simplement j'ai utilisé if. Le soucis est que j'ai à peu près 20000 lignes et même en utilisant un tableau pou ranger les valeurs, ça prends un temps interminable (ou ça me mets un truc comme Running Out)..

Voici mon code (google script):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
// Permet d'obtenir le nombre de ligne de ma feuille
 
function DerniereLigne() {
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Liste');
  var lignes = sheet.getLastRow();
  return lignes
}
 
// Permet d'affecter une valeur colonne B en fonction colonne A
function Tri() {
 
  var tab1 = []
  var tab2 = []
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.setActiveSheet(ss.getSheetByName('Liste'));
 
  for (var i =0,c=DerniereLigne()-1 ; i < c;i++){
    tab1.push(sheet.getRange("A"+ (i+2)).getValues());
    }
 
  for (var k =0;k<c;k++){
    if (tab1[k]=="Pomme") {
      tab2[k]="Fruit"
   } else if (tab1[k]=="Carotte") {
      tab2[k]="Légume"
    } else {
      tab2[k]="Rien"
    }
  }
 
  for (var j =0 ; j < c;j++){
     sheet.getRange("B"+ (j+2)).setValue(tab2[j]);
     }
}

Mon code marche très bien pour moins de 100 lignes. Si je commence a mettre 2000 ligne ça prends une bonne minutes mais pour 20000 ça a jamais réussi.. Avez vous une idée pour optimiser ce code ?

Je vous remercie d'avance !
A bientot

[CosmoKnacki]

À brûle-pourpoint, je dirais que tes deux tableaux ne sont pas utiles, et qu'il serait plus simple de faire une seule boucle au lieu de trois qui renseignerait la colonne B directement pour chaque valeur de la colonne A.

[Jonhy7]

En suivant tes conseils j'ai fais ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
// Permet d'obtenir le nombre de ligne de ma feuille
 
function DerniereLigne() {
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Liste');
  var lignes = sheet.getLastRow();
  return lignes
}
 
// Permet d'affecter une valeur colonne B en fonction colonne A
function Tri() {
 
// Initialisation du timer
  var startTime = new Date().getTime();
  var elapsedTime = 0;
 
  var tab1 = [];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.setActiveSheet(ss.getSheetByName('Liste'));
 
  for (var i =0,c=DerniereLigne()-1 ; i < c;i++){
    tab1.push(sheet.getRange("A"+ (i+2)).getValues());
     if (tab1[i]=="Pomme") {
      tab1[i]="Fruit"
    } else if (tab1[i]=="Carotte") {
      tab1[i]="Légume"
    } else {
      tab1[i]="Rien"
    }
    sheet.getRange("B"+ (i+2)).setValue(tab1[i]);
    }
 
  elapsedTime = new Date().getTime() - startTime;
  SpreadsheetApp.getUi().alert((elapsedTime/ 1000));
}

Sur le code que j'avais fais avant, pour une base de 2000 lignes ça prenant environs 130 secondes.

Là, avec ce code là, après 5 minutes c'était toujours en cours, et au bout de la moitié ca s'est arrété car c'était trop long (Durée d'exécution dépassé)
Avec ce code, ça affiche les cellules 1 par 1, donc on perds l'utilité de faire des tableaux, non ?

Après analyse, l'étape critique est celle où les cellules initiaux sont renseignés dans le tableau (tab1), avez vous une solution pour rentrer plus rapidement tous les valeurs dans une tableau ?

[ProgElecT]

Salut

Je suis encor apprenti dans le langage JavaScript, que donnerait

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
function Tri() {
 
// Initialisation du timer
  var startTime = new Date().getTime();
  var elapsedTime = 0;
 
  //var tab1 = [];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.setActiveSheet(ss.getSheetByName('Liste'));
 
  //for (var i =0,c=DerniereLigne()-1 ; i < c;i++){
  var RetValue ;
  var c=DerniereLigne()+1;
  var i = 2;
  for (; i < c;i++){
    //tab1.push(sheet.getRange("A"+ (i+2)).getValues());
    switch (sheet.getRange("A"+ (i)).getValues())
        {
            case "Pomme":
		RetValue = "Fruit";
		break;		
            case "Carotte":
		RetValue = "Légume";
		break;		
            default:
		RetValue = "Rien";
	}
	// if (tab1[i]=="Pomme") {
    //  tab1[i]="Fruit"
    //} else if (tab1[i]=="Carotte") {
    //  tab1[i]="Légume"
    //} else {
    //  tab1[i]="Rien"
    // }
    //sheet.getRange("B"+ (i+2)).setValue(tab1[i]);
    sheet.getRange("B"+ (i)).setValue(RetValue);
    }
 
  elapsedTime = new Date().getTime() - startTime;
  SpreadsheetApp.getUi().alert((elapsedTime/ 1000));
}

[Jonhy7]

Salut,

J'ai essayé ton code mais ça traite cellule par cellule et ça affiche les résultats au fur et a mesure dans la colonne B. Donc le temps d'exécution est très long, c'est pour cette raison que j'ai utilisé des tableau pour stocker les données.

[ProgElecT]

Salut,
.... et ça affiche les résultats au fur et a mesure dans la colonne B.

J'ai repris ton code post N° 3, qui lui aussi affiche au fur et à mesurei.

Dans un autre langage de programmation, pour largement améliorer la vitesse, je cachais/rendais invisible le grid durant les modifications de son contenu et le remettais visible après son actualisation, c'est peut-être une piste ...

[Jonhy7]

J'avais testé l'idée de CosmoKnacki, le code qui marche le mieux est le premier pour l'instant.

Effectivement, c'est ce que le programme (dans le poste n°1) fait, il garde en mémoire les valeurs dans le tableau et les affiches que à la fin.
Sous VBA, cette méthode marche bien, mais avec Javascript je galère..

[CosmoKnacki]

Après quelques essais, il semble que ce qui mine les performances soit les appels successifs à Sheet.getRange(), or tu l'utilises pour sélectionner chaque cellule pour chacune des colonnes, d'où la lenteur.

Ce n'est pas de cette manière qu'il faut utiliser cette méthode dans ce cas présent, mais plutôt en sélectionnant directement toutes les cellules dont on a besoin d'un coup (enfin de deux coups, une fois pour chaque colonne).

En plus on peut passer directement un ensemble de valeur à un objet Range via Range.setValues(). Donc adieu tableaux et boucles, il suffit de mapper les valeurs de la colonne A pour obtenir celles de la colonne B et de lui refourguer.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
function myFunction() {
    const categories = {
        corr:
            new Map().set( new Set(['Endive', 'Carotte', 'Blette', 'Poivron', 'Panais', 'Brocoli', 'Chou', 'Radis', 'Concombre', 'Oignon', 'Persil', 'Patate', 'Tomate', 'Topinambour', 'Asperge', 'Poireau']) , 'Légume' )
                     .set( new Set(['Pomme', 'Banane', 'Groseille', 'Pamplemousse', 'Kiwi', 'Prune', 'Ananas', 'Cerise', 'Raisin', 'Fraise', 'Poire', 'Orange', 'Citron']), 'Fruit' )
                     .set( new Set(['Marcassin', 'Hibou', 'Loutre', 'Lapin', 'Mulot', 'Panda', 'Lotte', 'Cochon', 'Perdrix', 'Ragondin', 'Poulpe', 'Mouton', 'Poule', 'Marmotte', 'Chien', 'Chat', 'Vache', 'Fouine', 'Âne', 'Pigeon', 'Moule']), 'Animal' )
                     .set( new Set(['Arbre', 'Herbe', 'Tabac']), 'Plante' )
                     .set( new Set(['Mercure', 'Venus', 'Terre', 'Mars', 'Jupiter', 'Saturne', 'Uranus', 'Neptune']), 'Planète' )
                     .set( new Set(['Zathras', 'Zaathras', 'Zathraas']), 'Zathras' ),
 
        'default': 'Rien',
 
        find: function (item) {
            for (let collection of this.corr.keys()) {
                if ( collection.has(item) )
                    return this.corr.get(collection);
            }
 
            return this['default'];
        }
    }
 
    const sheet = SpreadsheetApp.getActiveSpreadsheet(),
          firstRow = 3,
          lastRow = sheet.getLastRow(),
          rangeA = sheet.getRange(`A${firstRow}:A${lastRow}`),
          rangeB = sheet.getRange(`B${firstRow}:B${lastRow}`);
 
    rangeB.setValues(rangeA.getValues().map( n => [ categories.find(n[0]) ] ));
}

( ~ 5s pour 20.000 lignes)

Range.getValues() et Range.setValues() respectivement renvoient et reçoivent un tableau bidimensionnel.

[Jonhy7]

Tu es un génie CosmoKnacki, ça marche nickel ! merci !!

Mais une petite question si tu as le temps de répondre : comment marche ce qu'il y a dans la partie "const categories" surtout pour la partie "find".

J'aurais besoin d'utiliser ce code aussi pour des conditions ou fonctions plus poussés sur la colonne A, je n'y arrive pas trop, est-ce qu'il faut bien remplacer les new Set() par mes conditions avec des if ou autre conditions en fonction de la valeur lu ? enfin comment il lit la valeur de la cellule et le stocke dans quel variable ?

[CosmoKnacki]

À lire:

• Initialisateur d'objet
• Map (objet natif standard)
• Set
• Array.prototype.map()

J'ai supposé à partir de ton code initial que tu cherchais à associer des éléments (Veau, Vache, Cochon) à des catégories (Animal). J'ai donc créé cet objet categories, avec la propriété corr qui est un dictionnaire (Map) dont les clefs sont des ensembles (Set) associées à leurs valeurs, les catégories. Toujours à partir de ton code, j'ai ajouté la propriété default (que j'ai mis entre quotes car default est par ailleurs un mot-clef du langage) initialisée à 'Rien', pour les items n'appartenant à aucune catégorie.
Enfin, j'ai doté cet objet de la méthode find qui ne fait que chercher dans quel clef de corr se trouve l'item (à quel ensemble il appartient) pour renvoyer la valeur correspondante, ou, s'il n'y est pas, la valeur de default. Le paramètre item de la fonction correspond donc à la valeur dans une cellule de la colonne A et cette fonction renvoie la valeur pour la colonne B.

En m'en tenant stricto sensu à ton code initial, j'aurai pu écrire plus simplement:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
const categories = {
    corr = new Map([['Carotte', 'Légume'], ['Pomme', 'Fruit']]),
 
    'default': 'Rien',
 
    find: function(item) {
        if ( this.corr.has(item) )
            return this.corr.get(item);
 
        return this['default'];
    }
}

Voire même, j'aurais pu me passer de l'objet categories et utiliser directement la Map:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
const corr = new Map([['Carotte', 'Légume'], ['Pomme', 'Fruit']]),
      sheet = SpreadsheetApp.getActiveSpreadsheet(),
      firstRow = 3,
      lastRow = sheet.getLastRow(),
      rangeA = sheet.getRange(`A${firstRow}:A${lastRow}`),
      rangeB = sheet.getRange(`B${firstRow}:B${lastRow}`);
 
rangeB.setValues(rangeA.getValues().map( n => [ corr.get(n[0]) || 'Rien' ] ));

L'utilisation d'un objet Map n'est là que pour éviter des if else à n'en plus finir. De plus c'est beaucoup plus rapide.

Tout ça pour dire que cet objet categories avec ses propriétés et méthodes n'est là et a cette forme que pour poursuivre un but précis (regrouper tout ce qui est nécessaire pour associer un item à une catégorie). Si ton but est autre, tu n'en auras pas besoin ou tu auras besoin d'un tout autre objet ou d'une simple fonction.

[Jonhy7]

Ah d'accord, je comprends mieux comment ça marche

Merci beaucoup d'avoir pris le temps de répondre, mes tableurs seront plus rapides grâce à toi !

[Dave Hiock]

J'ai supposé à partir de ton code initial

Merci pour cette brillante explication qui manque trop souvent.

[Jonhy7]

Re bonjour,

Je me permets de "réouvrir" le sujet car je veux réaliser une autre opération sur mes colonnes et malgré mes recherches je n'y arrive pas..

Je voudrais juste affecter une valeur à la colonne R en fonction de la colonne A et B.
Par exemple : Si la Colonne A >= 50, ça m'affiche la valeur de la colonne B dans la colonne R ,si la colonne A < 50, ça affiche la colonne A, sinon ça affiche 'Rien'.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
function Comparaison() {
 
const sheet = SpreadsheetApp.getActiveSpreadsheet(),
      firstRow = 2,
      lastRow = sheet.getLastRow(),
      rangeA = sheet.getRange(`A${firstRow}:A${lastRow}`),
      rangeB = sheet.getRange(`B${firstRow}:B${lastRow}`),
      rangeR = sheet.getRange(`R${firstRow}:R${lastRow}`);
 
function test(item) {
  if (n >= 50) {
   return rangeB(n[0]);
   } else if (n < 50) {
   return rangeA(n[0]);
   } else
   return "Rien";
}
 
rangeR.setValues(rangeA.getValues().map(test));
 
}

Je pense que mon erreur vient de ma fonction test, est-ce cette fonction retourne bien les valeurs des autres colonnes et est-ce que ma façon de déclarer n est correcte ? ou dois je mettre un var devant le n ?

Merci !!

[NoSmoking]

Bonjour,

Je me permets de "réouvrir" le sujet car je veux réaliser une autre opération

Une discussion = une question Poser plusieurs questions dans un seul et même fil nuit grandement à la qualité du forum : notamment, lors d'une recherche, tout le contenu du fil n'est pas indiqué dans le titre, elle devient donc plus ardue. Notre but est de maintenir une base de connaissances facile à exploiter par tous les visiteurs, utilisable par tous. Vous avez pu bénéficier de ce forum en posant vos questions, laissez la possibilité aux suivants de bénéficier des réponses en leur offrant un sujet moins dispersé.

Merci

[Jonhy7]

Ah pas de soucis, je suis désolé.

J'ai crée une nouvelle discussion si ça intéresse quelqu'un : https://www.developpez.net/forums/d2.../#post11515777