CODEWITHSUNDEEP
google-apps-script
Danae Gonzales
Danae Gonzales

Reputation: 55

Clear contents on original sheet after copying/pasting

I have this script to copy and paste rows from the original sheet to the archive sheet that have been confirmed to have been actioned on - which works perfectly! But I also want to clear the contents on the original sheet so it does not copy duplicate rows. I'm new to writing script and would appreciate all suggestions! 

   function moveValuesOnly(){
     var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Time Off Action Items");
     var range = ss.getRange("A3:K");
     var values = range.getValues();
     var target = new Array();
      for(i = 0; i<values.length; i++){
      if (values[i][9] == "Confirm"){
      target.push(values[i])
     }
     }

   var sDest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Archive");
   sDest.getRange(sDest.getLastRow()+1,1,target.length,target[0].length).setValues(target);
  }

Upvotes: 0

Views: 190

Answers (1)

WilcoSoft
WilcoSoft

Reputation: 146

I made a similar sheet for a work project, and the solution I came up with was to store the values of the rows as I copied them to a new array and then deleted the rows from the bottom up using a for loop. For your instance, try this:

function moveValuesOnly(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Time Off Action Items");
  var range = ss.getRange("A3:K");
  var values = range.getValues();
  var target = new Array();
  // Create new array to log copied rows
  var copiedRows = new Array();
  for(i = 0; i<values.length; i++){
    if (values[i][9] == "Confirm"){
      target.push(values[i]);
      // Add row value into copied rows array
      copiedRows.push(i);
    }
  }
  var sDest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Archive");
  sDest.getRange(sDest.getLastRow()+1,1,target.length,target[0].length).setValues(target);
  // Go back through and delete rows from the bottom up
  var totalCopied = copiedRows.length;
  for (s = totalCopied; s>0; s--) {
  ss.deleteRow(copiedRows[s-1]+3);
  }
}

Upvotes: 3

Related Questions