lundi 28 décembre 2020

How can i optimice this FOR sentence with lots of IF?

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