How to delete cells if no match was found?

Question

I'm having difficulties writing the IF statement to satisfy my condition. I have 2 sheets: Main and Logistics. The first one has specific information about cargo and it's transportation, such as trailer, position and arrival date. The second sheet contains all of the transportation information, such names of the trailer, drivers, arrivals, departures etc. Based on the scheduled trailers on sheet "Logistics", the user can specify which of the available trailers he wants to use for the cargo in question.

However, in a situation when the trailer gets deleted from "Logistics" due to a cancellation, I am unable to revoke previously made selection on "Main". My idea is to make this script look for matching combinations of Destination and Trailer on both sheets (columns 8 and 13 on Main, columns 1 and 2 on Logistics). If there is a row on "Main" for which no matching trailer with the same destination was found on "Logistics", the script should set columns 13, 14 and 16 on "Main" to empty.

Could someone help me write an IF statement to satisfy this condition?

I have attached the bit of code I have so far. I think only IF statement needs modifying. Thanks for the help!

function deleteSelection() {
  // Main variables:
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetMain = ss.getSheetByName("Main");
  var tabMain = sheetMain.getRange(2, 1, sheetMain.getLastRow(), 18);
  var dataMain = tabMain.getValues();

  // Logistics variables:
  var sheet = ss.getSheetByName("Logistics");
  var dataRange = sheet.getRange(2, 1, sheet.getLastRow(), 9);
  var data = dataRange.getValues();

  for(var i = 0; i < dataMain.length; i++){

    for(var j = 0; j < data.length; j++){

      // Compare data: if there is no match between 2 sheets, set "Trailer", "Position" and "Arrival date" to empty:
      if(dataMain[i][7].toLowerCase() == data[j][0].toLowerCase() && dataMain[i][12] == data[j][1]){  
      } else{
        dataMain[i][12] = "";
        dataMain[i][13] = "";
        dataMain[i][15] = "";
        }

    }

  }

  // Take the modified tab and put it on the spreadsheet
  tabMain.setValues(dataMain);
}

UPD: Added a sample link. On "Logistics" you can see crossed out row, upon delete of which, a script should delete crossed out rows on "Main".

Answer 1

You are trying to establish whether the Event and Trailer values on Main match a value on Logistics.

I have taken a slightly different approach to you. I concatenated the value of the Main "EVent" and "Trailer" and used that value to find the match on Logistics.

• If a Match is found, then the script can break out of that loop and proceed to the next loop.
• If no Match is found, then a variable is incremented (since the match may be found in a subsequent comparison). However, once all the values on Logistics have been evaluated, if the number of mismatches is equal to the number of records on Logistics, then the values on Main must be updated.

---

function so5992862301() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetMain = ss.getSheetByName("Main");
  var tabMain = sheetMain.getRange(2, 1, sheetMain.getLastRow()-1, 18);
  var dataMain = tabMain.getValues();
  // Logger.log("DEBUG: Last row = "+sheetMain.getLastRow()+", length of dataMain = "+dataMain.length+" tab main = "+tabMain.getA1Notation());

  // Logistics variables:
  var sheet = ss.getSheetByName("Logistics");
  var dataRange = sheet.getRange(2, 1, sheet.getLastRow()-1, 9);
  var data = dataRange.getValues();
  // Logger.log("DEBUG: Logistics Last row = "+sheet.getLastRow()+", length of data = "+data.length+" dataRange = "+dataRange.getA1Notation());  

  // start loop through Main
  for(var i = 0; i < dataMain.length; i++){
    // count the matches
    var mismatch=0

    // start loop through Logistics
    for(var j = 0; j < data.length; j++){

      // match Logistics: Event (Column A) and Trailer (Column B)
      // match Main: Event (Column A) and Trailer (Column C)
      // Compare data: if there is no match between 2 sheets, set "Trailer", "Position" and "Arrival date" to empty:

      var logEventTrailer = data[j][0]+data[j][1];
      var mainEventTrailer = dataMain[i][0]+dataMain[i][2];

      //Logger.log("DEBUG: i:"+i+", Main:"+mainEventTrailer+", j:"+j+" Log:"+logEventTrailer);

      if (mainEventTrailer === logEventTrailer){
        // match
        // Logger.log("DEBUG: Match-"+"i:"+i+", Main:"+mainEventTrailer+", j:"+j+" Log:"+logEventTrailer);
        // if this is a match, then break loop and goto to next i
        break;
      }
      else{
        // no match
        mismatch = mismatch+1
        //Logger.log("DEBUG: No match:"+match+"-i:"+i+", Main:"+mainEventTrailer+", j:"+j+" Log:"+logEventTrailer)

      }

      // how many mismatches
      if (mismatch ==data.length){
        // no match found
        //Logger.log("DEBUG: no match found");
        // update array values for this row
        dataMain[i][2] = "";
        dataMain[i][3] = "";
        dataMain[i][4] = "";
      }

    }

  }
  // update the array values for Main
  tabMain.setValues(dataMain);
}