Build a Raffle with Google Sheet

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

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

Prepare

Select Tools / Script commands from main menú

A new tab appears with the Google Sheet Editor and a Code.gs file. Replace the code with the code shown at the end of the post

Save the proyect as Raffle (or whatever you want) and refresh the Google Sheet tab (F5).

Raffle

A new option appears in the main menu called Raffle with a subitem Raffle.

When the user select this item a sidebar appears 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 pressent (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.

Code

Comments

[Zach Sean]

Thanks for this! One quick heads up though - as you add additional rows, the script gets incredibly slow. ~400 rows takes a couple minutes to launch, and as I'm currently at ~8300 rows, it's taking 15-20 minutes just to fire up the script.

Any thoughts on how to make this more efficient & faster?

[Jorge]

Wooaa a raffle with 8300 participants!!! Yes, the problem with my example is that I was thinking in 20-30 participants and I read row by row

The solution comes changing the way we read all rows, so change the for in the getRemains method with:

---

for(var i=3; i<ss.getLastRow()+1;){
var from = i;
var to = from+100; //google has a max of 100 rows per read
var rows = ss.getRange("A"+from+":D"+to).getValues();
for( r in rows ){

if( !rows[r][0] ){
break
}

if( !rows[r][3] ){
// rowIndex, name and surname
ret.push( [i,rows[r][0],rows[r][1]] )
}

}
i=to+1

}

I updated the gist with this change.

Thanks for your comment.

[Brendan-C]

pretty cool, i wish more stuff like was out there, theres something in me that really wants to 'hack' the google suite like this

[Jorge]

Thanks. This is an ugly example using in a single file the logic and the ui but I wanted to have it as simple as possible. I'll try to write a more elaborate example explaining everything in detail