How to assign drivers and passengers to cars using google script

Question

Hello I have about 120 employees working on two shifts and living in 4 cities to which they drive with company cars(9 people van, so driver + 8 passengers, the cars do not always have to be full).

I have prepared a sheet with two tabs. On the first I have:

Name of the empleyee in column A
City they live in in column B
Whether they have drivers' license(yes/no) in column C
Whether they are on vacation(yes/no) in column D

And on another tab I have created a table with a car registration plate(B1) and direction it drives to in cell B2.

I am trying to create a script that will(on button press, one button per car) push the data into D2:D10 based on if: People are living in the same city AND they are not on vacation AND at least one of them has drivers' license.

I am not sure whether I even have the right idea for that script(or what to use to push the data) because the only script I have ever written was to archive a sheet and clear cells in the original one.

I would like to get some guidance on:

What I am doing is correct
How to take the data that will pass the if conditions and put it into the table

Here is a link to a copy of the sheet

function updateCar1() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let range = ss.getRange("A2:D61");
let town = ss.getCell("cars!B2");

for(range){
if(ss.getCell("cars!B2")==town && ss.getRange("D2:D61")=="No" && ss.getRange("C2:C61")=="Yes"){

}
}
}

user_11599789 · Answer

Your idea is correct, the remaining issue is how to loop through a range

For this

first you need to get the values of the range, since you cannot loop through a range without it
then you need to loop through the rows of the 2D value range
for each row, you can access the values in the respective columns by their indices starting with 0 for the first column (in your case A)

Sample:

function updateCar1() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let range = ss.getRange("A2:D61");
  let values = range.getValues();
  let town = ss.getRange("cars!B2").getValue();
  
  for(var i = 0; i < values.length; i++){
    if(values[i][1] == town && values[i][3] =="No" && values[i][2] =="Yes"){
      //do something 
    }
  }
}

How to assign drivers and passengers to cars using google script

1 Answers

Your idea is correct, the remaining issue is how to loop through a range