How do i move or copy data from google Sheet1 to Sheet2 without deleting the copied data when the original data is being deleted

Question

What i want to achieve:

When the last column indicates TRUE i will send an email to the respective recipient (created a button and assigned it to a script)
Only Move/Copy data from Sheet1 to Sheet2 when last column indicates TRUE
Data moved to Sheet2 will be written in new row(append new row)
Data in Sheet1 will be deleted,but it should not affect data in Sheet2.

What is the issue:

Data do not move from sheet1 to Sheet2

Below are my codes:

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

Brian Brian · Accepted Answer · 2018-05-14T12:26:41

You're not getting the values in the sheet before starting the loop. You need to use getValues() first because it returns a 2D array that you can then loop through. A modified script is below:

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

How do i move or copy data from google Sheet1 to Sheet2 without deleting the copied data when the original data is being deleted

1 Answers