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 images 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 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 in 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! Then, 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!

Sheets “Mad Lib” style activity

This Halloween, I wanted to create a fun activity for students to review the parts of speech and also get a great first impression of Google Sheets. To do this, I decided a “Mad Lib” style activity would be perfect!

TEMPLATE

Pre-writing

The first thing I did was write a simple story in a Google Doc. I underlined the nouns, verbs, adjectives and adverbs I thought would be fun to replace. I decided to keep the story simple as we all know that sometimes less is more.

Google Sheets

I created a new Sheet by typing in Sheets.new. Then, I merged the cells in the top row to add the title. I froze the first few rows to allow the headers to remain while students typed the answers.

Next, I added the parts of speech and a simple definition in column A. I then wrote the text “Answer #” in Column B to serve as a placeholder in the story. I hid the remaining columns and rows so students could focus on the activity.

If you don’t know how to hide a column or row it is quite simple. Right click on the name of the column or row, and now you can select “HIDE”. That is why you do not see any other cells in this activity.

Right-click and select hide to hide empty cells.

Adding the Formula

I wanted the words from Sheet 1 (titled MadLib) to appear in the story on Sheet 2 (titled Your Story). To do this, I merged cells, added titles, inserted clipart, and used the paint bucket to make the Sheet look festive. For the story, I merged numerous cells to create one large space for the formula. I also hid the remaining columns and cells.

I wrote a formula where the text would appear in quotes, and the answers would appear whenever I added the formula & Madlib!Cellnumber & . So for example, if I wanted the answer for the proper noun in cell B5 to appear in the story, I would write & Madlib!B5 &. This took a few practices and revisions with spacing and punctuation. (Notice how the words “Answer #” from Sheet 1 appear in the text as placeholders.) In the end the formula looked like this

Formula and Activity

Once satisfied, I tested the “Mad Lib” style activity out on my 9 year old son. After many laughs, I decided it was ready to share! I’m sure there will be many revisions to come, but that’s part of the fun!

Have you ever created your own Mad Libs? I’d love to see some examples.

St. Patrick’s Day Hyperdoc

Coaching Question: Do you have any fun and engaging resources for St. Patrick’s Day?

It seems like #hyperdocs are all the rage these days! Here is a fun and engaging resource to use with students in Grades 3-12. Disclaimer: You do need a subscription to BrainPOP to use this hyperdoc unless you change the video. Students will be watching the video and reading about St. Patrick’s Day in the FYI section below.

Screen Shot 2017-03-14 at 2.49.19 PM

What is a Hyperdoc?

Hyperdoc is a term used to describe an interactive Google Doc. By sharing a hyperdoc, students can experience resources such as links to websites, videos and tutorials. Usually, teachers include a task for students to create a presentation or organize a response to demonstrate their understanding. Hyperdocs are often self-paced and can be differentiated for all learners. Students could also collaborate and work on the hyperdoc together!

My hyperdoc includes activities to:

  • Activate prior knowledge
  • Engage
  • Apply/Create
  • Share

Access the St. Patrick’s Day Hyperdoc

Screen Shot 2017-03-14 at 2.52.07 PM

Click here to “Make a Copy” and put your own spin on this St. Patrick’s Day hyperdoc.

You can find some more templates at http://hyperdocs.co/templates.

Distributing to Your Students

There are many different ways to distribute this hyperdoc to your students.

My recommendations are:

  1. Google Classroom: Make a copy for each student.
  2. Seesaw: Push the copy out to all students. Make sure you set permissions as View-Only so they make a copy to publish on Seesaw when finished
  3. Click SHARE in Google Docs: and type in student emails
  4. Create a QR Code: Set as View-Only so students can Make a Copy to email you.

Do you have any hyperdocs you’d like to share? If so, post a link in the comments below.

Valentine’s Day Drag and Drop K-2

This Valentine’s Day, consider having students express their love for education by creating a customized card for their school!

Using Google Drawings, students can drag and drop tiles onto the card. Teachers may modify the tiles if needed. Students may then use the paint bucket to color the tiles, add images, change fonts, and personalize the blank tiles with their own writing

screen-shot-2017-02-02-at-9-20-00-pm

Click here for a copy of the template. 

To Share:

  • Print the cards
  • Create QR Codes
  • Add to a Padlet Wall
  • Share to a Seesaw Classroom
  • Post to Google Classroom
  • Create a screencast video
  • Share via social media
  • Add links to websites

Happy Valentine’s Day!