finding a selected row with identical columns in two different sheets and delete it from one

Question

I have two sheets; the first one is check-in with 6 columns, and second is check-out with 8 columns.

my columns are like this:

in check-in sheet:

code | model | made | type | Location | check-in time

in check-out sheet:

check-out?(y/n)| code | model | made | type | Location | check-in time | check-out time

so as you see, check-in columns and data come to check-out sheet by a filter function. So, when I want to check-out a 'model', I just type-in the model name in a specific cell and it brings related data from check-in sheet. I also made a function for first column of check-out sheet that once I type "y" under "check-out?(y/n)" column, in one of the filtered rows, it would type in now() time in the last column.

My question is that, I need to also find the row with "y" in the first column of "check-out" sheet and find that row in check-in sheet based on "check-in time" (which is the most unique data for each row) and delete it from the list which is my inventory list. I am not sure how to do it? I have been looking for a google scripts code, but not lucky. Any advice is appreciated.

Answer 1

Here you would probably need an onEdit() function that would check if 'y' is entered in the first column:

function onEdit(e) {
    var ss = e.source;
    if (e.range.getColumn() === 1 && e.value === "y") {
        ...some code here....
    }
}

Then you get the values from the row in which 'y' was input:

var checkOutArray = checkOutSheet.getRange(e.range.getRow(), 2, 1, 6).getValues()[0];

getValues() always returns a two-dimensional array that's why you need to specify that you want the its first element even though there's only one.

In the same manner you're taking all the values from the check-in sheet:

var checkInArray = checkInSheet.getRange(2, 1, checkInSheet.getLastRow() - 1, checkInSheet.getLastColumn()).getValues();

Then comes the checking part. I thought it might be safer to compare all the arrays' values instead of just dates:

for (var i = 0; i < checkInArray.length; i++) {
      if (arraysEqual(checkInArray[i], checkOutArray) == true) {
        checkInSheet.deleteRow(i + 2); //i + 2 is the row in which same values have been found
        return true;
      }
      else continue;
    }

Where arraysEqual is:

function arraysEqual(arr1, arr2) {
  if(arr1.length !== arr2.length) {
    return false; 
  }
  for(var i = arr1.length; i--;) {
    if(arr1[i].toString() !== arr2[i].toString()) {
      return false;
    }
  }

  return true;
}

---

Altogether it looks like this:

function onEdit(e) {
  var ss = e.source;
  var checkOutSheet = ss.getSheets()[0];
  var checkInSheet = ss.getSheets()[1];
  if (e.range.getColumn() === 1 && e.value === "y") {
    var checkOutArray = checkOutSheet.getRange(e.range.getRow(), 2, 1, 6).getValues()[0]; //getting the values from the row where "y" was entered (e.range.getRow())
    var checkInArray = checkInSheet.getRange(2, 1, checkInSheet.getLastRow() - 1, checkInSheet.getLastColumn()).getValues(); //getting all the values from check out sheet
    for (var i = 0; i < checkInArray.length; i++) {
      if (arraysEqual(checkInArray[i], checkOutArray) == true) {
        checkInSheet.deleteRow(i + 2); //i + 2 is the row in which same values have been found
        return true;
      }
      else continue;
    }
  }

}

function arraysEqual(arr1, arr2) {
  if(arr1.length !== arr2.length) {
    return false; 
  }
  for(var i = arr1.length; i--;) {
    if(arr1[i].toString() !== arr2[i].toString()) {
      return false;
    }
  }

  return true;
}