How to make a ladder card game with Apps Script

Here we’re going to make a simple ladder card game using Apps Script and Google Sheets.

In our foreign language classes, one of the classic activities we do to help students learn new language, is to print phrases onto card, then cut the cards up. We then write the answers on the back of the cards. They then place the cards on a desk and then go through the cards one by one, trying to remember the correct answer.

If they get the right answer, they continue to the next card (i.e. up the ladder), if they don’t their partner has a go, always starting from the top again. Thus, to get to the bottom, they need to know all the correct answers, and most likely they will have had to repeat a number of the cards many times.

The problem is that we’re now teaching online and don’t have these, so I wanted to recreate it digitally.

HOW TO PLAY

The phrases and sentences that will appear on the cards, and the answers are stored in Google Sheets.

Each set of cards are on a different numbered sheet.

The sheet number is selected and then the game link is shared with the students, for example, via the Zoom chat box.

When the students click on the link, the following page is opened with the cards.

When they tap a card, it shows the opposite side with the answer. If they tap again, it returns it to the yellow side.

SO HOW DO WE MAKE THIS?

There are two files, one for the Apps Script and one for the HTML/CSS etc.

Let’s start with the Apps Script side first.

1. function doGet(e) {
  2. const ss = SpreadsheetApp.getActiveSpreadsheet(),
    3. shSheet = ss.getSheetByName('SHEET'),
    4. sheetNo = shSheet.getRange(1, 2).getValue(),
    5. sh = ss.getSheetByName(sheetNo),
    6. rowData = sh.getRange(2, 1, sh.getLastRow() - 1, 2).getValues();

Line 1: As we’re going to deploy this as a web app we need to use the doGet() function.

Line 2: As this is bound to the spreadsheet, we get the active spreadsheet.

Line 3: Get the sheet called SHEET.

Line 4: Get the sheet number selected.

Line 5: Get the sheet using the sheet number.

Line 6: Get the data on that sheet, i.e. the text we want to show on our cards, and store that in the constant rowData.

Now, we have our data, we need to style it.

  8. //Make page
  9. let html = HtmlService.createTemplateFromFile("webpage");

Line 9: We use the HtmlService to get a template from the HTML file called ‘webpage’.

Next, as we could have different amount of cards, we need to create the HTML dynamically, so that the right amount of cards are created.

11.  html.questions = rowData.map((question, n) => {
12.    return '<div><button id="' + n + 'q" class="cards show qColour">'
13.      + question[0] +
14.      '</button></div><div><button id="' + n + 'a" class="cards hide aColour">'
15.      + question[1] +
16.      '</button></div><br><br>'
17.  }).join('');

Line 11: We loop through the data in rowData, using the map method. As we do this we return the data back to the html.questions.

Line 12: We’re going to return the HTML in lines 12 to 16. We’re setting up a Div, then a button, giving it an ID of the number in the loop (so that every card has a unique number). These will be the questions cards, so I’ve used the ‘q’ suffix to denote that. Finally, we add 3 classes: cards, show, qcolour”. We use show as this card will initially be shown.

Line 13: Then we add the question text, which is in column A on our sheet, and so is in the first position in the rowData array.

Line 14: This is similar to line 12, but this time it is for the answer side of the cards. So, we close the last button and set up a new button. The ID this time will have an ‘a’ suffix. The classes this time are: cards hide aColour. We use hide as this card will initially be hidden.

Line 15: We then add the text on the answer side, from column B in our sheet.

Line 16: Finally, we close the button, the Div, and add 2 line breaks to space out the cards.

Line 17: Then we use the .join() method to add all this together.

19.  html = html.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME);
20.  return html;
21. }

Line 19: We then need to evaluate the content of html and set the sandbox mode.

Line 20: We return the html.

Line 21: Close the function.

HTML

Now let’s look at the HTML file called ‘webpage’.

1. <!DOCTYPE html>
2. <html>
3.
4. <head>
5.  <base target="_top">
6.
7.  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.6/css/bootstrap.min.css"
8.    integrity="sha384-rwoIResjU2yc3z8GV/NPeZWAv56rSmLldC3R/AZzGRnGxQQKnKkoFVhFQhNUwEyJ" crossorigin="anonymous">
9. </head>

We leave the default HTML code in there and in lines 7 and 8, I’ve added a link to the bootstrap 4 library, which can be found here. I’ve done this for styling purposes.

Then within the style tag, let’s set up the CSS styling.

10. <style>
11.  h1 {
12.    font-size: 30px;
13.    text-align: center;
14.  }
15.

Lines 11-14: Style the header – the game title

16.  .cards {
17.    border: solid 2px;
18.    color: black;
19.    padding: 30px 20px;
20.    text-align: center;
21.    text-decoration: none;
22.    font-size: 30px;
23.    margin: 4px 2px;
24.    cursor: pointer;
25.    box-shadow: 0 8px 16px 0 rgba(0, 0, 0, 0.2), 0 6px 20px 0 rgba(0, 0, 0, 0.19);
26.    width: 99%;
27.    height: 25%;
28.    border-radius: 5px;
29.    position: relative;
30.  }

Lines 16-30: Next we style the cards.

32.  .qColour {
33.    background-color: yellow;
34.  }
35.
36.  .aColour {
37.    background-color: orange;
38.  }

Lines 32-34: The question side of the card will be yellow.

Lines 36-38: The answer side will be orange.

40.  #divContainer {
41.    margin-left: 5px;
42.  }

Lines 40-42: We’ll add a little margin to the left of the main Div to position it better.

44.  .hide {
45.    display: none;
46.  }
47.
48.  .show {
49.    display: inline-block;
50.  }
51. </style>

Lines 44-51: This sets up the either displaying or hiding the cards. Then close the style tag.

53. <body>
54.  <h1>Ladder Game</h1>
55.  <div id="divContainer">
56.    <?!= questions; ?>
57.  </div>
58. </body>
59.

Lines 53-58: Then we add the body. We add the game title, then the Div which will contain the cards.

Line 56: We use scriptlet tags to add the data we have stored in questions. Note, the symbols: ?!=

60. <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

Line 60: Below we’re going to use a bit of JQuery to react to clicks made by the user. So, we need to connect to the JQuery library. This can be found here.

This is the part that will let the user interact with the cards and show the different sides of them.

61. <script>
62.  //Change card when tapped
63.    $(document).ready(function(){
64.     $("button").click(function(e){

Line 63: Within the script tags, we first make sure the document is ready.

Line 64: We then look for a click of the button, i.e. a card has been clicked on.

66.     var id = this.id;
67.     var suffix = id.substr(-1);
68.     var qNo = id.substring(0, id.length - 1);

Line 66: We get the ID of the current element clicked.

Line 67: We get the suffix of that ID, either it’ll be q or a.

Line 68: We get the card number clicked, by extracting just the number from the ID.

70.     if(suffix == "q"){
71.      $("#"+id).hide();
72.      $("#"+qNo+"a").show();
73.     }
74.     else if(suffix == "a"){
75.      $("#"+id).hide();
76.      $("#"+qNo+"q").show();
77.     }
78.    });
79.   });
80.  </script>
81. 
82. </html>

Line 70: We check if the suffix is a q.

Line 71: If so, we hide the q card.

Line 72: We then show the a card.

Line 74: If not, we check it’s an a.

Lines 75-76: We do the opposite here, we hide the a card and show the q card.

We then close all the functions, the script and html tags.

DEPLOY THE WEB APP

Now, we need to deploy our code as a web app. Go to Deploy > New deployment.

Leave the settings as is and click Deploy. Then click Authorize access.

Click on your account.

Click Advanced.

Click Go to Ladder Game (unsafe) – It is!

Click Allow.

Then copy the URL under the Web app section and paste it in your Google Sheet. I usually create a short bit.ly link as I find them easier to share, but that’s optional.

The game is then ready to play!

To use a different set of cards, just change the sheet number on the Google Sheet. The link always remains the same.

Click here to make a copy of the Google sheet file.

a