DEV Community

loading...

Build a Raffle with Google Sheet

jagedn profile image Jorge Eψ=Ĥψ Updated on ・1 min read

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:

Example

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:

Sidebar 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:

winner

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

Discussion (4)

pic
Editor guide
Collapse
probszachsean profile image
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?

Collapse
jagedn profile image
Jorge Eψ=Ĥψ Author

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.

Collapse
brendan_c profile image
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

Collapse
jagedn profile image
Jorge Eψ=Ĥψ Author

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