Copy row from multiple sheets and delete

Question

I am new to javascript and scripts in Google. I have been working on a Google Spreadsheet. I need to copy rows from multiple sheets and paste them into a 'closed' sheet and then delete them from the sheet it was found only if in column B the word Cerrado is found. When I run the script it only runs on the sheet Material de Papeleria and only is the first row. It will copy and paste if Cerrado is found. I would run this script manually. How I can get it to run correctly on all pages and rows?

function deleteRow(){
   var ss=SpreadsheetApp.getActiveSpreadsheet();

    var tabs = [
      'Mantenimiento',
      'Material de papeleria',
      'Transporte',
      'Recursos para Eventos',
      'Mobiliario',
      'Material de Limpieza (e Higiene)'
    ];
  var sh1=ss.getSheetByName('closed');

  for (var i = 0; i < tabs.length; i++) {
    var sheet=ss.getSheetByName(tabs[i]);
    var range=sheet.getRange('A:AR').getValues();
      if(range[i][1]=='Cerrado')
      {
        sh1.appendRow(range[i]);
        sheet.deleteRow(i+1)
      }
    }
  }

Is the code sample you provided missing a line (specifically between var range... and if(range....)? — chuckx

chuckx chuckx · Accepted Answer · 2019-11-22T16:56:49

You need a nested loop to process the data in range. The variable i is keeping track of where you are in the tabs array. Reusing it when processing the data in range doesn't make sense.

Also, you'll want to process the rows in reverse order, otherwise deleting rows as you go will cause the row indexing to mismatch.

function deleteRow(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var tabs = [
      'Mantenimiento',
      'Material de papeleria',
      'Transporte',
      'Recursos para Eventos',
      'Mobiliario',
      'Material de Limpieza (e Higiene)'
  ];

  var closedSheet = ss.getSheetByName('closed');

  for (var i = 0; i < tabs.length; i++) {
    var sheet = ss.getSheetByName(tabs[i]);
    var range = sheet.getRange('A:AR').getValues();

    for (var j = range.length-1; j >= 0; j--) {
      if (range[j][1] == 'Cerrado') {
        sh1.appendRow(range[j]);
        sheet.deleteRow(j+1)
      }
    }
  }

Copy row from multiple sheets and delete

1 Answers