There are approximately 200 individuals in this particular department. Our goal is to form groups of 4, with each group consisting of members from different teams based in the same city. Each group must have one driver and three non-drivers, all sharing similar preferences. These criteria will be collected through a Google Form to gather the necessary data for creating the groups. My current challenge lies in my limited knowledge of Apps Script, so I will require high-level guidance on how to proceed.
My initial approach involves storing each column in separate arrays and using a For loop along with conditional statements to meet all the requirements. The first condition would identify drivers and assign them to a new array. Then, the next responses would be checked to find non-drivers with matching preferences, located in the same city, and from different teams to include them in another array. This process continues until a group of 4 is formed, which would then be recorded in a Google Sheet before moving on to create the next group following the same logic.
An alternative method could involve sorting people into two groups based on their respective cities, followed by further categorization within each group based on driving status and other criteria.
Regardless of the algorithm chosen, starting with storing each column value in an array seems like the most logical approach, allowing for easy management of the array values using various conditional statements.
This is the progress made so far:
function matchUsers() {
//store each column into arrays for later use
var responseSheet = SpreadsheetApp.getActive().getSheetByName("Responses");
var Alast = responseSheet.getRange("A2:A").getValues().filter(String).length;
Logger.log(Alast);
var emails = responseSheet.getRange("A2:A").getValues();
var userEmails = responseSheet.getRange(2,1, Alast,1).getValues();
var tmName = responseSheet.getRange(2,2, Alast,1).getValues();
var team = responseSheet.getRange(2,3, Alast,1).getValues();
var city = responseSheet.getRange(2,4, Alast,1).getValues();
var firstChoice = responseSheet.getRange(2,5, Alast,1).getValues();
var secChoice = responseSheet.getRange(2,6, Alast,1).getValues();
var thirdChoice = responseSheet.getRange(2,7, Alast,1).getValues();
var driveStatus = responseSheet.getRange(2,8, Alast,1).getValues();
var arrayResponses = [userEmails,tmName,team,city,firstChoice,secChoice,thirdChoice, driveStatus];
//clear sheets
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Output").getRange(2, 1,20,50).clearContent();
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CitySort").getRange(2, 1,20,50).clearContent();
//prepare arrays
var edmTMs = [];
var calTMs = [];
var edmDrivers = [];
var calDrivers = [];
//loop through each row and sort team members by respective city and driving capability
for (let i = 0; i < Alast;i+=1){
if (arrayResponses[3][i] == "Edmonton" && arrayResponses[7][i] == "Yes"){
edmDrivers.push(arrayResponses[1][i]);
}else if (arrayResponses[3][i] == "Edmonton" && arrayResponses[7][i] == "No"){
edmTMs.push(arrayResponses[1][i]);
}else if (arrayResponses[3][i] == "Calgary" && arrayResponses[7][i] == "Yes"){
calDrivers.push(arrayResponses[1][i]);
}else if (arrayResponses[3][i] == "Calgary" && arrayResponses[7][i] == "No"){
calTMs.push(arrayResponses[1][i]);
}
}
Logger.log("\nEdmonton: "+edmTMs + "\nCalgary: "+calTMs );
var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CitySort").getRange(2,1,edmTMs.length,1);
range.setValues(edmTMs);
var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CitySort").getRange(2,2,calTMs.length,1);
range.setValues(calTMs);
var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CitySort").getRange(2,3,edmDrivers.length,1);
range.setValues(edmDrivers);
var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CitySort").getRange(2,4,calDrivers.length,1);
range.setValues(calDrivers);
}