Google Script to Move Multiple Rows Simultaneously to Another Sheet - Archive Rows and Delete Completed

Question

I'm trying to move multiple rows simultaneously from one Google sheet to another.

I would like to run the script once at the end of day to archive all the complete jobs.

The script I've put together will only move 2 rows at a time as long as the targeted cell is not blank. Ideally, it would continue to loop and only move the jobs marked 'Complete'.

The sheet I'm using is here

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Admin')
      .addItem('Archive', 'archive')
      .addToUi();
}

function archive() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Scheduled");
  var range = sheet.getRange('A5:Q200');
  var sheetToMoveTheRowTo = "Archive";
  var numRows = sheet.getLastRow();
  var row = sheet.getRange(5,1);

  for (var row = 5; row < numRows; row++) 
  {
    var status = sheet.getRange(row,1).getValue(); 

      if ( status == "Complete") {

    var targetSheet = ss.getSheetByName(sheetToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(),1,1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(range.getRow());
  }
  }
}

Answer 1

Archive, Google Sheets, Move, Rows, Spreadsheet, Delete

You should set all the data to archive in one operation. You can't delete all the rows in one operation, but you can save what rows to delete and then loop through the row numbers.

Link to Publicly Shared Spreadsheet with Code

I'd set up the code to be structured like this:

function archive() {
  var arrayOfRowsToArchive,data,i,lastColumn,L,numRows,range,rowsToDelete,
      ss,sheet,sheetToMoveTheRowTo,startRow,status,targetSheet,thisRowsData;

  //USER INPUT:

  startRow = 5;

  //END OF USER INPUT

  ss = SpreadsheetApp.getActiveSpreadsheet();
  sheet = ss.getSheetByName("Scheduled");
  sheetToMoveTheRowTo = "Archive";
  numRows = sheet.getLastRow();
  lastColumn = sheet.getLastColumn();

  arrayOfRowsToArchive = [];
  rowsToDelete = [];

  data = sheet.getRange(startRow, 1, numRows - startRow, lastColumn).getValues();//Get all values except the header rows

  L = data.length;

  for (i=0; i < L; i++) {
    status = data[i][0];
    //Logger.log('status: ' + status);

    if ( status === "Complete") {
      thisRowsData = data[i];//Only get inner array of data
      //Logger.log('thisRowsData: ' + thisRowsData)
      arrayOfRowsToArchive.push(thisRowsData);//Push one row of data to outer array
      rowsToDelete.push(i+startRow);//Get the row number to delete later
    }
  }

  targetSheet = ss.getSheetByName(sheetToMoveTheRowTo);
  targetSheet.getRange(targetSheet.getLastRow()+1, 1, arrayOfRowsToArchive.length, arrayOfRowsToArchive[0].length)
    .setValues(arrayOfRowsToArchive);

  //Logger.log('rowsToDelete: ' + rowsToDelete)

  for (i=rowsToDelete.length;i>0;i--) {//Delete from bottom up
    Logger.log((i-1).toString())
    Logger.log('rowsToDelete[i-1]: ' + rowsToDelete[i-1])
    sheet.deleteRow(rowsToDelete[i-1]);
  }
}