Move Rows En Masse Contingent on Value in One Column

Question

I have a script setup that moves all the records in a sheet into another sheet. However, I would like to add functionality to only move records that have the value "Approved" in a specific column (column I for my current situation). I've seen some other questions/answers to move individual rows based on values, but I haven't figured out how to leverage these scripts to run across a full spreadsheet. My guess is I can use a for loop but how?

Current script:

 function MoveRecords(){

  //Original Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Example Input")

  //Calculate # of Rows Ignoring Blank Array Values
  var Rows = ss.getRange("A2:A").getValues();
  var NumRows = Rows.filter(String).length;

/*Add a For Loop here to only get the rows with Approved in Column I?*/

  //Parsed Data to Move
  var rangeValues1 = sheet.getRange(2,1,NumRows,1).getValue();
  var rangeValues2 = sheet.getRange(2,2,NumRows,5).getValues();

  //Destination of Parsed Data 
  var DestinationSS = SpreadsheetApp.openById('ID');
  var DestinationSheet = DestinationSS.getSheetByName('Approved');  
  var DestinationLastRow = DestinationSheet.getLastRow()+1;

  //Move the Data
  DestinationSheet.getRange(DestinationLastRow,3,NumRows,1).setValue(rangeValues1);
  DestinationSheet.getRange(DestinationLastRow,5,NumRows,5).setValues(rangeValues2);

  };

Any help is greatly appreciated!

ra89fi ra89fi · Accepted Answer · 2019-03-15T18:24:36

You can get all values, filter every row for Approved then write those rows into destination sheet.

function MoveRecords() {
  //Original Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Example Input');
  var values = sheet.getDataRange().getValues();

  /*Add a For Loop here to only get the rows with Approved in Column I?*/
  // col I = index 8
  var res = values.filter(function(row) {
    return row[8] == 'Approved';
  });

  //Parse Data to Move
  var rangeValues1 = res.map(function(row) {
    return [row[0]];
  });
  var rangeValues2 = res.map(function(row) {
    row.shift();
    while (row.length > 5) row.pop();
    return row;
  });

  //Destination
  var DestinationSS = SpreadsheetApp.openById('ID');
  var DestinationSheet = DestinationSS.getSheetByName('Approved');
  var DestinationLastRow = DestinationSheet.getLastRow() + 1;

  //Move the Data
  DestinationSheet.getRange(DestinationLastRow, 3, rangeValues1.length, 1).setValues(rangeValues1);
  DestinationSheet.getRange(DestinationLastRow, 5, rangeValues2.length, 5).setValues(rangeValues2);
}

Move Rows En Masse Contingent on Value in One Column

1 Answers