It takes too many time to run one for iteration because the quantity of if in it, if its a better solution for it and someone can help me i would be very greatfull. The function works a singular code with the items detailed in IF sentences time by time. With a list of 2000 3000 codes it takes too much time and ejecutions because the 6 min max time expires frecuently.
function CODIFICADOR() {
var sheet0 = SpreadsheetApp.getActive().getSheetByName('HOJA');
var sheet1 = SpreadsheetApp.getActive().getSheetByName('PROGRAMA');
var cantcod = sheet0.getRange("B1:B1500").getValues().filter(String).length;
SpreadsheetApp.getActiveSpreadsheet().toast('POR FAVOR NO REALIZAR NINGUNA ACCION MIENTRAS EL PROGRAMA SE EJECUTA');
for(var i = 2; i <= cantcod; i++){
var codigo = sheet0.getRange(i, 2).getValue();
var codigosig = sheet0.getRange(i+1, 2).getValue();
sheet1.getRange(2, 8).setValue(codigo);
sheet1.getRange(2,11).setValue(codigosig);
var codcumple = sheet1.getRange(6, 8).getValue();
var codidentico = sheet1.getRange(8, 8).getValue();
if( codcumple == "OK" && codidentico == "NO"){
var codfila = sheet1.getRange(4, 8).getValue();
if(codfila == "-AP" ){
sheet0.insertRowsAfter(i, 3);
sheet0.getRange(i, 21).activate();
sheet0.getActiveRange().autoFill(sheet0.getRange(i, 21, 4, 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheet0.getRange(i+1, 2).setValue(codigo+"-KP01").setBackground("green");
sheet0.getRange(i+1, 6).setValue("Bomba").setBackground("green");
sheet0.getRange(i+2, 2).setValue(codigo+"-MK01").setBackground("green");
sheet0.getRange(i+2, 6).setValue("Acoplamiento").setBackground("green");
sheet0.getRange(i+3, 2).setValue(codigo+"--M01").setBackground("green");
sheet0.getRange(i+3, 6).setValue("Motor eléctrico").setBackground("green");
var cantcod = cantcod+3;
i = i+3;
}
else if(codfila == "-AN" ){
sheet0.insertRowsAfter(i, 2);
sheet0.getRange(i, 21).activate();
sheet0.getActiveRange().autoFill(sheet0.getRange(i, 21, 3, 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheet0.getRange(i+1, 2).setValue(codigo+"--M01").setBackground("green");
sheet0.getRange(i+1, 6).setValue("Motor eléctrico").setBackground("green");
sheet0.getRange(i+2, 2).setValue(codigo+"-KN01").setBackground("green");
sheet0.getRange(i+2, 6).setValue("Ventilador").setBackground("green");
var cantcod = cantcod+2;
i = i+2;
}
else if(codfila == "-AA" ){
sheet0.insertRowsAfter(i, 1);
sheet0.getRange(i, 21).activate();
sheet0.getActiveRange().autoFill(sheet0.getRange(i, 21, 2, 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheet0.getRange(i+1, 2).setValue(codigo+"-KA01").setBackground("green");
sheet0.getRange(i+1, 6).setValue("Válvula").setBackground("green");
var cantcod = cantcod+1;
i = i+1;
}
else if(codfila == "-AT" ){
sheet0.insertRowsAfter(i, 1);
sheet0.getRange(i, 21).activate();
sheet0.getActiveRange().autoFill(sheet0.getRange(i, 21, 2, 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheet0.getRange(i+1, 2).setValue(codigo+"-KT01").setBackground("green");
sheet0.getRange(i+1, 6).setValue("Filtro").setBackground("green");
var cantcod = cantcod+1;
i = i+1;
}
else if(codfila == "-BR" ){
sheet0.insertRowsAfter(i, 1);
sheet0.getRange(i, 21).activate();
sheet0.getActiveRange().autoFill(sheet0.getRange(i, 21, 2, 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheet0.getRange(i+1, 2).setValue(codigo+"-MR01").setBackground("green");
sheet0.getRange(i+1, 6).setValue("Tubería").setBackground("green");
var cantcod = cantcod+1;
i = i+1;
}
else if(codfila == "-CP" ){
sheet0.insertRowsAfter(i, 1);
sheet0.getRange(i, 21).activate();
sheet0.getActiveRange().autoFill(sheet0.getRange(i, 21, 2, 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheet0.getRange(i+1, 2).setValue(codigo+"-QP01").setBackground("green");
var cantcod = cantcod+1;
i = i+1;
}
else if(codfila == "-CT" ){
sheet0.insertRowsAfter(i, 1);
sheet0.getRange(i, 21).activate();
sheet0.getActiveRange().autoFill(sheet0.getRange(i, 21, 2, 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheet0.getRange(i+1, 2).setValue(codigo+"-QP01").setBackground("green");
var cantcod = cantcod+1;
i = i+1;
}
else if(codfila == "-CG" ){
sheet0.insertRowsAfter(i, 1);
sheet0.getRange(i, 21).activate();
sheet0.getActiveRange().autoFill(sheet0.getRange(i, 21, 2, 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheet0.getRange(i+1, 2).setValue(codigo+"--S01").setBackground("green");
sheet0.getRange(i+1, 6).setValue("Interruptor de final de carrera").setBackground("green");
var cantcod = cantcod+1;
i = i+1;
}
else if(codfila == "-AC" ){
sheet0.insertRowsAfter(i, 1);
sheet0.getRange(i, 21).activate();
sheet0.getActiveRange().autoFill(sheet0.getRange(i, 21, 2, 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheet0.getRange(i+1, 2).setValue(codigo+"-QC01").setBackground("green");
sheet0.getRange(i+1, 6).setValue("Intercambiador de calor").setBackground("green");
var cantcod = cantcod+1;
i = i+1;
}
}
//else { sheet0.getRange(i, 2).setFontColor("red");}
}
Aucun commentaire:
Enregistrer un commentaire