CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamp
user1324637
user1324637

Reputation: 35

Deleting a row based on two conditions

So I have a row of data in a sheet. I need to delete the row if column H = "Complete" and if Column A's timestamp is more than 48 hours old. My issue is with adding in the elapse time condition. The script works when deleting the row if column H = "Complete".

var SS = SpreadsheetApp.openById("SheetID");
var SHEET = SS.getSheetByName("Form Responses");
var RANGE = SHEET.getDataRange();
var day = 24*3600*1000
var today = parseInt((new Date().setHours(0,0,0,0))/day);


var DELETE_VAL = "Complete";
var COL_TO_SEARCH = 7; // The column to search for the DELETE_VAL (Zero is first)
var COL_TO_SEARCH_2 = 0; // The column to search for the date (Zero is first)


function deleteEachRow(){

  var rangeVals = RANGE.getValues();

  //Reverse the 'for' loop.
  for(var i = rangeVals.length-1; i >= 0; i--){
    if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL && rangeVals[i][COL_TO_SEARCH_2] < today){

      SHEET.deleteRow(i+1); 
    };
  };
};

Upvotes: 0

Views: 177

Answers (1)

Cooper
Cooper

Reputation: 64140

function deleteIfOlderThan2DaysAgo() {
  var ss=SpreadsheetApp.openById("SheetID");
  var sh=ss.getSheetByName("Form Responses");
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var dt=new Date();
  var twoDaysAgo=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()-2).valueOf();
  var d=0;
  for(var i=0;i<vA.length;i++){
    var col1=new Date(vA[i][0]).valueOf();
    if(vA[i][7]=="Complete" && col1<twoDaysAgo) {
      sh.deleteRow(i+1-d++); 
    }
  }
}

Upvotes: 1

Related Questions