CODEWITHSUNDEEP
google-apps-script
Joey
Joey

Reputation: 25

Google App Script, Moving row onto another sheet if date has passed

I've been working on this code and doing some research the last couple of weeks and not having much luck. I'm looking to setup a trigger that will scan Col C of dates and if the date has passed move that specific row to another sheet.

Below is where I'm currently at.

Note that I have today's date stored in cell 'O2' of the 'Contract Language' sheet.

I've gotten this code to work with text, but not with dates.

Any advise is greatly appreciated!

function MovetoPastShows2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Confirmed Deals'); //source sheet
  var testrange = sheet.getRange('C:C');
  var testvalue = testrange.getValues();
  var todaysheet = ss.getSheetByName('Contract Language');
  var todaycol = todaysheet.getRange('O2');
  var today = todaycol.getValues().valueOf();
  var csh = ss.getSheetByName('Confirmed (Past)'); //destination sheet
  var data = [];
  var j =[];


for (i=0; i<testvalue.length;i++) {
  if ( testvalue[i] = today) {
  data.push.apply(data,sheet.getRange(i+1,1,1,187).getValues());
  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;
}
}
};

Upvotes: 1

Views: 542

Answers (1)

Cooper
Cooper

Reputation: 64040

Move Past Rows to Destination Sheet

function MovetoPastShows2() {
  var ss = SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Confirmed Deals'); 
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var today=new Date(ss.getSheetByName('Contract Language').getRange('O2').getValue()).valueOf();
  var dsh=ss.getSheetByName('Confirmed (Past)'); 
  var d=0;
  for(var i=0;i<vA.length;i++) {
    if (new Date(vA[i][2]).valueOf()<=today) {
      dsh.appendRow(vA[i])
      sh.deleteRow(i+1-d);
      d++;
    }
  }
}

Upvotes: 1

Related Questions