Valentine’s Day: Sheets Coding Game

Last week, I opened up a Google Sheet and all I could see was a fun coding game waiting to be designed for kids! I had a blast creating the game and decided to give it a Valentine’s Day theme due to the upcoming holiday!

Since posting a link to this Sheets coding game on Twitter (www.tinyurl.com/ValentinesCoding), I’ve had quite a few inquiries asking about the process. So, here are the steps for those that are interested! And, here’s the game Template if you want to check it out!

Step 1: Create a Cover Image for Your Game

I knew I wanted the first Tab to be the cover of my game. So, I merged the cells in the Sheet to create a space for the image. I created a basic image with Word Art and shapes by using Google Drawings. Once finished, I downloaded the image as a PNG File. Then, I went back into Sheets and selected Insert-Image to place the image into the cells in the Sheet. I cropped out all the extra cells by using The Crop Sheet Add-on but you could also crop the Sheet cells out manually.

You may insert an image inside or over a cell.

Step 2: Design the First Level

The paint bucket allowed me to fill in the different cells with color. I eliminated the cell borders in each Sheet except for the area where the coding challenge would be located. Titles and directions were added by merging cells in order to make room for text. I used OpenMoji.com to add decorations and characters into the game.

Select the boarders for your cell grid lines.
Example level with and without grid lines

Step 3: Data Validation

The Data Validation tool in Sheets made it simple to add the different answer choice into a dropdown list for each level of the game. This feature also allowed me to make the correct answer choices fill with green paint while the incorrect answers remained red. This would make it easy for students to self-correct!

Go to Data and select Validation to provide a list of choices to complete each level.

Step 4: Adding Coding Block Images

I decided I wanted a corresponding coding block image to appear once students selected answers. To do this, I created the images in Google Drawings and then went to File-Publish to get a direct URL to the image. I created a Sheet Tab with links to all the images and posted the URLs in there. (You could HIDE this Tab.)

The VLookup formula allowed me to have the spreadsheet insert the images that match the student answer choices. (If you open the Template you can see all the formulas underneath the answer choices and modify them as you wish. And, if you don’t know a lot about formulas, you could just modify my Sheet as you will most likely see the patterns. I don’t know too much about advanced formulas myself.)

Step 5: Create a Congratulatory Image

To show students they completed the level, I decided I wanted an image to appear on the Sheet stating they completed the level. To do this, I assigned each correct answer choice a point value by using an IF statement such =IF(O3=”East”,1,0). I placed this formula in the cell below the data validation choices. I merged the cells below the coding choices to allow room for the image and inserted another IF statement to make the image appear only if a student gained enough points by selecting the correct answers.

Use an IF statement to have a celebratory image appear!
Example of VLook Images Appearing

Step 6: Duplicate Tabs to Create New Levels

Once I had my first level completed, I then right-clicked on the tab and duplicated the level. I then created new levels by changing colors, directions, data validation choices, and images! I had my 10-year-old give the game a test run! He loved it! So…..now I share with you!

Have you created any coding games with Sheets? Can you modify and edit this game to make it even better? I’d love to hear from you!

Passionate about all things edtech.

Share

Leave a Reply