I'm trying to randomly distribute 5 colours to a list of people, with as equal amounts of people with each colour as possible. The current code I have takes the list and distributes the colours equally which is great, but the randomising process is still manual (I have to take the list of names, randomise them, run the script, then unrandomise them with the colours in place as I need to list to be in the original order it started as)
The script i'm currently using looks like this:
function AssignBreaks() {
// Active Sheet
const sheet = SpreadsheetApp.getActiveSheet();
// Clear any existing data
var ClearExistingData = sheet.getRange("B2:B");
ClearExistingData.clearContent();
// Identify the range of Column A
var ColA = sheet.getRange("A2:A").getValues();
var ColALast = ColA.filter(String).length;
// Get Array Data from the the range
var Employees = sheet.getRange(2,1,ColALast).getValues();
console.log("Employee List =")
console.log(Employees);
// Count Employee Numbers and output results
var EmployeeCount = Employees.length
console.log("Employee Count = ",EmployeeCount);
// Divide Employees by 5 Breaks
var ChunkCount = (EmployeeCount / 5).toFixed(2);
console.log("Chunk Count = ",ChunkCount);
// Split the employee array into chunks - Source: Andrei R - https://stackoverflow.com/questions/8495687/split-array-into-chunks
var perChunk = ChunkCount // items per chunk
var EmployeeChunks = Employees.reduce((resultArray, item, index) => {
const chunkIndex = Math.floor(index/perChunk)
if(!resultArray[chunkIndex]) {
resultArray[chunkIndex] = [] // start a new chunk
}
resultArray[chunkIndex].push(item)
return resultArray
}, [])
// Return Results
console.log("Employee Chunks =")
console.log(EmployeeChunks);
//Chunk 1
// Define the row to start on
var Row = 1
// Apply Break to each member of the chunk
// Source: https://stackoverflow.com/questions/9329446/for-each-over-an-array-in-javascript
for (const element of EmployeeChunks[0]) {
console.log(element);
Row = Row + 1;
var cell = sheet.getRange(Row,2);
cell.setValue("Break1");
}
//Chunk 2
// Define the row to start on
var ColB = sheet.getRange("B2:B").getValues();
var ColBLast = ColB.filter(String).length;
var Row = ColBLast + 1;
// Apply Break to each member of the chunk
// Source: https://stackoverflow.com/questions/9329446/for-each-over-an-array-in-javascript
for (const element of EmployeeChunks[1]) {
console.log(element);
Row = Row + 1;
var cell = sheet.getRange(Row,2);
cell.setValue("Break2");
}
//Chunk 3
// Define the row to start on
var ColB = sheet.getRange("B2:B").getValues();
var ColBLast = ColB.filter(String).length;
var Row = ColBLast + 1;
// Apply Break to each member of the chunk
// Source: https://stackoverflow.com/questions/9329446/for-each-over-an-array-in-javascript
for (const element of EmployeeChunks[2]) {
console.log(element);
Row = Row + 1;
var cell = sheet.getRange(Row,2);
cell.setValue("Break3");
}
//Chunk 4
// Define the row to start on
var ColB = sheet.getRange("B2:B").getValues();
var ColBLast = ColB.filter(String).length;
var Row = ColBLast + 1;
// Apply Break to each member of the chunk
// Source: https://stackoverflow.com/questions/9329446/for-each-over-an-array-in-javascript
for (const element of EmployeeChunks[3]) {
console.log(element);
Row = Row + 1;
var cell = sheet.getRange(Row,2);
cell.setValue("Break4");
}
//Chunk 5
// Define the row to start on
var ColB = sheet.getRange("B2:B").getValues();
var ColBLast = ColB.filter(String).length;
var Row = ColBLast + 1;
// Apply Break to each member of the chunk
// Source: https://stackoverflow.com/questions/9329446/for-each-over-an-array-in-javascript
for (const element of EmployeeChunks[4]) {
console.log(element);
Row = Row + 1;
var cell = sheet.getRange(Row,2);
cell.setValue("Break5");
}
//Employee Chunk Count
//EmployeeChunks[0].length
// Access a particular chunk
//console.log(EmployeeChunks[2]);
}
In an ideal world, I could base all this on another column, so if my list was in column A, in column B would be those peoples favourite activity for example, walking, hiking, swimming, cycling or rowing. For each activity, i'd then like to split those who picked them into the 5 colours, so if I had 10 for walking, 5 for hiking etc, it would be 2 of each colour for walking and 1 of each colour for hiking etc
Hope this all makes sense and thanks in advance for any help
Aucun commentaire:
Enregistrer un commentaire