Bloquage sur le nombre de tache que supporte le programme
C
Bonjour,
J'ai réalisé un programme pour faire un copier coller de feuille sur sheets. J'effectue cette action pas/pas et sur 6 feuille.
"i" qui correspond à ma valeur de ligne et que j'incrémenté me pousse problème. Si je veux faire des copier/coller de 5 lignes mon programme fonctionne mais 10, 20 lignes il se stop en court comme si il avait atteint ses limites. Cependant mon objectif "était au minimum 50 linges
Une idée de comment je pourrais faire.
function onEdit(event) {
var bbb = SpreadsheetApp.getActiveSheet();
var cellule = SpreadsheetApp.getActiveRange();
var m = 20
if (bbb.getName() == 'ORDO/SEMAINE' && cellule.getColumn() == 1 && cellule.getRow() == 3 && cellule.getValue() != '') {
var w=1;
while (w<19){
bbb.getRange('A3').setValue("Préparation de modification");
var u = 1;
for (k = 1; k <= m; k++) {//COPIER/COLLER KFR
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KFR');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KFR/MASK');
var aaaref = aaa.getRange(k, 1).getValue(); //Prend les valeur sur la page KFR
var aaadate = aaa.getRange(k, 4).getValue();
var aaaHD = aaa.getRange(k, 5).getValue();
var aaaHF = aaa.getRange(k, 6).getValue();
var aaarup = aaa.getRange(k, 7).getValue();
aaamask.getRange(u, 1).setValue(aaaref);//Je met les valeur copier en KFR en KFRMASK
aaamask.getRange(u, 2).setValue(aaadate);
aaamask.getRange(u, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 5).setValue(aaarup);
u = u + 1;
}
u = 1;
for (k = 1; k <= m; k++) {//COPIER/COLLER KAL
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KAL');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KAL/MASK');
var aaaref = aaa.getRange(k, 2).getValue();
var aaadate = aaa.getRange(k, 5).getValue();
var aaaHD = aaa.getRange(k, 6).getValue();
var aaaHF = aaa.getRange(k, 7).getValue();
var aaarup = aaa.getRange(k, 8).getValue();
aaamask.getRange(u, 1).setValue(aaaref);
aaamask.getRange(u, 2).setValue(aaadate);
aaamask.getRange(u, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 5).setValue(aaarup);
u = u + 1;
}
u = 1;
for (k = 1; k <= m; k++) {//COPIER/COLLER BHX
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BHX');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BHX/MASK');
var aaaref = aaa.getRange(k, 2).getValue();
var aaadate = aaa.getRange(k, 5).getValue();
var aaaHD = aaa.getRange(k, 6).getValue();
var aaaHF = aaa.getRange(k, 7).getValue();
aaamask.getRange(u, 1).setValue(aaaref);
aaamask.getRange(u, 2).setValue(aaadate);
aaamask.getRange(u, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
u = u + 1;
}
u = 1;
for (k = 1; k <= m; k++) {//COPIER/COLLER KOCH
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KOCH');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KOCH/MASK');
var aaaref = aaa.getRange(k, 2).getValue();
var aaadate = aaa.getRange(k, 5).getValue();
var aaaHD = aaa.getRange(k, 6).getValue();
var aaaHF = aaa.getRange(k, 7).getValue();
aaamask.getRange(u, 1).setValue(aaaref);
aaamask.getRange(u, 2).setValue(aaadate);
aaamask.getRange(u, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
u = u + 1;
}
var z = 1;
for (q = 1; q <= m; q++) {//COPIER/COLLER BTP
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BTP');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BTP/MASK');
var aaaref = aaa.getRange(q, 2).getValue();
var aaadate = aaa.getRange(q, 5).getValue();
var aaaHD = aaa.getRange(q, 6).getValue();
var aaaHF = aaa.getRange(q, 7).getValue();
var aaarup = aaa.getRange(q, 8).getValue();
aaamask.getRange(z, 1).setValue(aaaref);
aaamask.getRange(z, 2).setValue(aaadate);
aaamask.getRange(z, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(z, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(z, 5).setValue(aaarup);
z = z + 1;
}
var w = 1;
for (y = 1; y <= m; y++) {//COPIER/COLLER BTS
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BTS');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BTS/MASK');
var aaaref = aaa.getRange(y, 2).getValue();
var aaadate = aaa.getRange(y, 5).getValue();
var aaaHD = aaa.getRange(y, 6).getValue();
var aaaHF = aaa.getRange(y, 7).getValue();
var aaarup = aaa.getRange(y, 8).getValue();
aaamask.getRange(w, 1).setValue(aaaref);
aaamask.getRange(w, 2).setValue(aaadate);
aaamask.getRange(w, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(w, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(w, 5).setValue(aaarup);
w = w + 1;
}
}
bbb.getRange('A3').setValue("Modification possible");
Browser.msgBox("Modification possible");
}
}
Bonjour,
quelle est la raison du blocage du programme ? est-ce que Google affiche par exemple un dépassement de temps d'exécution ?
j'ai 2 remarques à la lecture
- tu fais une boucle while sur w
while (w<19)
mais tu sembles réinitialiser w puisque je vois 2 fois dans le programmevar w=1
- il est toujours préférable de faire des copies globales et non cellule par cellule, à condition qu'elles soient contigües mais cela semble être souvent le cas; cela simplifie aussi le fait de devoir faire certaines boucles
Bonjour,
function onEdit(event) {
var bbb = SpreadsheetApp.getActiveSheet();
var cellule = SpreadsheetApp.getActiveRange();
var m = 20
if (bbb.getName() == 'ORDO/SEMAINE' && cellule.getColumn() == 1 && cellule.getRow() == 3 && cellule.getValue() != '') {
w = w + 1
while (w<19){
bbb.getRange('A3').setValue("Préparation de modification");
var u = 1;
for (k = 1; k <= m; k++) {//COPIER/COLLER KFR
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KFR');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KFR/MASK');
var aaaref = aaa.getRange(k, 1).getValue(); //Prend les valeur sur la page KFR
var aaadate = aaa.getRange(k, 4).getValue();
var aaaHD = aaa.getRange(k, 5).getValue();
var aaaHF = aaa.getRange(k, 6).getValue();
var aaarup = aaa.getRange(k, 7).getValue();
aaamask.getRange(u, 1).setValue(aaaref);//Je met les valeur copier en KFR en KFRMASK
aaamask.getRange(u, 2).setValue(aaadate);
aaamask.getRange(u, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 5).setValue(aaarup);
u = u + 1;
}
u = 1;
for (k = 1; k <= m; k++) {//COPIER/COLLER KAL
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KAL');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KAL/MASK');
var aaaref = aaa.getRange(k, 2).getValue();
var aaadate = aaa.getRange(k, 5).getValue();
var aaaHD = aaa.getRange(k, 6).getValue();
var aaaHF = aaa.getRange(k, 7).getValue();
var aaarup = aaa.getRange(k, 8).getValue();
aaamask.getRange(u, 1).setValue(aaaref);
aaamask.getRange(u, 2).setValue(aaadate);
aaamask.getRange(u, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 5).setValue(aaarup);
u = u + 1;
}
u = 1;
for (k = 1; k <= m; k++) {//COPIER/COLLER BHX
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BHX');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BHX/MASK');
var aaaref = aaa.getRange(k, 2).getValue();
var aaadate = aaa.getRange(k, 5).getValue();
var aaaHD = aaa.getRange(k, 6).getValue();
var aaaHF = aaa.getRange(k, 7).getValue();
aaamask.getRange(u, 1).setValue(aaaref);
aaamask.getRange(u, 2).setValue(aaadate);
aaamask.getRange(u, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
u = u + 1;
}
u = 1;
for (k = 1; k <= m; k++) {//COPIER/COLLER KOCH
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KOCH');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KOCH/MASK');
var aaaref = aaa.getRange(k, 2).getValue();
var aaadate = aaa.getRange(k, 5).getValue();
var aaaHD = aaa.getRange(k, 6).getValue();
var aaaHF = aaa.getRange(k, 7).getValue();
aaamask.getRange(u, 1).setValue(aaaref);
aaamask.getRange(u, 2).setValue(aaadate);
aaamask.getRange(u, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(u, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
u = u + 1;
}
var z = 1;
for (q = 1; q <= m; q++) {//COPIER/COLLER BTP
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BTP');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BTP/MASK');
var aaaref = aaa.getRange(q, 2).getValue();
var aaadate = aaa.getRange(q, 5).getValue();
var aaaHD = aaa.getRange(q, 6).getValue();
var aaaHF = aaa.getRange(q, 7).getValue();
var aaarup = aaa.getRange(q, 8).getValue();
aaamask.getRange(z, 1).setValue(aaaref);
aaamask.getRange(z, 2).setValue(aaadate);
aaamask.getRange(z, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(z, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(z, 5).setValue(aaarup);
z = z + 1;
}
w = 1;
for (y = 1; y <= m; y++) {//COPIER/COLLER BTS
var aaa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BTS');
var aaamask = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BTS/MASK');
var aaaref = aaa.getRange(y, 2).getValue();
var aaadate = aaa.getRange(y, 5).getValue();
var aaaHD = aaa.getRange(y, 6).getValue();
var aaaHF = aaa.getRange(y, 7).getValue();
var aaarup = aaa.getRange(y, 8).getValue();
aaamask.getRange(w, 1).setValue(aaaref);
aaamask.getRange(w, 2).setValue(aaadate);
aaamask.getRange(w, 3).setValue(aaaHD);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(w, 4).setValue(aaaHF);//.setNumberFormat('HH:mm:ss');
aaamask.getRange(w, 5).setValue(aaarup);
w = w + 1;
}
}
bbb.getRange('A3').setValue("Modification possible");
Browser.msgBox("Modification possible");
}
}
Ne remets jamais quand tu remets ta variable à 1.
Bonne Journée