Build a Raffle with Google Sheet

This post was published in Dev.to (https://dev.to/jagedn/build-a-raffle-with-google-sheet-part-2-1n1a)

In this post we’ll see how to build a simple application using Google Sheet.

The application consists in a raffle from a list of assistant to an event from we have the names and surnames in a sheet:

Raffle, the idea

After installing our raffle function, a new option’ll appear in the main menu of the sheet called Raffle with a sub-item Raffle.

When the user select this item a sidebar’ll appear with a (customizable) interface with a button to start a raffle:

At this moment the application will choose a random element from the sheet and show it. If the participant is present (and want the prize) the user will click at yepes or nopes it the user reject the prize:

In both case the application will mark the participant as "used" to avoid pick him again.

The admin can repeat the raffle as many times he want meanwhile remain participants.

Coding the UI

Select Tools / Script commands from main menu and create a new file called Client.html and another file called Dialog.html plus the default Code.gs file.

Client.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
  </head>
  <body>
    <h1><?!= LanguageApp.translate('Bienvenido','',Session.getActiveUserLocale()) ?></h1>

    <div>
    <p>
    <?!= LanguageApp.translate('Total Participantes activos','',Session.getActiveUserLocale()) ?>
    </p>
    <p>
    <?!= totalRemains ?>
    </p>
    </div>

    <? for(var i in prizes){ ?>
    <div>

    <? for(var c=0; c < prizes[i][1]; c++){ ?>
    <p>
    <input type="button" value="<?!=prizes[i][0]?>" onclick="google.script.run.raffle('<?!=prizes[i][0]?>')"/>
    </p>
    <? } ?>

    </div>
    <? } ?>

  </body>
</html>

This will render the sidebar once the user select the Raffle option in the menu.

We’ll show how many participants remains to participate and we’ll build a list of buttons, once per prize. In this way the admin can choose what prize to raffle in every moment

As you can see, when the admin click a prize button we’ll call a remote function sending the prize selected.

Dialog.html

<script>
var suertudoIdx = <?=suertudo[0]?>;
var prize = '<?=prize?>';

function yepes(){
   google.script.run.withSuccessHandler(google.script.host.close).yepes(suertudoIdx,prize)
}

function nopes(){
  google.script.run.withSuccessHandler(google.script.host.close).nopes(suertudoIdx,prize)
}

function notPresent(){
  google.script.run.withSuccessHandler(google.script.host.close).notPresent(suertudoIdx,prize)
}
</script>

<h1>
   Congratulations <?=suertudo[1]+" "+suertudo[2]?>
</h1>

<p>
  <image src="https://media.giphy.com/media/11sBLVxNs7v6WA/giphy.gif"/>
</p>

<p>
   <div>
     <input type="button" value="Yepes" onclick="yepes()" />
     &nbsp;
     <input type="button" value="Nopes" onclick="nopes()" />
     &nbsp;
     <input type="button" value="No Present" onclick="notPresent()" />
   </div>
</p>

This file is the template to render the winner of a prize and let to choose an action (accept, denied, and not present)

Once the admin click one of buttons following actions happen:

the dialog call a remote function to notify the action selected
when the remote function is executed the dialog is closed.

For example if the winner accept the prize the dialog will execute this:

google.script.run.withSuccessHandler(google.script.host.close).yepes(suertudoIdx,prize)

where yepes is a remote function

Coding the Business

Paste this code into the Code.gs

//Called by Google Sheet
function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createAddonMenu().addItem('Raffle', 'raffleUI').addToUi();
}

// Read G3:H999 searching prizzes (title and quantity)
function getPrizzes(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var ret = [];

  for(var i=3; i<ss.getLastRow()+1; i++){
    var row = ss.getRange("G"+i+":H"+i).getValues()[0];
    if( !row[0] ){
      break
    }
    // extract the title and the quantity
    ret.push( [row[0],row[1]] )
  }
  return ret;
}

// Write the G3:H999 range with new prizzes status
function updatePrizzes(prizzes){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  ss.getRange("G3:H"+(2+prizzes.length)).setValues(prizzes)
}

// Search particpants without prizzes in range A3:D999
function getRemains(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var ret = [];

  for(var i=3; i<ss.getLastRow()+1; i++){
    var row = ss.getRange("A"+i+":D"+i).getValues()[0];
    if( !row[0] ){
      break
    }
    if( !row[3] ){
      // rowIndex, name and surname
      ret.push( [i,row[0],row[1]] )
    }
  }
  return ret;
}

// The winner accept the prize:
// update their cell and decrement the prize
function yepes(idx, prize){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getRange("D"+idx);
  cell.setValue("winner of "+prize);
  var prizzes = getPrizzes();
  for(var i in prizzes){
    if( prizzes[i][0] === prize ){
      prizzes[i][1]--;
      updatePrizzes(prizzes)
      break;
    }
  }

  raffleUI();
}

// the winner decline the prize: do nothing
function nopes(idx, prize){
  raffleUI();
}

// the winner is not pressent: bad guy
function notPresent(idx){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getRange("D"+idx);
  cell.setValue("no present");
  raffleUI();
}

// Show the winner and ask if they want the prize
function raffle(prize){
  var remains = getRemains();
  var suertudoIndex = Math.floor(Math.random()*remains.length);
  var suertudo = remains[suertudoIndex];

  var template = HtmlService.createTemplateFromFile('Dialog');
      template.suertudo = suertudo;
      template.prize = prize;
  var html = template.evaluate();

  var htmlOutput = HtmlService
    .createHtmlOutput(html)
    .setWidth(640)
    .setHeight(480);

  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Wooaaaaa');
}

// main: prepare a sidebar with prizzes
function raffleUI(){
  var remains = getRemains();
  var template = HtmlService.createTemplateFromFile('Client');
  template.totalRemains = remains.length;
  template.prizzes = getPrizzes();
  var html = template.evaluate();
      html.setTitle("Raffle")
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}

Prepare your Raffle

In a clean tab write the participants and the prizes following this screen:

Pay attention to use the same rows and columns or if you want to use different ranges remember to adjust them into the Code.gs file

See in action

In this video you can see the raffle in action