Google 脚本将多行同时移动到另一张表 - 存档行并删除已完成

我正在尝试同时将多行从一张 Google 工作表移动到另一张。

我想在一天结束时运行一次脚本来归档所有完整的作业。

只要目标单元格不为空，我编写的脚本一次只会移动 2 行。理想情况下，它将继续循环并仅移动标记为“完成”的作业。

我正在使用的表是here https://docs.google.com/spreadsheets/d/1jrsjag4LmHdmmDKTGrpm_sPiU9wODjUyaeOwOdm1SeQ/edit#gid=0

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());
  }
  }
}

存档、Google 表格、移动、行、电子表格、删除

您应该在一次操作中将所有数据设置为存档。您无法在一次操作中删除所有行，但可以保存要删除的行，然后循环遍历行号。

链接到带有代码的公开共享电子表格 https://docs.google.com/spreadsheets/d/1Jh7zl-1sAdaNECIa7WSKqGOW2In0BixS8xBksUNK_Do/edit?usp=sharing

我将代码设置为如下结构：

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]);
  }
}