randomly select value from list and remove that item from the list

Question

I was in need of a script that would allow me to randomly select a value from a list and remove that value from the list - thereby, decrementing the total items in the list by one item each time the script was executed.

I needed this script so I could simulate the process of drawing a card at random from a deck of cards.

The solutions provided below were helpful (thank you Cooper). However, as noted, "Each new deal starts with the same cards." So, these scripts didn't quite solve my problem -- admittedly because I did a poor job of initially explaining what I needed.

After doing more research, I found this post: pick from random list and decrement result for next pick. The script provided as an answer almost solved my problem - but I needed a way to grab the range from column A instead of having to type each value into the script itself. After doing more research, I found this post: How can I store a range of cells to an array? which enabled me to know how to modify the script to solve my problem.

Here is the script that finally solved my problem:


function pickCard(){
  var ssh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = ssh.getSheetByName('Cards');
  var lastRow = ss.getLastRow();
  var completeList = ss.getRange(1, 1, lastRow, 1).getValues(); //getRange(starting Row, starting column, number of rows, number of columns)

    for(var i=0;i<(lastRow-1);i++)
    {
      Logger.log(completeList[0][i]);
    }

  var currentList = [];
  var columnWithNames = "A"; // expects: start in row 1; no empty cells 
  try{var populatedRange = getPopulatedRange(ss,columnWithNames); currentList = populatedRange.getValues(); populatedRange.clear()}
  catch (e) {currentList = completeList; Logger.log(e)} //if the ss list is exhausted, currentList will be filled from the completList array
  var randomIndex = Math.floor(Math.random() * currentList.length);
  var theChosenOne = currentList.splice(randomIndex,1)
  if(currentList.length>0)
  ss.getRange(1,1,currentList.length,1).setValues(currentList);
  ss.getRange("B1").setValue(theChosenOne)
}

function getPopulatedRange(ss,column) { //Returns the range in a single column from row 1 to the last populated cell in that column
  var lastRow = ss.getMaxRows();
  var values = ss.getRange(column + "1:" + column + lastRow).getValues();
  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return ss.getRange(column  + "1:" + column + lastRow);
}

I have to apologize for my poor English skill. Unfortunately, I cannot understand about `I need a script that will randomly select a value from a set of 52 values stored in a Column (e.g. Column 'A') of a Google Sheet, paste that random value in a cell (e.g., 'C1'), and remove that value from the original set, thereby leaving 51 values in the column.`. In this case, when the script is run several times, how does the values on the Spreadsheet change? — Tanaike, May 28 '20 at 03:10
What about the holes created within? Do you want to leave the holes or gaps (whatever you say it) as it is after a card is drawn? For the others, you might want to 1. generate a `random number` say `n` where `n is any number between 1 and 52` 2. Select that cell. If n is 50. You select A50. 3. Get the value of the cell and `set` it in the other column. 4. Optionally, select A`n+1` to A52 and set them to one cell above if the cards must fill up the blank spaces — , May 28 '20 at 03:38

Cooper · Answer 1 · 2020-05-28T20:15:53.597

Note: Each new deal starts with the same cards. My sheet named cards just has numbers from 1 to 52 in the first 52 rows of ColumnA

function dealNCards(n) {
  var n=n||6;
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Cards');
  const rg=sh.getRange(1,1,sh.getLastRow(),1);
  const cards=rg.getDisplayValues().map(function(r){return r[0];});
  var deal=[];
  do{
    let idx=Math.floor(Math.random()*cards.length);
    deal.push(cards[idx]);
    cards.splice(idx,1);    
  }while(deal.length<n);
  var s=Utilities.formatString('<br /><input type="button" value="Deal %s Cards " onClick="google.script.run.dealNCards(%s)" />',n+1,n+1)
  var html=deal.join(',')+s;
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'The Deal')
  return deal;
}

Here's another version:

function dealNCards(n) {
  var n=n||24;
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Cards');
  const rg=sh.getRange(1,1,sh.getLastRow(),1);
  const cards=rg.getDisplayValues().map(function(r){return r[0];});
  var deal=[];
  do{
    let idx=Math.floor(Math.random()*cards.length);
    deal.push(cards[idx]);
    cards.splice(idx,1);    
  }while(deal.length<n && cards.length>0);
  var s=Utilities.formatString('<br /><input type="button" value="Deal %s Cards " onClick="google.script.run.dealNCards(%s)" />',n+1,n+1);
  var p=Utilities.formatString('Dealt %s cards.<br />',deal.length)
  var html=p+ deal.join(',')+s;
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'The Deal')
  return deal;
}

Here's a much more fun version. It will deal n cards into m hands until it runs out of cards.

function dealnCardsmHands(n,m) {
  var n=n||5;
  var m=m||4;
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Cards');
  const rg=sh.getRange(1,1,sh.getLastRow(),1);
  const cards=rg.getDisplayValues().map(function(r){return r[0];});
  var deal=[];
  var hands={};
  for(let i=0;i<m;i++) {
    hands[i+1]=[];
  }
  do{
    for(let i=0;i<m;i++) {
      let idx=Math.floor(Math.random()*cards.length);
      hands[i+1].push(cards[idx]);
      cards.splice(idx,1);  
    }
  }while(hands[m].length<n && cards.length>=m);
  var html=Utilities.formatString('<br />Hands: %s Cards: %s',m,hands[m].length);
  for(let i=0;i<m;i++) {
    html+=Utilities.formatString('<br />Hand%s: %s',i+1,hands[i+1].join(','))
  }
  html+=Utilities.formatString('<br /><input type="text" id="cards" value="%s" />Cards',hands[m].length);
  html+=Utilities.formatString('<br /><input type="text" id="hands" value="%s" />Hands',m);
  html+='<br /><input type="button" value="Deal" onclick="deal();" />';
  html+='<script>function deal(){google.script.run.dealnCardsmHands(document.getElementById("cards").value,document.getElementById("hands").value);}</script>';
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'The Deal')
  return deal;
}

This deals the suits out without using a spreadsheet.

function dealnCardsmHands(n,m) {
  var cA=['A','2','3','4','5','6','7','8','9','10','J','Q','K'];
  var sA=['s','d','h','c'];
  cards=[];
  sA.forEach(function(s,i){
    cA.forEach(function(c,j){
      cards.push(String(c+s));
    });
  });        
  var n=n||5;
  var m=m||4;
  //const ss=SpreadsheetApp.getActive();
  //const sh=ss.getSheetByName('Cards');
  //const rg=sh.getRange(1,1,sh.getLastRow(),1);
  //const cards=rg.getDisplayValues().map(function(r){return r[0];});
  var deal=[];
  var hands={};
  for(let i=0;i<m;i++) {
    hands[i+1]=[];
  }
  do{
    for(let i=0;i<m;i++) {
      let idx=Math.floor(Math.random()*cards.length);
      hands[i+1].push(cards[idx]);
      cards.splice(idx,1);  
    }
  }while(hands[m].length<n && cards.length>=m);
  var html=Utilities.formatString('<br />Hands: %s Cards: %s',m,hands[m].length);
  for(let i=0;i<m;i++) {
    html+=Utilities.formatString('<br />Hand%s: %s',i+1,hands[i+1].join(','))
  }
  html+=Utilities.formatString('<br /><input type="text" id="cards" value="%s" />Cards',hands[m].length);
  html+=Utilities.formatString('<br /><input type="text" id="hands" value="%s" />Hands',m);
  html+='<br /><input type="button" value="Deal" onclick="deal();" />';
  html+='<script>function deal(){google.script.run.dealnCardsmHands(document.getElementById("cards").value,document.getElementById("hands").value);}</script>';
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'The Deal')
  return deal;
}

This version deals the hands and also sorts them by suit and rank.

function dealnCardsmHands(n,m) {
  var cA=['A','K','Q','J','10','9','8','7','6','5','4','3','2']
  var sA=['s','d','h','c'];
  var cards=[];
  sA.forEach(function(s,i){
    cA.forEach(function(c,j){
      cards.push(String(c+s));
    });
  });        
  var n=n||12;
  var m=m||4;
  //const ss=SpreadsheetApp.getActive();
  //const sh=ss.getSheetByName('Cards');
  //const rg=sh.getRange(1,1,sh.getLastRow(),1);
  //const cards=rg.getDisplayValues().map(function(r){return r[0];});
  var deal=[];
  var hands={};
  for(let i=0;i<m;i++) {
    hands[i+1]=[];
  }
  do{
    for(let i=0;i<m;i++) {
      let idx=Math.floor(Math.random()*cards.length);
      hands[i+1].push(cards[idx]);
      cards.splice(idx,1);  
    }
  }while(hands[m].length<n && cards.length>=m);
  var html=Utilities.formatString('<br />Hands: %s Cards: %s',m,hands[m].length);
  for(let i=0;i<m;i++) {
    hands[i+1].sort(function(a,b){
      var aidx=sA.indexOf(a.slice(-1));
      var bidx=sA.indexOf(b.slice(-1));
      if(aidx!=bidx) {
        return aidx-bidx;
      }else{
        var A=cA.indexOf(a.slice(0,-1));
        var B=cA.indexOf(b.slice(0,-1));
        return A-B;
      }
    });
    html+=Utilities.formatString('<br />Hand%s: %s',i+1,hands[i+1].join(','))
  }
  html+=Utilities.formatString('<br /><input type="text" id="cards" value="%s" />Cards',hands[m].length);
  html+=Utilities.formatString('<br /><input type="text" id="hands" value="%s" />Hands',m);
  html+='<br /><input type="button" value="Deal" onclick="deal();" />';
  html+='<script>function deal(){google.script.run.dealnCardsmHands(document.getElementById("cards").value,document.getElementById("hands").value);}</script>';
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'The Deal')
  return deal;
}

As you can tell I rather enjoyed playing around with this. – Cooper May 28 '20 at 06:02 — Cooper, May 28 '20 at 06:02

randomly select value from list and remove that item from the list

1 Answers1