It’s almost World Cup time and I thought what better way to get into it than to create a little World Cup game amongst friends and colleagues using Google Sheets, Sites, and Apps Script.
This year I decided to create a game where the participants guess how many goals will be scored in each stage and the winner is the person with the total number of goals nearest the actual final figure.
The set up is relatively simple:
-Google Site with 3 pages:
- HOME – To explain how the game works and the rules
- TABLE – Showing the players guesses against the current number of goals and up to the current stage of the tournament
- REGISTER – Web form where the players will enter their details and guesses
This is what the players will use to play the game.
-Google Sheet with 3 tabs:
- REGISTER – To store the participants’ details and guesses
- RESULTS – To record the match results and update the tables
- TABLES – Automatically updating tables based on the goals scored and the guesses
This stores the data for the game. Most is automatic except for entering the match results. It also contains a couple of examples of the new LAMBDA, MAP, BYROW functions.
-Apps Script Projects – One project bound to the Sheet with 6 script files, and one stand-alone project which will update the table
Here’s a link to the folder which contains all the files. You’ll need to make a copy of them.
OK, let’s look at the code that makes all this happen.
Set up web app form
1WebApp.gs
First, we are going to create a web form for the participants to register.
function doGet(e) {
const temp = HtmlService.createTemplateFromFile('2WebForm');
return temp.evaluate();
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}The template for the form is in the file 2WebForm (see below). So, first we need to get that.
I’ve also added the include function here, which will add a JavaScript file (3formJS.gs) at the end of the HTML.
Create web form
2WebForm.html
This file produces the registration form. It adds some JavaScript which will register the player’s details and guesses on the Google Sheet, and also show a confirmation message once submitted.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
<link href='https://fonts.googleapis.com/css?family=Nunito' rel='stylesheet'>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/0.97.3/css/materialize.min.css">
</head>In the <head> section we connect to a couple of external sources to get a specific font, Nunito, and to connect to the Materialize library, which will provide the styling and functionality to reduce the amount of code needed.
<style>
body {
padding: 25px 5px 100px 5px;
}
h3 {
text-align: center;
}
h3,
p,
button {
font-family: 'Nunito', 'Verdana', sans-serif;
}
p {
font-size: 18px;
}
#confMsg {
display: none;
}
</style>In the <style> section, we’re going to give the form some basic styling, like padding, font styling, and also hide the confirmation message div which we will show when the form is submitted.
Next, we set up the page and the form.
<body>
<div class="container">
<div class="row" id="title">
<h3 class="card-panel #388e3c green darken-2 #ffffff white-text center">
BazR's World Cup 2022
</h3>
</div>
<div class="row" id="confMsg">
<p class="card-panel orange">
Form submitted. Good luck!
</p>
</div>
<div class="row" id="instr">
<p class="card-panel yellow">
Fill in the form with your name, team name, your email, and guess how many goals will be scored in each phase of
the tournament. Then press "Submit".
</p>
</div>
<div class="row" id="tip">
<p class="card-panel yellow">¿Did you know that the average number of goals scored in the World Cups is 160? Good luck!
</p>
</div>
<form class="col s12" id="regForm">
<div class="row">
<div class="input-field col s12">
<i class="material-icons prefix">face</i>
<input id = "name" type = "text" length = "100" />
<label for="name">Name</label>
</div>
</div>
<div class="row">
<div class="input-field col s12">
<i class="material-icons prefix">people_outline</i>
<input id="teamName" type="text" length="40" />
<label for="teamName">Team name</label>
</div>
</div>
<div class="row">
<div class="input-field col s12">
<i class="material-icons prefix">mail_outline</i>
<input id="email" type="email" class="validate"/>
<label for="email">Email</label>
<span class="helper-text" data-error="Email incorrect format" data-success="OK"></span>
</div>
</div>
<div class="row">
<div class="input-field col s12">
<i class="material-icons prefix">code</i>
<br>
<p class="range-field">
<input type="range" id="goals32" min="0" max="200" />
</p>
<label for="goals32">Goals in group stage (48)</label>
</div>
</div>
<div class="row">
<div class="input-field col s12">
<i class="material-icons prefix">code</i>
<label for="goals16">Goals in last 16 (16)</label>
<br>
<p class="range-field">
<input type="range" id="goals16" min="0" max="80" />
</p>
</div>
</div>
<div class="row">
<div class="input-field col s12">
<i class="material-icons prefix">code</i>
<label for="goals8">Goals in quarter-finals (8)</label>
<br>
<p class="range-field">
<input type="range" id="goals8" min="0" max="40" />
</p>
</div>
</div>
<div class="row">
<div class="input-field col s12">
<i class="material-icons prefix">code</i>
<label for="goals4">Goals in semi-finals (4)</label>
<br>
<p class="range-field">
<input type="range" id="goals4" min="0" max="20" />
</p>
</div>
</div>
<div class="row">
<div class="input-field col s12">
<i class="material-icons prefix">code</i>
<label for="goals2">Goals in 3rd place play off and final (4)</label>
<br>
<p class="range-field">
<input type="range" id="goals2" min="0" max="20" />
</p>
</div>
</div>
<div class="row">
<button id="sendBtn" class="btn-large green darken-2 white-text col s12 waves-effect waves-light">
<i class="material-icons left">send</i>SUBMIT
</button>
</div>
</form>
</div>In the <body> section I’ve used 3 card panels to show the title, instructions, and a little tip, and the confirmation message.
Then we have the form, where we are using icons from the materialize site. I’ve added a character count to give users an idea of the maximum number of characters they should enter, but I haven’t actually limited it to those, but a check could be added.
The email goes red if the format isn’t correct.
Then we have a series of sliders, where players will enter the number of goals. I’ve set different limits to give players an idea of the likely number of goals in each stage.
Then at the bottom there is a submit button to send the form and its data.
<script type="text/javascript" src="https://code.jquery.com/jquery-2.1.1.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/0.97.3/js/materialize.min.js">
</script>
<?!= include('3formJS'); ?>
</body>
</html>At the bottom, we have some JavaScript. First, we need to connect to the JQuery library as we’re going to use a bit of that in the next part. We also connect to the Materialize JS library. Finally, we need to connect the next part, which contains some JavaScript, so we use the include function for that.
Add JavaScript to web form
3formJS.html
Here, we need to add some JavaScript elements, and most importantly the functions that will be called when the form is submitted.
<!--MATERIALIZE-->
<script>
//CHARACTER COUNT
$(document).ready(function() {
$('input#input_text, textarea#textarea1').characterCounter();
});
$("#regForm").submit(function(e) {
e.preventDefault();
});
//Listen for send button click
document.getElementById("sendBtn").addEventListener("click", sendDataFromForm);
//GET DATA FROM FORM, SEND IT TO registerData AND RESET FORM
function sendDataFromForm(){
let formData = {};
formData.name = document.getElementById("name").value;
formData.teamName = document.getElementById("teamName").value;
formData.email = document.getElementById("email").value;
formData.goals32 = document.getElementById("goals32").value;
formData.goals16 = document.getElementById("goals16").value;
formData.goals8 = document.getElementById("goals8").value;
formData.goals4 = document.getElementById("goals4").value;
formData.goals2 = document.getElementById("goals2").value;
//Runs registerData first then showConfirmation
google.script.run.withSuccessHandler(showConfirmation(document)).registerData(formData);
}The first part provides the character count functionality.
The second part is to prevent the default action when the submit button is clicked, otherwise you will get a blank page appear.
Next, we have the event listener, which will call the sendDataFromForm function when the submit button is clicked.
Then, we have the sendDataFromForm function, which gets the values from the form.
It then runs the registerData function, which is in the next part, which will append the player’s details and guesses to the Google Sheet. It also passed the formData object to it.
//Show confirmation message and hide other elements
function showConfirmation(document){
document.getElementById('confMsg').style.display = "block";
document.getElementById('instr').style.display = "none";
document.getElementById('tip').style.display = "none";
document.getElementById('regForm').style.display = "none";
}
</script>If that is successful, it will then call the showConfirmation function to show the confirmation message.
This shows the hidden confMsg div and hides the other divs, so only the title and the confirmation message are seen, when the form submitted.
Register the player’s details and guesses and send email
4registerAndEmail.gs
When the form is submitted it calls the registerData function, which will append a row with the player’s submission on the REGISTER sheet.
The script will also calculate the total number of goals the player has predicted up to each stage of the tournament, which will be used on the tables.
It then sends the player a confirmation email with their details and guesses.
function registerData(formData) {
const ss = SpreadsheetApp.openById('1d6-uqml5otmn9jl4FuQDam_RiWbC5bliOqobskbbcrY');
const shRegister = ss.getSheetByName('REGISTER');
const goalsArray = [formData.goals32, formData.goals16, formData.goals8, formData.goals4, formData.goals2];
const totalGoals = goalsArray.reduce((runningTotal, current) => {
return Number(runningTotal) + Number(current);
});
const g2OF = Number(formData.goals32) + Number(formData.goals16);
const g2QF = g2OF + Number(formData.goals8);
const g2SF = g2QF + Number(formData.goals4);
shRegister.appendRow([
formData.name,
formData.teamName,
formData.email,
formData.goals32,
formData.goals16,
formData.goals8,
formData.goals4,
formData.goals2,
g2OF,
g2QF,
g2SF,
totalGoals
]);
sendConfEmail(formData, totalGoals);
}It extracts the predictions from the formData object, then sums them using the reduce method.
I also wanted to add the running total of goals at each stage of the tournament. Note, for the overall goals I could have just added the last stage to the semi-finals figure.
Then, append all the data to the REGISTER sheet.
It then calls the sendConfEmail function below.
function sendConfEmail(formData, totalGoals) {
const subject = `BazR's World Cup 2022 Game - Sign up Confirmation: ${formData.teamName}`;
const image = DriveApp.getFileById("1jZ5rUL1JuJLEdEDpBqnH5s0hR0m1QJzG").getBlob();
let emailBody = HtmlService.createTemplateFromFile('5ConfEmail').evaluate().getContent();
emailBody = emailBody.replace('#NAME', formData.name);
emailBody = emailBody.replace('#TEAMNAME', formData.teamName);
emailBody = emailBody.replace('#GOALS32', formData.goals32);
emailBody = emailBody.replace('#GOALS16', formData.goals16);
emailBody = emailBody.replace('#GOALS8', formData.goals8);
emailBody = emailBody.replace('#GOALS4', formData.goals4);
emailBody = emailBody.replace('#GOALS2', formData.goals2);
emailBody = emailBody.replace('#GOALSTOTAL', totalGoals);
MailApp.sendEmail(formData.email.toString(), subject, emailBody, {
htmlBody: emailBody,
inlineImages: { logo: image },
name: "BazR's World Cup 2022"
});
}This sets up the email including using a logo image stored on my Drive. It gets the email template and then replaces the placeholders with the player’s data. Then sends the email.
Set the confirmation email template
5ConfEmail.html
This will create an email template for the confirmation email that will be sent to the player.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
h3,
table,
p {
font-family: verdana, Helvetica, sans-serif;
}
h3 {
font-size: 20px;
}
table {
font-size: 18px;
}
td {
line-height: 1.8;
padding-bottom: 10px;
padding-top: 10px;
vertical-align: text-top;
}
.zebra {
background-color: #d9ead3;
}
td,
th {
border: 1px solid #ddd;
padding: 5px;
font-size: 14px;
}
.bold {
font-weight: bold;
}
</style>
</head>First, set the style of the paragraphs and table.
<body>
<div>
<img src=cid:logo>
<h3>BAZR'S WORLD CUP 2022</h3>
<table>
<tr>
<td class="bold">NAME:
<td>#NAME
</tr>
<tr class="zebra">
<td class="bold">TEAM NAME:
<td>#TEAMNAME
</tr>
<tr>
<td class="bold">GOALS IN GROUP STAGES:
<td>#GOALS32
</tr>
<tr class="zebra">
<td class="bold">GOALS IN LAST 16:
<td>#GOALS16
</tr>
<tr>
<td class="bold">GOALS IN QUARTER-FINALS:
<td>#GOALS8
</tr>
<tr class="zebra">
<td class="bold">GOALS IN SEMI-FINALS:
<td>#GOALS4
</tr>
<tr>
<td class="bold">GOALS IN PLAY-OFF & FINAL:
<td>#GOALS2
</tr>
<tr class="zebra">
<td class="bold">TOTAL GOALS:
<td>#GOALSTOTAL
</tr>
</table>
<br>
<p>Good luck!</p>
<p>Baz</p>
</div>
</body>
</html>Then, add the text and a table to display the player’s predictions. The placeholders start with a # sign.
Update player positions on current table
6Positions.gs
This last bound script file will update the player positions on the table for the current round.
function updatePositions(e) {
if(e.range.getA1Notation() === "H8" && e.value === "TRUE"){
const shTables = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TABLES");
//Get current table
const tableToShow = shTables.getRange(1, 1).getValue();
const columns = getTableColumns(tableToShow);
let tableData = shTables.getRange(4, columns.tableFirst,
shTables.getRange(4, columns.tableFirst).getDataRegion().getLastRow() - 3, 4).getValues();
let previousPos = 1;
let previousplayerDiff = 0;
//Add positions on the league table
tableData.forEach((playerData, r) => {
let row = r + 4;
let playerDiff = playerData[3];
if (row === 4) {
shTables.getRange(row, columns.tableFirst).setValue(1);
}
else {
previousplayerDiff = tableData[row - 5][3];
if (playerDiff > previousplayerDiff) {
shTables.getRange(row, columns.tableFirst).setValue(previousPos + 1);
previousPos++;
}
else if (playerDiff === previousplayerDiff) {
shTables.getRange(row, columns.tableFirst).setValue(previousPos);
}
}
});
}
}It works on an onEdit trigger and can be triggered by ticking the checkbox in cell H8 on the RESULTS sheet. I added this as sometimes I want to update the match results on my mobile while I’m out.
There is a table for each tournament stage, so I added a drop-down menu on the TABLES sheet in cell A1, which I will change at the start of each stage. This allowed me to write one piece of code that will work for all the tables.
It gets the stage name and then runs the getTableColumns function below. This will return the column for the first column in the appropriate table and also the column where the goals scored so far is.
It then gets the appropriate table data and then loops through to work out the player positions based on their goals difference to the actual number of goals. If a player has the same goals difference they have the same position. I didn’t bother to work out, the following position if that’s the case, i.e. if there are two position 1s, the next could be 3rd, but I just put the next consecutive number, in this case 2nd.
function getTableColumns(tableToShow) {
let table = {};
table.grupos = 19;
table.oct = 24;
table.qf = 29;
table.sf = 34;
table.f = 39;
let columns = {};
columns.tableFirst = table[tableToShow];
columns.goalsScored = columns.tableFirst + 2;
return columns;
}This function has the column of the each of the tables, stored as properties in the table object. It then gets the appropriate key from the tableToShow variable.
The ‘goals scored’ column is always 2 columns on from the first column of the table, so I just add 2 to work it out.
Both columns are stored in the columns object, so that just one item is returned, and then can be extracted in the code above.
That is all the code in the bound script project. Below, I also needed to create a separate script file, to be able to show the current table on the Google Site. It needs a separate project as I need to deploy this separately from the one above.
Display the current table on the site
7showTables.gs
This will get the data in the current table and then using a HTML template will display it on the Google Site.
You will see some similarities in this code as the 6Positions one above, as we also need to get the table data, work out the column positions of the table.
function doGet(e) {
const ss = SpreadsheetApp.openById('1d6-uqml5otmn9jl4FuQDam_RiWbC5bliOqobskbbcrY');
const shTables = ss.getSheetByName('TABLES');
//Get current table
const tableToShow = shTables.getRange(1, 1).getValue();
const columns = getTableColumns(tableToShow);
//Get goals scored so far
const goalsScored = shTables.getRange(2, columns.goalsScored).getValue();
//Get playerData
let table1 = shTables.getRange(4, columns.tableFirst,
shTables.getRange(4, columns.tableFirst).getDataRegion().getLastRow() - 3, 4).getValues();
let rowData;
const table1Data = table1.map((row) => {
rowData = {
"position": row[0],
"player": row[1],
"predictedGoals": row[2],
"diff": row[3]
}
return rowData;
});
let htmlTemp = HtmlService.createTemplateFromFile('7showTable1h');
htmlTemp.data = table1Data;
htmlTemp.goals = goalsScored;
const html = htmlTemp.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME);
return html;
}It gets the current table to show from cell A1 on the TABLES sheet, then calls the getTableColumns function below, so get the first column of the table and the goals scored column, just like we saw in the script file above.
This time we get the table data as we want to display it on the Site.
We store it in the table1 variable then iterate through it with the map method, to create the rowData object, which stores the four columns of data.
We then get the HTML template and add the table data and goals scored to the htmlTemp object, so we can use it in the <script> part of the HTML file below.
The function below is the same as what we saw in the 6Positions.gs script file in the bound project.
//Get table first column and goal scored cell number
function getTableColumns(tableToShow) {
let table = {};
table.groups = 19;
table.l16 = 24;
table.qf = 29;
table.sf = 34;
table.f = 39;
let columns = {};
columns.tableFirst = table[tableToShow];
columns.goalsScored = columns.tableFirst + 2;
return columns;
}Set up the league table
7showTable1h.html
The final part of the code is to set up the league table template and add the table data to it.
<!DOCTYPE html>
<script>
const datags = <?!= JSON.stringify(data) ?>;
</script>
<html>
<head>
<base target="_top">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css" rel="stylesheet"
integrity="sha384-Zenh87qX5JnK2Jl0vWa8Ck2rdkQ2Bzep5IDxbcnCeuOxjzrPF/et3URy9Bv1WTRi" crossorigin="anonymous">
<link href='https://fonts.googleapis.com/css?family=Nunito' rel='stylesheet'>
</head>We pass in the table data to be able to loop through it later on. I’m using Bootstrap for the table styling so we connect to the cdn. Plus, I also want to use a specific font that needs adding.
<style>
body {
padding: 25px 25px 50px 25px;
}
h1,
h2,
h3 {
font-family: 'Nunito', 'verdana', sans-serif;
}
h1 {
font-weight: bold;
}
h2 {
color: blue;
}
</style>Next, we add some basic styling to the body and headers.
<body>
<h1>BazR's World Cup 2022</h1>
<h2>Goals scored so far ➡️
</h2>
<h2>⚽️⚽️⚽️⚽️⚽️⚽️ <strong><?= goals ?></strong> ⚽️⚽️⚽️⚽️⚽️⚽️
</h2>
<div>
</div>
<div>
<table id="table1" class="table table-success table-striped table-sm table-hover">
<tbody>
<tr class="table-dark">
<th>P</th>
<th>Player</th>
<th>Predicted Goals</th>
<th>Difference</th>
</tr>
</tbody>
</table>
</div>The body has some text and a table with headers to show the positions, the players, their predictions, and their difference from the current amount of goals. The table rows we’ll add later (see below).
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/tether/1.4.0/js/tether.min.js"
integrity="sha384-DztdAPBWPRXSA/3eYEEUWrWCy7G5KFbe8fFjk5JAIxUYHKkDx6Qin1DkWx51bBrb" crossorigin="anonymous">
</script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/js/bootstrap.bundle.min.js"
integrity="sha384-OERcA2EqjJCMA+/3y+gxIOqMEjwtxJY7qPCqsdltbNJuaOe923+mo//f6V8Qbsw3" crossorigin="anonymous">
</script>
</script>
<script>
$(function () {
jQuery.each(datags, function () {
$('#table1 tr:last').after('<tr><td>' +
this.position + '</td><td>' +
this.player + '</td><td>' +
this.predictedGoals + '</td><td>' +
this.diff + '</td></tr>');
})
});
</script>
</body>
</html>The final part is to add the JQuery library as we’re going to use that to add the table rows. Plus, the JavaScript libraries for Bootstrap.
Then, we add the table rows using a bit of JQuery to loop through the datags variable to create the table dynamically.
Deploying the web app
You’ll need to deploy the web app. In the editor, go to Deploy > New deployment. Type in a description, select Execute as: Me and Who has access: Anyone, then click Deploy.
You will see two links and the bottom one is the URL you need to paste into the Google Site.
Tip: If you’re using a Google Workspace paid account, to make sure anyone can open the link without any problems, as sometimes if people have multiple Google accounts, it can stop the web apps opening, I’ve found you need to edit the deployment URL. For example:
If this is the URL you get, add your /a/domain name in between script.google.com and /macros.
https://script.google.com/macros/s/AKfycbwQwM5HwrbiAGt-Tw0_tQEgzVFFzbQsvB2yHWJiPof_3R_K_GWAUWXficT3LYEYULbR/exec
//https://script.google.com/a/bazroberts.com/macros/s/AKfycbzo6W4m0ukD2Yzni-JelW_BYqnF4EPYgS6R95wXiZrZrTs8TKOQQ3k5ee5eg1E-5v8abR/exec
Embedded the web apps on Google Site
To embed the web apps on the Google Site, go to Page on the sidebar and then click “Full page embed”.
Then click “Embed”.
Then enter the URL from the deployment above and click “Insert”.
This then creates a page with that web app.
Publish the Site
For the public to use it, you will need to publish the site.
Try it out! All you need to do is copy the files in this folder, and deploy the scripts. You’ll probably want to edit the Google Site a bit too.
As a welshman, I’m very excited that Wales is in the World Cup after soo many years absent! Enjoy!!
Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).
a
