I'm working on matching a pair from a list of names in Apps Script but there's a condition attached to it which I'm trying to figure out.
<table>
<thead>
<tr>
<th>Names1</th>
<th>Names2</th>
</tr>
</thead>
<tbody>
<tr>
<td>Glen</td>
<td>Adam</td>
</tr>
<tr>
<td>John</td>
<td>Matthew</td>
</tr>
<tr>
<td>Mitch</td>
<td>Damien</td>
</tr>
<tr>
<td>Daryl</td>
<td>Jason</td>
</tr>
<tr>
<td>Steve</td>
<td>Shane</td>
</tr>
<tr>
<td>Ricky</td>
<td>Stuart</td>
</tr>
</tbody>
</table>Here's the Google sheets link explaining the input and the expected output - https://docs.google.com/spreadsheets/d/1nwu5j2ae_NQAmNQ5WlC-etd12lYMr4FDNKt3iAIjq4w/edit?usp=sharing
Checkout inline comments in cells A2 and D1
In the above example, Glen from Names1 column is mapped to Adam in Names2 column. I wish to write a randomization matching code where Glen gets mapped with every other person from Names1 and Names2 column without any repeating matches. This is what I have tried so far -
Method 1: Use a simple randomize() function but repeating matching occurs at a higher frequency
Method 2: Using a custom shuffle function
function shuffleArray(range) {
var i,j,temp;
for (i=range.length-1; i>0; i--) {
j = Math.floor(Math.random()*(i+1));
temp = range[i];
array[i] = array[j];
array[j] = temp;
}
return range;
}
Unfortunately, this method also shows repeated matching but at a very less rate and it does not do an exhaustive matching with every other person in both the lists.
Any help would be really appreciated. Thanks!
You could store what you already get in an array and everytime you get a pair check first if the array does not already contain the pair you found. If you want every possible pair, without duplicates you can do something like this:
const found = [];
for(let i=0; i<range.length; i++){
for(let j=i+1; j<range.length; j++){
const str = `${range[i]}_${range[j]}`;
if(!found.includes(str)){
found.push(str);
}
}
}
Solution1
function solution1() {
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName(`Solution1`)
const personsList = sheet.getRange(`A3:A`)
.getValues()
.filter(String)
.flat()
const teamsGenerated = sheet.getRange('C3:3')
.getValues()
.flat()
.filter(String)
.length
let newTeam
if (teamsGenerated) {
const previousTeams = sheet.getRange(3, 3, personsList.length, teamsGenerated)
.getValues()
newTeam = randomize(personsList)
while (previousTeams.some((i, index) => i.includes(newTeam[index]))) {
newTeam = randomize(personsList)
}
sheet.getRange(3, 3+teamsGenerated, newTeam.length)
.setValues(newTeam.map(i => [i]))
} else {
newTeam = randomize(personsList)
sheet.getRange(3, 3+teamsGenerated, newTeam.length)
.setValues(newTeam.map(i => [i]))
}
}
function randomize(array) {
for (let i = array.length - 1; i > 0; i--) {
const j = Math.floor(Math.random() * (i + 1));
[array[i], array[j]] = [array[j], array[i]];
}
return array
}
Taking all names from the first column, we see if there are already teams generated. If there are teams found, generate a new team where no player is in the same position as previous teams until found; then insert in next available column. If there are no teams found, insert a randomized team.
Glen gets mapped with every other person from Names1 and Names2 column without any repeating matches
It is unclear why you want to do this in Apps Script, because a plain vanilla spreadsheet formula should suffice. You can map each name to every other name in a random order like this:
=transpose( { A3; sort( filter( A$3:A, len(A$3:A), A$3:A <> A3 ), randarray( counta( filter( A$3:A, len(A$3:A), A$3:A <> A3 ) ) ), true ) } )
See the new Solution1 sheet in your sample spreadsheet. To get another iteration, click the checkbox in cell D2.
The expected output is just one iteration. Next time when I run the code, I expect to see a different set of pairing for each of the individual without repetition.
Team 1,Team 2
That sounds like a different question, but you can divide the names in A3:A into the number of teams given in B3 with this formula in cell C3:
=arrayformula(
query(
query(
iferror(
if(
{ 1, 1, 0 },
floor( mod( sequence(counta(A2:A)) - { 1, 1 }, { 9^9, B3 } ), { B3, 1 } ),
transpose( split( regexreplace( query( transpose( query(
transpose( sort( A3:A, if( len(A3:A), randbetween( sign(row(A3:A)), 9^9 ), iferror(1/0) ), true ) & char(9) ),
"", 9^9 ) ), "", 9^9 ), "\s+$", "" ), char(9) & " ", false, true ) )
)
),
"select max(Col3) where Col3 <> '' group by Col1 pivot Col2", 0
),
"offset 1", 0
)
)
The formula will create a table that lists teams, one team per column, with the names divided randomly into teams, with no names repeating.
See the new Solution2 sheet in your sample spreadsheet. To get random division into teams, click the checkbox in cell B5.