CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
CubbyG4M3R
CubbyG4M3R

Reputation: 1

GoogleSheet Script to Move to Sheet with same name as cell value

I have a sheet with 6 columns. Row 1 is titles and each other sheet has the same titles and columns.

Sheet Sample Photo

I have tried a couple codes like this that i found online with edits but can get it to correctly work for multiple sheets.

function copyrange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Source');
  var testrange = sheet.getRange('F:F');
  var testvalue = (testrange.getValues());
  var csh = ss.getSheetByName('target');
  var data = [];
  var j =[];

for (i=0; i<testvalue.length;i++) {
  if ( testvalue[i] == 'Completed') {
  data.push.apply(data,sheet.getRange(i+1,1,1,25).getValues());
  //Copy matched ROW numbers to j
  j.push(i);
 }
 }


 csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);


  for (i=0;i<j.length;i++){
  var k = j[i]+1;
  sheet.deleteRow(k);


  if (!(i == j.length-1)) {
  j[i+1] = j[i+1]-i-1;
}
}

Any Help would be greatly appreciated

Upvotes: 0

Views: 67

Answers (1)

Cooper
Cooper

Reputation: 64100

Deleting Rows as you collect data that meet criteria

function moveRows() {
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName('Source');
  var vA=sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
  var csh=ss.getSheetByName('target');
  var d=0;//deleted row counter
  var data=[];
  for(var i=0;i<vA.length;i++) {
    if (vA[i][5]=='Completed') {
      data.push(vA[i]);
      sheet.deleteRow(i+1-d++);//increment deleted row counter as you delete rows
    }
  }
  csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);
}

Sheet getLastRow()

Sheet getLastColumn

Upvotes: 1

Related Questions