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
| function onOpen(e) { | |
| var ui = SpreadsheetApp.getUi(); | |
| ui.createAddonMenu().addItem('Raffle', 'raffleUI').addToUi(); | |
| } | |
| function getRemains(){ | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var sheet = ss.getActiveSheet(); | |
| var ret = []; | |
| 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 | |
| } | |
| return ret; | |
| } | |
| function yepes(idx){ | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var sheet = ss.getActiveSheet(); | |
| var cell = sheet.getRange("C"+idx); | |
| cell.setValue("yepes"); | |
| raffleUI(); | |
| } | |
| function nopes(idx){ | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var sheet = ss.getActiveSheet(); | |
| var cell = sheet.getRange("C"+idx); | |
| cell.setValue("nopes"); | |
| raffleUI(); | |
| } | |
| function raffle(){ | |
| var remains = getRemains(); | |
| var suertudoIndex = Math.floor(Math.random()*remains.length); | |
| var suertudo = remains[suertudoIndex]; | |
| Logger.log(suertudo) | |
| var htmlOutput = HtmlService | |
| .createHtmlOutput('<p>'+ | |
| '<image src="https://media.giphy.com/media/11sBLVxNs7v6WA/giphy.gif">'+ | |
| '</p>'+ | |
| '<p>'+ | |
| '<div>'+ | |
| '<input type="button" value="Yepes" onclick="google.script.run.withSuccessHandler(google.script.host.close).yepes('+suertudo[0]+')" /> '+ | |
| '<input type="button" value="Nopes" onclick="google.script.run.withSuccessHandler(google.script.host.close).nopes('+suertudo[0]+')" />'+ | |
| '</div>'+ | |
| '</p>' | |
| ) | |
| .setWidth(550) | |
| .setHeight(350); | |
| SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Congratulations '+suertudo[2]+','+suertudo[1]); | |
| } | |
| function raffleUI(){ | |
| var remains = getRemains(); | |
| var 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>'+ | |
| '<div> There are ' + remains.length + ' participants</div>'+ | |
| '<p>'+ | |
| '<div><input type="button" value="Sortear" onclick="google.script.run.raffle()" />'+ | |
| '</p>'+ | |
| '</body>'+ | |
| '</html>' | |
| var htmlOutput = HtmlService | |
| .createHtmlOutput(html) | |
| .setTitle('Raffle'); | |
| SpreadsheetApp.getUi().showSidebar(htmlOutput); | |
| } |
Top comments (4)
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?
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
forin 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.
pretty cool, i wish more stuff like was out there, theres something in me that really wants to 'hack' the google suite like this
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