Add-on google sheets, optimisation
Bonjour,
Je suis actuellement en train de développer un add-on pour google sheets, mais je trouve que le code s’exécute trop lentement a mon goût. Je viens donc demander votre avis pour optimiser la procédure .
BUT :
Le but est de trier certaines dates et de cacher les lignes inutiles avec la fonction hideRow(range) en appliquant un filtre.
Le filtre peux avoir plusieurs valeurs, mais dans ce cas-ci disons qu'il puisse demander une date inférieur, supérieur ou égale a une valeur indiquée par l'utilisateur.
MÉTHODE :
La méthode que j'emploie pour le moment est de lire les données du document via l'API google ( ce qui renvoie un double array ),
puis d'ajouter une colonne a la fin dans laquelle j'ajoute les résultat du filtre ( donc si la ligne est accepté par le filtre je met un 1, sinon un 0).
Ensuite j'applique la colonne au document, et je trie le document ( range.sort() ) par rapport a la colonne que je viens d'ajouter ( Donc toutes les lignes qui ont un "1" a la fin seront groupées ).
Puis je récupère les ranges à cacher.
Et finalement je cache toutes les lignes avec la valeur "0" avec la méthode sheet.hideRow(range)
EXPLICATIONS :
Je regroupe les valeurs dans le document car cela permet de toutes les cacher en même temps, sans devoir envoyer 200 requêtes individuelles au serveur ( google sheet ne permet de sélectionner qu'une seule range a la fois ce qui complique les choses, donc mieux vaut que les valeurs soit groupée pour que l'on puisse envoyer une grosse range plutôt que plusieurs petites ).
TEMPS D’EXÉCUTION :
Le temps d'exécution varie de 9 à 12 seconde sur un tableau de 8000 entrées ( cela dépends de si le serveur google est surchargé), ce qui est plutôt long.
SOLUTIONS TESTÉES :
J'ai déjà essayé de créer une copie du document coté serveur, mais le fait est que le document sera surement modifié constamment par le serveur ( pour d'autres raisons n'ayant rien a voir avec le problème ), et il serait plus long d'entretenir une version "up to date" du document coté serveur que de récupérer les données directement je penses.
Je suis obligé de regrouper les lignes acceptées car si le serveur devait cacher des ranges éparpillées cela pourrait prendre jusqu'à 300 secondes ( difficile d'attirer du monde si une procédure toute simple durait 5 minutes ).
CODE :
Le code est uniquement la comme complément, je ne penses pas qu'il soit utile pour résoudre le problème mais je comprends que certaines personnes veuillent le lire pour mieux comprendre la démarche.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
function filterByCondition(filter){
// Select the current sheet
var ss = SpreadsheetApp.getActiveSpreadsheet(); // The active spreadsheet
var sheet = ss.getSheets()[0]; // The active sheet
var dataRange = sheet.getDataRange().getA1Notation(); // Coordinates of the active range
var range = sheet.getRange("A2:" + dataRange.split(":")[1]); // Range of the active data
var values = range.getValues(); // Values of the active data
// Ici je demande à la fonction filter de me renvoyer un array contenant des "0" et des ""1" comme expliqué au dessus
var newValues = filter(filter, values);
// La fonction applyFilter s'occupe d'appliquer le colonne puis de trier et cacher les lignes contenant des "0"
// J'ajouterai une version simplifiée de cette fonction plus bas
var match = applyFilter(newValues, sheet, range);
// A ce stade on devrait avoir un document affichant uniquement ce qu'on lui a demandé.
return match;
} |
Voici une version simplifiée de la fonction applyFilter().
Code:
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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
/*
* Applies an array of 0's and 1's to the spreadsheet.
* If a row is worth 1 it will be kept.
* If a row is worth 0 it will be hidden.
*
* @param newValues[] - The array of 0's and 1's
* @param sheet - the active sheet
* @param range - the range of the active data ( not including title )
*
* @return boolean match - States if a match was found
*/
function applyFilter(newValues, sheet, range){
// Some variables to set up ranges during iteration over the binaryRange
var s = false; // States if the first coordinate of the range as initialized
var sVal = ""; // First coordinate of the range
var eVal = ""; // End coordiante of the range was initialized
var seRange = 0; // A range delimiter so that the sheet gets updated every _maxRowSelect rows
var binaryRange; // The range to set the binary valeus on ( usually Z column )
// Setting the binary values to the binary range
if(newValues.length > 0){
binaryRange = sheet.getRange("Z2:" + "Z" + (newValues.length + 1));
binaryRange.setValues(newValues);
} else {
return -1;
}
// Sort the binair column (groups 1's together )
filterAZ(25, range);
// Sort the binary values
var binaryValues = newValues.sort(function(a, b){return a[0]-b[0]});
// Get the sorted binary values
// var binaryValues = binaryRange.getValues();
// Iterate over binary values and hide the rows that don't match 1
for (var i = 0; i < binaryValues.length; i++) {
// Getting the current range ( row )
var currentValue = binaryValues[i][0];
// If the cell is blank, write the start coordinates if it's the first
// one, else update the end coordinates .
if(currentValue == 0){
if(s == false){
sVal = "A" + (i + 2).toString();
eVal = "A" + (i + 2).toString();
s = true; // Set s = true, to state the first range coordinate was initialized
seRange = 0;
} else {
eVal = "A" + (i + 2).toString();
seRange++;
}
if(i == (binaryValues.length - 1) || seRange == _maxRowSelect){
// Hides what doesn't match the condition and reset range coordinates
sheet.hideRow(sheet.getRange(sVal + ":" + eVal));
seRange = 0;
s = false;
}
// If the cell isn't 0, hide the built range ( if they where initialized )
} else {
if(s == true){
// Hides what doesn't match the condition and reset range coordinates
sheet.hideRow(sheet.getRange(sVal + ":" + eVal));
s = false; // Set s = false to state the first range coordinate isn't initialized anymore
}
}
}
return 1;
} |
Je remercie d'avance ceux qui auront le courage de lire mon problème, je ne m'attends pas à une réponse complète car je sais que cela peut prendre du temps, mais toute suggestion est le bien venu.
