## DEV Community

Jorge Eψ=Ĥψ

Posted on • Updated on

# 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

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 Eψ=Ĥψ

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.

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 Eψ=Ĥψ

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