删除原始数据时，如何将数据从google Sheet1移动或复制到Sheet2，而不删除复制的数据

Question

我想要实现的目标：

1. 当最后一列指示TRUE时，我将向相应的收件人发送一封电子邮件（创建一个按钮并将其分配给脚本）
2. 仅当最后一列指示TRUE时将数据从Sheet1移动/复制到Sheet2
3. 移动到Sheet2的数据将写入新行（追加新行）
4. Sheet1中的数据将被删除，但不会影响Sheet2中的数据。

问题是什么：

1. 数据不会从表1移动到表2

以下是我的代码：

function moveData(){
var app = SpreadsheetApp;
var activeSheet = app.getActiveSpreadsheet().getActiveSheet();
var originalSheet = app.getActiveSpreadsheet().getSheetByName("Form 
Responses 4");
var copySheet = app.getActiveSpreadsheet().getSheetByName("Sheet2");
var lc = originalSheet.getLastColumn();
var lr = originalSheet.getLastRow();
var secondRangeTop = copySheet.getLastRow();

for (var i=2; i<=lr+1 ; i++){

var tempNumber = originalSheet.getRange(i,1,lr,lc).getValues();
var daTa = copySheet.getRange(secondRangeTop+1, 1, tempNumber.length, lc).setValues(tempNumber);
Logger.log(daTa);
}
return daTa; 
}

Answer 1

在开始循环之前，您不会在工作表中获取值。您需要首先使用getValue（），因为它返回一个2D数组，然后您可以循环使用。修改后的脚本如下：

function moveData() {
  var app = SpreadsheetApp;
  var originalSheet = app.getActiveSpreadsheet().getSheetByName("Form Responses 4");
  var copySheet = app.getActiveSpreadsheet().getSheetByName("Sheet2");
  var lc = originalSheet.getLastColumn();

  // Get the whole original sheet as a 2D array
  var originalData = originalSheet.getDataRange().getValues();

  // Now you can start the loop (assuming one header row)
  // To delete rows, it's better to loop backward so your index doesn't change
  // I also prefer to store rows to delete in an array for removal after data has been copied
  var toDelete = [];
  for (var i=originalData.length-1; i>=0 ; --i) {
    if(originalData[i][lc] == "true") {

      // Move the data to copySheet
      copySheet.getRange(copySheet.getLastRow()+1, 1, 1, originalData[i].length).setValues([ originalData[i] ]);
      toDelete.push(i+1);
    }
  }
  // Finally, delete the original data row
  toDelete.forEach(function(i) {
    originalSheet.deleteRow(i)
  }
}