App-Scripts How to delete and keep some rows based on conditions

Question

I'm going to use google sheets instead of excel. So I face some problem about coding. This problem I used to ask before.

Let say I have two days data in a row (earlyday, laterday) with 25 columns and more than 50K rows I want to keep some data which match the criteria and delete others which I don't need.

example. I want to delete 'AAA', 'BBB', 'DDD', 'FFF' from early day and keep other 'CCC', 'EEE', 'GGG'. and for later day I want to keep 'AAA', 'BBB', 'DDD', 'FFF' and delete others

enter image description here

And the result will be like this enter image description here

here is the code I tried but it didn't run to get the result.

//text data in D, dates to evaluate earlyDay/lateDay in C

var Offset = 1;

function deleteRows() {
 var app = SpreadsheetApp.getActiveSpreadsheet();
 var targetSheet = app.getSheetByName('Sheet1');
 var r = targetSheet.getRange('C:C');
 var v = r.getValues();
 var r1 = targetSheet.getRange('D:D');
 var f = r1.getValues(); 
 var lateDay = new Date(getLateDay()).getTime();
 var earlyDay = new Date(getEarlyDay()).getTime();
  
 for(var i = f.length-1; i>=Offset; i--){
  var tmp = new Date(v[0,i]).getTime();
   if(tmp==earlyDay && (f[0,i]=="AAA" || f[0, i]=="BBB")){
    targetSheet.deleteRow(i+1);
   }
 }
  for(var i = f.length-1; i>=Offset; i--){
  var tmp = new Date(v[0,i]).getTime();
   if(tmp==lateDay && (f[0,i]!="AAA" && f[0, i]!="BBB")){
    targetSheet.deleteRow(i+1);
   }
 }
}

function getLateDay() {
 var app = SpreadsheetApp.getActiveSpreadsheet();
 var targetSheet = app.getSheetByName('Sheet1');
 var r = targetSheet.getRange('C:C');
 var v = r.getValues();
  for(var i = v.length-1; i>=Offset; i--){
  var tmp = new Date(v[0,i]).getTime();
  var tmp1 = new Date(v[0,i-1]).getTime();
   if(tmp>tmp1){
    return tmp;
   }
  }
  return null;
}

function getEarlyDay() {
 var app = SpreadsheetApp.getActiveSpreadsheet();
 var targetSheet = app.getSheetByName('Sheet1');
 var r = targetSheet.getRange('C:C');
 var v = r.getValues();
  for(var i = v.length-1; i>=Offset; i--){
  var tmp = new Date(v[0,i]).getTime();
  var tmp1 = new Date(v[0,i-1]).getTime();
   if(tmp<tmp1){
    return tmp;
   }
  }
  return null;
}

Answer 1

The OP code has some fundamental syntax and logic flaws. Troubleshooting with the Logger command might have helped the OP identify problems.

1) The wrong syntax is used to access the value of the array. For example, getLateDay uses v[0,i]; there are two problems here.

• both elements of the array must be in square brackets ([0][i]),
• it is querying the wrong element. Instead of [0][i], it should be [i][0].

2) getLateDay and getEarlyDay are querying the values in column C. Rather, they should query column D (the column of dates).

3) Both getLateDay and getEarlyDay should evaluate if(tmp>tmp1). However getLateDay should return "tmp" and getEarlyDay should return "tmp1".

4) getLateDay and getEarlyDay repeat much of the main code, and are largely identical. In the interests of performance, they could easily be incorporated into the main code.

5) A single loop only is required to evaluate for EarlyDay/LateDay versus "tmp", though of course the alpha code conditions applying to EarlyDay and LateDay need to be evaluated separately. I also found that the values that were to be retained for LateDay were best evaluated in a sequence of IF statements. The entire second loop can be deleted.

6) It might have been less confusing, and save a few moments in processing time if, rather than working with separate ranges for Column C and Column D, a single range and values were declared for the combined columns. This would be much more important as the OP's task grows and they are working with 25 columns.

I've modified the OP's code to address these comments. I've left several Logger statements in the code so that the OP can evaluate variables at different states of the code.

//text data in D, dates to evaluate earlyDay/lateDay in C
var Offset = 1;

function deleteRows() {

  // set up the spreadsheet
  var app = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = app.getSheetByName('Sheet1');

  // define the data ranges and get values
  var r = targetSheet.getRange('C:C');
  var v = r.getValues();
  var r1 = targetSheet.getRange('D:D');
  var f = r1.getValues();
  //Logger.log("DEBUG: code: "+v[0][0]+", date: "+f[0][0].getTime());//DEBUG
  //Logger.log("DEBUG: length of f: "+f.length);//DEBUG

  // get the respective values for lateDay and earlyDay
  var lateDay = new Date(getLateDay()).getTime();
  var earlyDay = new Date(getEarlyDay()).getTime();
  //Logger.log("DEBUG: LateDay: "+lateDay+", EarlyDay: "+earlyDay); //DEBUG

  // Loop through the data, starting at te bottom.
  for (var i = f.length - 1; i > -1; i--) {

    // get the date for this row
    var tmp = new Date(f[i][0]).getTime();

    // evaluate if this row is early and the values of possible codes
    if (tmp == earlyDay && (v[i][0] == "AAA" || v[i][0] == "BBB" || v[i][0] == "DDD" || v[i][0] == "FFF")) {
      Logger.log("DEBUG: Option#1 i: " + i + ", row: " + (i + 1) + "- delete row , earlyDay: " + earlyDay + ", tmp: " + tmp + ", code: " + v[i][0]); //DEBUG

      // Note the row deleted is "i+1" because the deleteRow command matchs the actual row number, whereas the loop works on a zero-basis.
      targetSheet.deleteRow(i + 1);
    } else {
      //Logger.log("DEBUG: Option#1 - do nothing");//DEBUG
    }

    // evaluate if this row is late and the values of possible codes
    // Note this is an either or; a day cannot be both early and late
    if (tmp == lateDay) {
      Logger.log("today is Late Day");
      if (v[i][0] != "FFF") {
        if (v[i][0] != "DDD") {
          if (v[i][0] != "BBB") {
            if (v[i][0] != "AAA") {
              Logger.log("DEBUG: code <> AAA or BBB or DDD or FFF"); //DEBUG
              Logger.log("DEBUG: Option#2 i: " + i + ", row: " + (i + 1) + "- delete row , lateDay: " + lateDay + ", tmp: " + tmp + ", code: " + v[i][0]); //DEBUG
              targetSheet.deleteRow(i + 1);
            } else {
              //Logger.log("DEBUG: Option#2 - do nothing");//DEBUG
            }
          }
        }
      }
    }
  }
}

function getLateDay() {

  var app = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = app.getSheetByName('Sheet1');
  var r1 = targetSheet.getRange('D:D');
  var f = r1.getValues();
  for (var i = f.length - 1; i > -1; i--) {
    var tmp = new Date(f[i][0]).getTime();
    var tmp1 = new Date(f[i - 1][0]).getTime();
    //Logger.log("DEBUG: LateDay: i: "+i+", this day: "+tmp+", yesterday: "+tmp1);//DEBUG
    if (tmp > tmp1) {
      //Logger.log("DEBUG: return this day: "+tmp);//DEBUG
      return tmp;
    }
  }
  //Logger.log("DEBUG: return null");
  return null;
}

function getEarlyDay() {

  var app = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = app.getSheetByName('Sheet1');
  var r1 = targetSheet.getRange('D:D');
  var f = r1.getValues();
  for (var i = f.length - 1; i > -1; i--) {
    var tmp = new Date(f[i][0]).getTime();
    var tmp1 = new Date(f[i - 1][0]).getTime();
    //Logger.log("DEBUG: EarlyDay: i: "+i+", tmp: "+tmp+", tmp1: "+tmp1);//DEBUG
    if (tmp > tmp1) {
      //Logger.log("DEBUG: return this day: "+tmp1);//DEBUG
      return tmp1;
    }
  }
  //Logger.log("DEBUG: return null");//DEBUG
  return null;
}

---

BEFORE & AFTER screenshots