change rows of datas when one of them is empty-CodePudding

i work in database to manage changing rooms in Google Sheets. Each changing room is composed ok many lockers and each locker can be used by two people max (for exemple : one locker named 1 is identified by two places, one with the name 1_A and the other by the name 1_B). I have a tab on my Sheet where i have all of datas (it's my data base), an other tab where i attribute lockers for the staff and the last where i can clean lockers when a member of staff leave the company. I would like to create a script allowing to change the lockers. If a member of staff with the locker 1_A leave the company, i would like to give at the other person of the locker with the name 1_B the locker 1_A.

For the exemple, if Sansa leave the company (she have the Locker 02_A), i would like to give to Arya the locker 02_A (with all of the information of her) and clear the row 5).

I don't know how i can proceed. Anyone can help me with that please (i don't need the code, just an idea to write the apps script)? Thank you in advance for your help.

CodePudding user response：

It can be something like this:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var [header, ...data] = sheet.getDataRange().getValues();
  var new_data = [];

  while(data.length) {
    var row_a = data.shift();
    var row_b = data.shift();

    // swap the rows A and B if there is no name in column D of row A
    // and if there is a name in column D of row B
    if (row_a[3] == '' && row_b[3] != '') {
      [row_a, row_b] = [row_b, row_a];             // swap rows
      [row_a[1], row_b[1]] = [row_b[1], row_a[1]]; // swap back the values in column B
    }
    new_data.push(row_a);
    new_data.push(row_b);
  }

  var table = [header, ...new_data];
  sheet.clearContents();
  sheet.getRange(1,1,table.length,table[0].length).setValues(table);
}