CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user3406204
user3406204

Reputation: 25

Google script: How to move or copy multiple rows to a target sheet

I am trying to get the source rows that match my conditions to move to a target sheet. Then delete the source rows in the source sheet. Please see below code. Any help would be much appreciated.

 function moveDeleteRow() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s1 = ss.getSheets()[0];
    var values = s1.getDataRange().getValues();
    var activeUser = Session.getActiveUser().getEmail();
    var userFullName = ContactsApp.getContact(activeUser).getFullName();
    Logger.log(userFullName);
    var deleted = 0;  // Counter
    var cell = s1.getActiveCell();
for (var i = 0; i < values.length; i++) {
  if (values[i][0] == userFullName && values[i][2] == 'Shipped') {
    var rowdata = values[i];
    Logger.log(rowdata);
  var targetSheet = ss.getSheets()[2];
  var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
  s1.getRange(1, 1, 1, s1.getLastColumn()).moveTo(targetRange);
  s1.deleteRow(i + 1 - deleted);
  deleted++;
   }
  }
 SpreadsheetApp.flush();
 };

Upvotes: 1

Views: 3442

Answers (1)

Karan
Karan

Reputation: 1187

This is a general code for moving range to range

function moveRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sourceSheet = ss.getSheets()[0];  
  var sourceRange = sourceSheet.getDataRange();
  var targetSheet = ss.getSheets()[1];  
  var targetRange = targetSheet.getRange(1, 1, sourceSheet.getLastRow(), sourceSheet.getLastColumn());

  sourceRange.moveTo(targetRange) ;

}

https://developers.google.com/apps-script/reference/spreadsheet/range#movetotarget

You just need to select the source and target properly.

Edit: If you're moving specific rows and want to delete the rows as well then it would be better to push the target values into an array (set them all at once later using range.setValues(array[][])) and also the indexes for the rows to be deleted and then delete the rows using sheet.deleteRow(position) using another loop. While using this, you'll have to keep in mind the offset for the next row being deleted because the ones that were deleted will cause the rest to shift up.

Upvotes: 2

Related Questions