I am having a few issues. I have a sheet called (RF) in which I have information divided by places (Passenger seat, driver seat, etc), each one is identified with a number (1, 2, 3, 4. Four is the amount of categories but it could change so that is why I am trying a loop). Something like this:

So what I am trying to do is to iterate through each of the rows and filter them by the number on column A (as I said I would need to do it someway through a loop or something because those "numbers" I use as identifiers may vary). Once it's filtered, I am trying to copy the filtered data to a new sheet called just like the number. But it's just not working. I solve an error and then another one occurs and it's driving me crazy.

I've seen a few posts on StackOverFlow trying to get close to my problem but I have not found any solution.

function bucleInicialPrueba(){
    var spreadsheet = SpreadsheetApp.getActive();
    var hojaRF = spreadsheet.getSheetByName('RF');
    var ultFila = spreadsheet.getSheetByName('RF').getLastRow();
    var data = hojaRF.getDataRange().getValues();
    spreadsheet.getRange('1:156').activate();
    hojaRF.getRange('1:156').createFilter();
    for(j=0;j<=data.length;j  ){
      if(data[j][0]!=j){
        hojaRF.hideRows(j 1);
        hojaRF.getRange('A1').activate();
        for(i=1;i<=4;i  ){
          var filter = hojaRF.getFilter();
          if (filter!=null) {
            var criterio = SpreadsheetApp.newFilterCriteria();
            criterio.whenNumberEqualTo(i).build();
            hojaRF.getFilter().setColumnFilterCriteria(i, criterio);
            spreadsheet.insertSheet(1);
            spreadsheet.getActiveSheet().setName(i);
            hojaRF.getRange('A'  j   ':AK'         j).copyTo(spreadsheet.getSheetByName(i).getActiveRange(),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
            hojaRF.getFilter().remove();
          } else {
            var criterio = SpreadsheetApp.newFilterCriteria();
            criterio.whenNumberEqualTo(i).build();
            hojaRF.getRange(1, 1, hojaRF.getLastRow()).createFilter().setColumnFilterCriteria(i, criterio);
            spreadsheet.insertSheet(1);
            spreadsheet.getActiveSheet().setName(i);   
            hojaRF.getRange('A'  j   ':AK'   j).copyTo(spreadsheet.getSheetByName(i).getActiveRange(),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)          
          }
        }
      }
    }
  };

CodePudding user response：

Do you need the information to actually be copied?? You could just set a Query in each category sheet. For example, in A1 of the Category Sheet you could set this function in B1:

=query(RF!1:156,"Select * Where A = "&A1)

This is with the range of your code, but probably your amount of rows will be increasing (probably the columns won't), so you should better say:

=query(RF!A1:Z,"Select * Where A = "&A1)

I've said Z, but you can stablish the column you want

CodePudding user response：

With this function vs1 now contains the lines you want:

function bucleInicialPrueba(n=2) {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Sheet0');
  const vs1 = sh1.getRange(2,1,sh1.getLastRow() - 1, sh1.getLastColumn()).getValues().filter(r => r[0].toString().includes(n)).filter(e => e);
Logger.log(JSON.stringify(vs1));
}

Execution log
4:27:58 PM  Notice  Execution started
4:27:57 PM  Info    [["2sh",4,5,15,2,3,19,14,11,10,5,7,0,1,10,6,18,4,19,1,8,4,16,1,18,19],["2sh",2,19,2,18,3,17,19,16,5,10,6,6,1,13,15,14,0,8,3,6,7,18,4,17,5],["2sh",2,16,15,9,10,5,8,4,19,4,5,16,16,1,8,3,13,4,4,19,2,1,16,16,18],["2sh",14,15,3,0,19,12,17,13,6,2,8,13,6,11,16,3,0,12,18,19,5,15,17,7,1],["2sh",7,18,13,17,18,11,1,3,16,11,0,16,9,10,19,6,14,12,16,7,2,11,13,13,16],["2sh",3,15,11,18,6,18,3,10,17,5,6,17,15,11,14,1,5,14,9,13,19,13,6,17,2],["2sh",12,16,7,9,10,7,5,14,0,4,0,6,12,6,6,17,10,15,5,15,1,3,6,12,16]]
4:27:59 PM  Notice  Execution completed

My Data: