Get data et suppression valeur prends du temps

Le 14/06/2021 à 09:18

Bonjour,

Je reviens demander de l'aide pour corriger une action qui prends du temps:

Lorsque qu'un Pc est en prêt, le script assigné au bouton "Save Loan Return" récupère les données de chaque cellules de la ligne du prêt concerné, efface ces mêmes cellules et les "colle" en incrémentant d'une ligne dans mon onglet "History".

Cela fonctionne, mais l'action qui consiste à récupérer les valeurs et à les supprimer avant des les coller dans mon autre onglet prends du temps "15-20s).

Le script concerne débute en ligne 444 du code.

Est-ce que j'utilise la bonne méthode et y aurait-il un moyen plus rapide d'effectuer ces actions?

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

Merci à tous

Le 14/06/2021 à 11:05

Bonjour,

Comme pour excel, si tu as beaucoup d'interactions avec ta feuille comme c'est le cas

ici

     LoanType=cell.offset(i, debcolwriteinfo-11).getValue();
     cell.offset(i, debcolwriteinfo-11).setValue(""); 
     CourseNb=cell.offset(i, debcolwriteinfo-10).getValue();    
     cell.offset(i, debcolwriteinfo-10).setValue("");    
     CourseFrom=cell.offset(i, debcolwriteinfo-9).getValue();
     cell.offset(i, debcolwriteinfo-9).setValue("");
     CourseTo=cell.offset(i, debcolwriteinfo-8).getValue();
     cell.offset(i, debcolwriteinfo-8).setValue("");
     CourseOther=cell.offset(i, debcolwriteinfo-7).getValue();
     cell.offset(i, debcolwriteinfo-7).setValue("");
     CourseLast=cell.offset(i, debcolwriteinfo-6).getValue();
     cell.offset(i, debcolwriteinfo-6).setValue("");     
     LoanTo=cell.offset(i, debcolwriteinfo-5).getValue();
     cell.offset(i, debcolwriteinfo-5).setValue("");     
     LoanToMail=cell.offset(i, debcolwriteinfo-4).getValue();
     cell.offset(i, debcolwriteinfo-4).setValue("");     
     LoanToRequestor=cell.offset(i, debcolwriteinfo-3).getValue();
     cell.offset(i, debcolwriteinfo-3).setValue("");      
     LoanToLoc=cell.offset(i, debcolwriteinfo-2).getValue();
     cell.offset(i, debcolwriteinfo-2).setValue("");      
     LoanAsset=cell.offset(i, debcolwriteinfo-1).getValue();  
     LoanMat=cell.offset(i, debcolwriteinfo).getValue();
     LoanStatus=cell.offset(i, debcolwriteinfo+4).getValue();
     cell.offset(i, debcolwriteinfo+4).setValue("IN STOCK");        
     LoanVer=cell.offset(i, debcolwriteinfo+6).getValue();
     cell.offset(i, debcolwriteinfo+6).setValue("");      
     LoanExpectDate=cell.offset(i, debcolwriteinfo+7).getValue();   
     cell.offset(i, debcolwriteinfo+7).setValue("");         
     LoanBundleComp=cell.offset(i, debcolwriteinfo+8).getValue(); 
     cell.offset(i, debcolwriteinfo+8).setValue("");    
     LoanBy=cell.offset(i, debcolwriteinfo+9).getValue();
     cell.offset(i, debcolwriteinfo+9).setValue("");         
     LoanDate=cell.offset(i, debcolwriteinfo+10).getValue();
     cell.offset(i, debcolwriteinfo+10).setValue("");      
     Comments=cell.offset(i, debcolwriteinfo+11).getValue(); 
     cell.offset(i, debcolwriteinfo+11).setValue("");

et ici

   cellHistory.offset(nextRow, FirstColWriteInfo-10).setValue(LoanType);
   cellHistory.offset(nextRow, FirstColWriteInfo-9).setValue(CourseNb);
   cellHistory.offset(nextRow, FirstColWriteInfo-8).setValue(CourseFrom);
   cellHistory.offset(nextRow, FirstColWriteInfo-7).setValue(CourseTo);
   cellHistory.offset(nextRow, FirstColWriteInfo-6).setValue(CourseOther);
   cellHistory.offset(nextRow, FirstColWriteInfo-5).setValue(CourseLast);
   cellHistory.offset(nextRow, FirstColWriteInfo-4).setValue(LoanTo);
   cellHistory.offset(nextRow, FirstColWriteInfo-3).setValue(LoanToMail);
   cellHistory.offset(nextRow, FirstColWriteInfo-2).setValue(LoanToRequestor);
   cellHistory.offset(nextRow, FirstColWriteInfo-1).setValue(LoanToLoc);
   cellHistory.offset(nextRow, FirstColWriteInfo).setValue(LoanAsset);
   cellHistory.offset(nextRow, FirstColWriteInfo+1).setValue(LoanMat);  
   cellHistory.offset(nextRow, FirstColWriteInfo+2).setValue(LoanVer);
   cellHistory.offset(nextRow, FirstColWriteInfo+3).setValue(LoanDate);
   cellHistory.offset(nextRow, FirstColWriteInfo+4).setValue(LoanExpectDate);
   cellHistory.offset(nextRow, FirstColWriteInfo+5).setValue(LoanBundleComp);
   cellHistory.offset(nextRow, FirstColWriteInfo+6).setValue(LoanBundleRecep);
   cellHistory.offset(nextRow, FirstColWriteInfo+7).setValue(LoanBy);  
   cellHistory.offset(nextRow, FirstColWriteInfo+8).setValue(Comments);

Il faut que tu mettes tout dans un tableau ("array") et que tu copies, effaces et colles tout en un seul bloc.

Par exemple, définis ta plage et tu manipules ensuite les données (ceci devrait remplacer toutes les lignes ci-dessus)

  var plage = cell.offset(i, debcolwriteinfo-11,1,19)
  var donnees = plage.getValues()
  plage.clearContents()
  cellHistory.offset(nextRow, FirstColWriteInfo-10,1,19).setValues(donnees)

fais un essai (je n'ai pas testé).

Fais le aussi pour function ResetForm()

Le 14/06/2021 à 14:49

Je n'ai pas encore testé, mais étant donné que ma feuille "PC List" est aussi ma feuille d'inventaire de matériel, ça va m'enlever une partie que je souhaite conserver (asset, material, brand, model, serial et status).

Ou alors, je fais en 2 fois (une première fois comportant les valeurs situées avant celles qui ne doivent pas bouger, et une seconde fois avec les valeurs situées après)?

Le 14/06/2021 à 15:24

Tu peux, une fois les données importées dans un array (donnees)

var donnees = plage.getValues()

affecter les items de l'array aux champs que tu souhaites ... comme il n'y a pas de connexion avec la page, tout le calcul sera fait rapidement sur le serveur.

Par exemple

var CourseFrom = donnees[2][0]

donnees sera toujours un array à 2 dimensions même si la seconde ne bouge pas; par ailleurs les indices commencent à 0

Le 17/06/2021 à 09:51

Bonjour,

Je n'ai pas réussi à m'en sortir (je suis débutant en App script - voire même en scripting tout court) :-(.

J'ai remis comme c'était pour que ça fonctionne (je précise que je fais les tests sur un autre fichier).

Pourriez-vous m'aider (au moins sur le début du script)?

Le 17/06/2021 à 11:09

D'abord, si je prends ceci

     var cell = sheetPCList.getRange('K1');
     LoanType=cell.offset(i, debcolwriteinfo-11).getValue();
     cell.offset(i, debcolwriteinfo-11).setValue(""); 
     CourseNb=cell.offset(i, debcolwriteinfo-10).getValue();    
     cell.offset(i, debcolwriteinfo-10).setValue("");    
     CourseFrom=cell.offset(i, debcolwriteinfo-9).getValue();
     cell.offset(i, debcolwriteinfo-9).setValue("");
     CourseTo=cell.offset(i, debcolwriteinfo-8).getValue();
     cell.offset(i, debcolwriteinfo-8).setValue("");
     CourseOther=cell.offset(i, debcolwriteinfo-7).getValue();
     cell.offset(i, debcolwriteinfo-7).setValue("");
     CourseLast=cell.offset(i, debcolwriteinfo-6).getValue();
     cell.offset(i, debcolwriteinfo-6).setValue("");
     LoanTo=cell.offset(i, debcolwriteinfo-5).getValue();
     cell.offset(i, debcolwriteinfo-5).setValue("");     
     LoanToMail=cell.offset(i, debcolwriteinfo-4).getValue();
     cell.offset(i, debcolwriteinfo-4).setValue("");     
     LoanToRequestor=cell.offset(i, debcolwriteinfo-3).getValue();
     cell.offset(i, debcolwriteinfo-3).setValue("");      
     LoanToLoc=cell.offset(i, debcolwriteinfo-2).getValue();
     cell.offset(i, debcolwriteinfo-2).setValue("");      
     LoanAsset=cell.offset(i, debcolwriteinfo-1).getValue(); 
     LoanMat=cell.offset(i, debcolwriteinfo).getValue();
     LoanStatus=cell.offset(i, debcolwriteinfo+4).getValue();
     cell.offset(i, debcolwriteinfo+4).setValue("IN STOCK");        
     LoanVer=cell.offset(i, debcolwriteinfo+6).getValue();
     cell.offset(i, debcolwriteinfo+6).setValue("");      
     LoanExpectDate=cell.offset(i, debcolwriteinfo+7).getValue();   
     cell.offset(i, debcolwriteinfo+7).setValue("");         
     LoanBundleComp=cell.offset(i, debcolwriteinfo+8).getValue(); 
     cell.offset(i, debcolwriteinfo+8).setValue("");    
     LoanBundleRecep=cell.offset(i, debcolwriteinfo+9).getValue(); 
     cell.offset(i, debcolwriteinfo+9).setValue("");    
     LoanBy=cell.offset(i, debcolwriteinfo+10).getValue();
     cell.offset(i, debcolwriteinfo+10).setValue("");         
     LoanDate=cell.offset(i, debcolwriteinfo+11).getValue();
     cell.offset(i, debcolwriteinfo+11).setValue("");      
     Comments=cell.offset(i, debcolwriteinfo+12).getValue(); 
     cell.offset(i, debcolwriteinfo+12).setValue("");

cela doit se résumer à :

//import des données
    var donneesActuelles = sheetPCList.getRange(1,1,1,23).getValues();
//effacement
    sheetPCList.getRange(1,1,23,1).clearContents();
    LoanType = donneesActuelles[0][0]
    CourseNb = donneesActuelles[0][1]
    CourseFrom = donneesActuelles[0][2]
    CourseTo = donneesActuelles[0][3]
etc.

fais un essai, il faut peut-être régler les paramètres? Sans données et sans m'être approprié le logigramme c'est difficile de tester

Une règle (pas absolue) est d'utiliser la notation Camel ou Dromadaire ... les variables commencent avec une minuscule et peuvent comporter des majuscules au milieu pour conserver la lisibilité si la variable est nommée de façon significative ... exemple donneesActuelles

Avec cela, on a vu l'importation de données et l'effacement.

On verra ensuite l'export de données du script vers la feuille.

Le 17/06/2021 à 15:14

Merci beaucoup!!! Je vais reprendre ça ce soir la tête reposée et je vous fais un retour demain.

J'espère y arriver sans trop de mal

Le 17/06/2021 à 15:58

Cela va prendre quelques jours, mais il faut que je fasse un fichier didactique qui montre en même temps les syntaxes d'écriture/lecture, et les performances en temps.

Le 18/06/2021 à 08:56

Un exemple : j'inverse le texte (verlan) ou le signe s'il s'agit de nombres dans une matrice de 10x10.

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

Le temps d'exécution est divisé par ~100

function onOpen() {
  var menuEntries = [ {name: "test method 1", functionName: "changeValues"},
                      {name: "test method 2 (array)", functionName: "changeValuesByArray"}
                     ];
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  sh.addMenu("** run tests **",menuEntries);
}

function changeValues(){
  var time = new Date().getTime();
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  for (var i=3;i<=12;i++){
    for (var j=2;j<=11;j++){
      if(isNaN(sh.getRange(i,j,1,1).getValue())){
        sh.getRange(i,j,1,1).setValue(swap(sh.getRange(i,j,1,1).getValue()))
      }else{
        sh.getRange(i,j,1,1).setValue(-sh.getRange(i,j,1,1).getValue())
      }
    }
  }
  Browser.msgBox('duration = '+Number(new Date().getTime()-time)+' mS');
}

function changeValuesByArray(){
  var time = new Date().getTime();
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sh.getRange(3,2,10,10).getValues()
  for (var i=0;i<10;i++){
    for (var j=0;j<10;j++){
      if(isNaN(data[i][j])){
        data[i][j] = swap(data[i][j])
      }else{
        data[i][j] = -data[i][j]
      }
    }
  }
  sh.getRange(3,2,10,10).setValues(data)
  Browser.msgBox('duration = '+Number(new Date().getTime()-time)+' mS');
}

function swap(txt){
  var verlan=''
  for (var i=txt.length;i>0;i--){
    verlan=verlan+txt.substring(i-1,i)
    Logger.log(i + ' ' + txt.substring(i-1,i) + ' ' + verlan)
  }
  return verlan
}

je passe de 15 secondes à une fraction d'une seconde

Le 18/06/2021 à 10:58

En fait c'est la suppression des valeurs des cellules de la ligne concernée qui prends tout le temps...

Je lui demande donc de faire le "getData" des infos de la ligne concernée dans la sheet "PC List" , je les "setData" dans ma feuille "History" et pour terminer c'est là que je lance la suppression de mes valeurs dans "PC List".

Cependant, avec la partie de script dédiée à cela:

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

//effacement

sheetPCList.getRange(1,1,23,1).clearContents();

LoanType = donneesActuelles[0][0]

CourseNb = donneesActuelles[0][1]

CourseFrom = donneesActuelles[0][2]

CourseTo = donneesActuelles[0][3]
---------------------------------------------------------------------------------

je pense que je me suis loupé dans la syntaxe car cela ne fonctionne pas....

Et du coup il faudrait que je le fasse en 2 fois (avant et après ma la colonne comprenant les cellules qui déterminent la ligne concernée - donc ma colonne K).

Sorry mais là je me perds un peu dans la manip... :--(

Au moins j'ai de la marge de progression ;-)

Le 18/06/2021 à 11:19
enermax a écrit :

En fait c'est la suppression des valeurs des cellules de la ligne concernée qui prends tout le temps...

est-ce que les données sont disséminées ?

Le 18/06/2021 à 11:38

Oui et non... elles sont toutes sur la même ligne.

Cependant, la ligne varie en fonction de l'asset (identifiant du PC) concerné et qui est choisi, et les valeurs à supprimer sur la ligne concernées sont dans mon cas de la colonne A à J et de Q à Z. les colonnes situées entre J et Q ne doivent pas être modifiées/supprimées (à part le statut en colonne P, mais peu importe)

Le 21/06/2021 à 09:20

J'ai réussi mais à moitié ;-)

Du coup, je procède en 2 fois (suppression des valeurs avant la colonne ASSET et après la colonne "STATUS").

Ca fonctionne et c'est bien plus rapide! Merci de m'avoir mis sur la voie!

Cependant (eh oui), j'ai un souci: ça supprime bien les données....mais de la ligne immédiatement au dessus de celle qui est concernée :-(

Ceci est dû au fait que mon "i" a pour valeur 0:

  var readingGender = sheetPCList.getRange("K1:K2540").getValues();
  var lastRow = sheetPCList.getLastRow();
  var debcolwriteinfo=1;

  for  (var i=0; i < lastRow; i++) {
   if (readingGender[i][0] == LoanAsset){
     var cell = sheetPCList.getRange('K1');
     LoanType=cell.offset(i, debcolwriteinfo-11).getValue();
     CourseNb=cell.offset(i, debcolwriteinfo-10).getValue();      
     CourseFrom=cell.offset(i, debcolwriteinfo-9).getValue();
     CourseTo=cell.offset(i, debcolwriteinfo-8).getValue();
     CourseOther=cell.offset(i, debcolwriteinfo-7).getValue();
     CourseLast=cell.offset(i, debcolwriteinfo-6).getValue();    
     LoanTo=cell.offset(i, debcolwriteinfo-5).getValue();
     LoanToMail=cell.offset(i, debcolwriteinfo-4).getValue(); 
     LoanToRequestor=cell.offset(i, debcolwriteinfo-3).getValue();
     LoanToLoc=cell.offset(i, debcolwriteinfo-2).getValue();
     LoanAsset=cell.offset(i, debcolwriteinfo-1).getValue();  
     LoanMat=cell.offset(i, debcolwriteinfo).getValue();
     LoanStatus=cell.offset(i, debcolwriteinfo+4).getValue();
     cell.offset(i, debcolwriteinfo+4).setValue("IN STOCK");        
     LoanVer=cell.offset(i, debcolwriteinfo+6).getValue();   
     LoanExpectDate=cell.offset(i, debcolwriteinfo+7).getValue();         
     LoanBundleComp=cell.offset(i, debcolwriteinfo+8).getValue(); 
     LoanBy=cell.offset(i, debcolwriteinfo+10).getValue();      
     LoanDate=cell.offset(i, debcolwriteinfo+11).getValue();      
     Comments=cell.offset(i, debcolwriteinfo+12).getValue();   
    } else {
  }
}
sheetPCList.getRange("A"+i+":J"+i).clearContent();
sheetPCList.getRange("Q"+i+":X"+i).clearContent();

Comment puis-je corriger cela? En faisant une variable supplémentaire = à i++ (ex: var j = i++)?

Le 21/06/2021 à 09:36

ou bien tu crées une variable

var ii = i+1

ou bien pour la fin du script

sheetPCList.getRange("A"+(i+1)+":J"+(i+1)).clearContent();
sheetPCList.getRange("Q"+(i+1)+":X"+(i+1)).clearContent();

en effet, les variables tableaux comment à 0 et les lignes à 1

Le 21/06/2021 à 11:27

Impeccable, ça fonctionne nickel!

Mille mercis pour votre aide précieuse!

Rechercher des sujets similaires à "get data suppression valeur prends temps"