lundi 22 novembre 2021

Splitting lists into equal chunks

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