Pick Random Members from Team with Apps script

We always get the need of select a member from team for party game or giving party to team and for this we always gets confuse how to pick random member, should we write numbers on a small paper and distribute/assign them.  But with this script you will have to just click a button and it will choose a member for you.

This script can be modified with your requirement, if you want to choose a random number within a set of number, then you can also replace the name with numbers.

Pre-Requisites:

Free Gmail Account or Google Apps Account

Setting Up the Script:

Setting up this script is also similar to the scripts we have already discussed. Just follow the steps mentioned below.

• Open you Google Drive.
• Create a new spreadsheet.
• Go to Tools > Script Editor
• Copy paste the below script

function onOpen(){
  SpreadsheetApp.getUi().createMenu('Pick Random').addItem('Pick', 'randompick').addToUi(); // Adding Menu in SpreadSheet
}
function randompick() {
var selectsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var fulllist = [["John"],["Arya"],["Snow"],["Sansa"],["Ricon"],["Bran"]]; // Enter comma seperated members list
selectsheet.setFrozenRows(1);
try
{
selectsheet.getRange(2,2).clearContent();
var data = selectsheet.getRange("A2:A" + selectsheet.getLastRow()).getValues();
}
catch(e)
{
data = fulllist;
}
var index = Math.floor(Math.random() * data.length);
var theChosenOne = data.splice(index,1);
SpreadsheetApp.getUi().alert(theChosenOne + ' has been selected.');

if(data.length >0.0)
{
selectsheet.clear();
selectsheet.appendRow(["Members", "Selected Members"]).setFrozenRows(1);
selectsheet.getRange(1,1,1,2).setBackground('Lightgreen').setFontWeight("Bold");
selectsheet.getRange("B2").setValue(theChosenOne);
selectsheet.getRange(2, 1, data.length, 1).setValues(data);

}
else{
selectsheet.clear();
selectsheet.appendRow("Members", "Selected members").setFrozenRows(1);
selectsheet.getRange(1,1,1,2).setBackground('Lightgreen').setFontWeight("Bold");
selectsheet.getRange("B2").setValue(theChosenOne);
}
SpreadsheetApp.flush()

}

After pasting the the above script in the script editor, it should look like this

Now you need to fill your own data like your members name

Lets refer the above screenshot

• Change my team with your team members in Line number 8.

Run the Script

At first lets update the menu in the spreadsheet.

Select the “onOpen” in the script run menu

and click on play option beside that. It will add the Menu in the spreadsheet.

Now you can run the script from the menu, You can close the script tag. This menu will stay in the spreadsheet when ever you will open the sheet.

click on Pick Random > Pick and script will run and show you the random member and update the sheet.

NOTE:

• It will remove the name of selected member for the next run and update the sheet.
• Once all members is selected, it will update the list again with all members list.

Use the script and let us know for any doubt in comments below.