Created
October 17, 2019 06:56
-
-
Save jagedn/b649a9db6cbfc6215b1831e71957add1 to your computer and use it in GitHub Desktop.
A more elaborate Raffle example for Google Sheet
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!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 prizzes){ ?> | |
| <div> | |
| <? for(var c=0; c < prizzes[i][1]; c++){ ?> | |
| <p> | |
| <input type="button" value="<?!=prizzes[i][0]?>" onclick="google.script.run.raffle('<?!=prizzes[i][0]?>')"/> | |
| </p> | |
| <? } ?> | |
| </div> | |
| <? } ?> | |
| </body> | |
| </html> | |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| //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 prizze: | |
| // update their cell and decrement the prizze | |
| function yepes(idx, prizze){ | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var sheet = ss.getActiveSheet(); | |
| var cell = sheet.getRange("D"+idx); | |
| cell.setValue("winner of "+prizze); | |
| var prizzes = getPrizzes(); | |
| for(var i in prizzes){ | |
| if( prizzes[i][0] === prizze ){ | |
| prizzes[i][1]--; | |
| updatePrizzes(prizzes) | |
| break; | |
| } | |
| } | |
| raffleUI(); | |
| } | |
| // the winner decline the prizze: do nothing | |
| function nopes(idx, prizze){ | |
| 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 prizze | |
| function raffle(prizze){ | |
| var remains = getRemains(); | |
| var suertudoIndex = Math.floor(Math.random()*remains.length); | |
| var suertudo = remains[suertudoIndex]; | |
| var template = HtmlService.createTemplateFromFile('Dialog'); | |
| template.suertudo = suertudo; | |
| template.prizze = prizze; | |
| 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); | |
| } | |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <script> | |
| var suertudoIdx = <?=suertudo[0]?>; | |
| var prizze = '<?=prizze?>'; | |
| function yepes(){ | |
| google.script.run.withSuccessHandler(google.script.host.close).yepes(suertudoIdx,prizze) | |
| } | |
| function nopes(){ | |
| google.script.run.withSuccessHandler(google.script.host.close).nopes(suertudoIdx,prizze) | |
| } | |
| function notPresent(){ | |
| google.script.run.withSuccessHandler(google.script.host.close).notPresent(suertudoIdx,prizze) | |
| } | |
| </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()" /> | |
| | |
| <input type="button" value="Nopes" onclick="nopes()" /> | |
| | |
| <input type="button" value="No Present" onclick="notPresent()" /> | |
| </div> | |
| </p> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment