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!

Consolidate Assignments by Using Google Sheets

Are you creating beautiful daily lesson plans for students using Google Slides? Are your assignments posted into Google Classroom along with links, tasks, and templates and followed with a detailed checklist created in Docs? I’m sure you spent a lot of time creating these resources! But, are we creating too MANY resources and links for our students?

Last week, I watched my 10 year old son attempt to complete all his assignments in Google Classroom. My son is relatively tech-savvy and loves working digitally, but he was LOST. There were too many links, too many Classroom topics, and too many places to “go”. In fact, I had difficulty helping him locate some of the resources!

The Problem

Sometimes less is more. Creating multiple resources, Classroom topics, links, and Docs to help our students stay organized could possibly be counter-productive especially while helping our youngest learners. Could we be making it even more challenging for students to locate and access assignments by providing too many guiding Docs?

To streamline resources in Google Classroom many edtech gurus have recommended:

1. Classroom Topics named “Today’s Links”: If you name a Google Classroom topic “Today’s Links” students will know where to find all the materials for that day! Teachers may choose to move the links under the correct subject Topic headings later.

2. Numbering Assignments: Numbering assignments makes it easy to refer to an assignment later. Also, students may be able to recall which number assignment they should work on today.

But, once you add on all the amazing Google Slides daily/weekly learning plans and Google Docs checklists it may still get a little confusing.

How could we simplify our assignments and make it easier for students?

The first solution that came to me is Google Sheets!

Google Sheets allows you to create a “one-stop-shop” for objectives, tasks, checklists, links, and more!

This daily lesson template for students allows you to add all your assignments along with a checklist for each task. Template available TinyUrl.com/SheetsRemotePlans

Template available TinyUrl.com/SheetsRemotePlans

Also, you can link to the specific Google Classroom Assignment in the Sheet by clicking on the 3 dots in the right-hand corner of the assignment while in Google Classroom. Then, add that link to the template.

Click the 3 dots and select “Copy-Link”

Finally, you may add a checkbox for students to complete once they have “Turned-In” each assignment.

Duplicate the template by clicking on the TAB below and selecting “dupicate”. You may rename each TAB using dates.

Duplicate Tabs by right clicking and selecting “duplicate”.

How do you streamline your assignments?

Digital Hall Passes using QR Codes

Flipped Tech Coaching

This Fall, educators are doing everything they can to provide sanitized materials for our students. But what about those old, laminated bathroom passes or sign-out notebooks? If your students have access to a device that can scan QR codes you may want to consider using digital hall passes. Most all newer model SMARTphones, tablets and laptops are able to scan QR codes.

Why QR Code Hall Passes?

If students have access to a device with a QR code scanner, the codes allow students to quickly scan the code whenever they need to leave the classroom without touching a shared bathroom pass or notebook. This also creates a record system for the teacher by recording student activity using a spreadsheet. The information may then be sorted and shared with parents, co-teachers or administrators as needed. This blog post also explains how to create a pre-filled link so even our youngest learners…

View original post 606 more words

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.

Google Sheets Planbook

Looking for a new digital planbook?

School will be in session before we know it and many educators are scrambling to decide which tool to use this year while lesson planning. Over the past few years, I have tried creating lessons using Google Calendar, Docs and Slides templates. Each of these tools makes it super easy to create a template, edit, revise, share and archive lessons.

Last year I tried using Google Sheets and decided this is the tool that best fits my needs as:

  • Tabs may be duplicated each week
  • You may share an entire Sheet or Tab with admin or co-teachers
  • Images are now easily inserted
  • It’s easy to merge and wrap cells
  • Color coding is easy with the paint bucket

Here’s the link if you’d like to make a copy of the Template. http://bit.ly/SheetsPlanbook

Which tool do you prefer for your digital lesson planning?

Google Sheets Holiday “Shopping”

Teach an introductory lesson to Google Sheets by doing some holiday shopping!

Link to Template (Click TEMPLATE in the right corner to make a copy)

Screen Shot 2018-12-18 at 5.23.07 PM

What will students learn?

Students will learn how to enter data, enter basic formulas, use FUNCTIONS, and create a graph by following the directions in the second tab. They can even checkoff the items as they work using the checkboxes.

Screen Shot 2018-12-18 at 5.25.38 PM.png

Where do students “shop”?

Students shop using this imaginary inventory. Shop for toys, household items, electronics, or even clothes and personal items!

Screen Shot 2018-12-18 at 5.38.40 PM

How can I differentiate for different learners?

This activity is geared towards students in Grades 3-5. You may differentiate the activity by using different templates linked using the Tabs at the bottom of the Sheet. For example, if you want a template with the formulas or headings already inserted, you could use the tab labeled “Differentiation with Formulas”.

Screen Shot 2018-12-18 at 5.27.59 PM

What do students graph?

I added this easy activity for students to graph sales of popular toys. Then, use the checklist below to analyze the data and enter formulas!

Screen Shot 2018-12-18 at 5.39.53 PM.png

How may I assess learning?

Use this nifty exit ticket to assess learning! Add questions, tasks and activities as needed! You can pretty much turn Google Sheets into an activity pack! Or, add a rubric on a new tab!

(Example Exit Ticket Tab)

Screen Shot 2018-12-18 at 5.31.31 PM.png

Do your students do any “holiday shopping” using Google Sheets? I’d love to see your lesson!

 

Evaluating the Authenticity of Websites

The Objective

This month, students in grades 3-5 evaluated the authenticity of three different websites. Students often are unaware that anyone may create a website and frequently believe that anything they read online is valid. If we are going to teach students how to use search engines and conduct research, then we must also show them how to evaluate the search results.

The Lesson

Each class had roughly 15-20 minutes to explore the websites on their own. Fourth and fifth grade students had received the same lesson the year prior, only they were presented with a different website.

I introduced the following (fake) websites by stating that I would appreciate some student opinions before I started a project.

  • Third Graders: Dog Island Preface: My dog has been acting up. Should I send her on a vacation to Dog Island?
  • Fourth Graders: The Northwest Tree Octopus Preface: I found this amazing creature online and realized it was endangered. Should we start a school fundraiser?
  • Fifth Graders: All About Explorers Preface: We will be learning about different explorers this year.  Would this website be a good resource for me to recommend to for student research?

Assistive Technology: Read and Write for Google Chrome Extension

Since not all students are able to read at grade level, I reminded students to use the Read and Write for Google extension to help them read text aloud and define words. We used the highlight collector to highlight important information to support our opinions and then collected the highlights into a Google Doc. I find this extension to be extremely helpful with any and all reading activities.

The results were far from shocking.

Grade 3: Third graders decided I should NOT send my dog to Dog Island as it did not seem like a nice place. However, they did not question the website’s authenticity.

Grade 4: Fourth graders voted to save the Northwest Tree Octopus.  Only a couple students seemed confused while learning that this octopus lived in a tree. But, they still did not question the websites authenticity. Only one student raised their hand to share that they thought “something is wrong with the website”.

Grade 5: Roughly a quarter of the fifth graders picked up on the false information. I politely asked those students to hold their thoughts until after I asked the class to vote if we should use this website as a resource. The majority of the students still voted YES!

Action Taken

After I revealed the objective of this lesson, students laughed and pretended like they knew the websites were “fake” the whole time. But, in reality they absolutely did not. We then talked about how we can evaluate websites for authenticity and brainstormed many different ideas.

Students then used this Google Sheets checklist to go back and evaluate the authenticity of the website using the 5 W’s:

  • Who wrote the information?
  • Where did the author get their information?
  • What is the purpose of this website?
  • When was the site last updated?
  • Why is this site useful for your research?

Evaluating Website Google Sheet Template: Click USE TEMPLATE to modify.

Here is a template you may use with your students. I found it was very helpful!

Not only was this lesson extremely important; it was also extremely fun! Many students laughed as they exited the classroom stating, “You got us again, Mrs. Boucher! Not next year!” Hopefully, they are correct.

Lesson Slideshow

An amazing reader sent me a fabulous Google Slides lesson they created using this blog post. Check it out here! (Somehow, I cannot find the name of the teacher that created and sent me this Slideshow, so if it was you, please send me an email so I can give you credit!)

How do you teach students to evaluate website authenticity? Have any great resources you’d like to share?

Digital Hall Passes using QR Codes

This Fall, educators are doing everything they can to provide sanitized materials for our students. But what about those old, laminated bathroom passes or sign-out notebooks? If your students have access to a device that can scan QR codes you may want to consider using digital hall passes. Most all newer model SMARTphones, tablets and laptops are able to scan QR codes.

Why QR Code Hall Passes?

If students have access to a device with a QR code scanner, the codes allow students to quickly scan the code whenever they need to leave the classroom without touching a shared bathroom pass or notebook. This also creates a record system for the teacher by recording student activity using a spreadsheet.   The information may then be sorted and shared with parents, co-teachers or administrators as needed. This blog post also explains how to create a pre-filled link so even our youngest learners can scan a code with their names and destinations already selected for them.

Step 1: Create a Google Form

The first thing to do is create a Google Form. Go to Forms.new and create a question for every QR code category that you would like to create. And, list student names as the answers to each question. Save yourself time by duplication each question and editing the destinations. Do NOT make answering the question a required action.

Example FORM with hall pass categories and student names.

Step 2: View Response Sheet

Once you are finished, click on View Response Sheet above. And select “View responses in Sheets”.

Step 3: Create a New Sheet Tab for Links

Open the Sheet and create a new Sheet tab for the links to the pre-filled responses you are about to create. Creating pre-filled Form responses will allow students to scan codes that already have their name and desired action selected. Use only 2 columns in this tab. One Column will be for the student links. One Column will be for descriptions.

You will want the final Sheet to look like this:

Example Sheet with Links

Step 4: Copy the Pre-Filled Links

Go into Google Forms and create the pre-filled link for each student response. In this example below, I am creating QR Codes for “Bathroom-Out” and “Bathroom-In” for Student 1.

Note: that if you are creating a generic numbering system instead of student names you will only have to do this once and not every new school year. Paste the link in the Sheet and make sure to add the description in Column B. Here’s a link to my Sheet for reference.

Step 5: Optional- Edit the link in Google Sheets to formResponses.

Now that we have the link, we are going to paste it into the Links tab in Google Sheets. But, we are going to edit the link and change it from “viewform” to read “formResponse”. This edit will allow students to Scan the code and automatically log their response. If you do not change from viewform to formResponse, students will see their names and the Form questions. It’s really a personal preference.

Step 6: Add QR Code Generator Add-On

Once you have finished copy, pasting and editing the codes, you will now add the QR Code Generator Add-On for Google Sheets. This allows you to highlight the URL for the Code, along with the student names, and create a printout of all the QR codes for your students. It’s pretty simple to use. Just go to Add-Ons, select “Get Add-ons” and add the QR Code Generator. (Note: The new update for QR Code generator requires you to only use 2 columns)

You will get a printout like this:

Step 7: Test your QR Codes

You now have a QR Code for every student. Try printing and scanning a QR Code and see if it works by logging the information into your form response tab in Google Sheets. When you scan the code it should timestamp the activity and place the student name in the appropriate column.

Example Form

Step 8: Distribute QR Codes

I used Google Slides to copy and paste the codes. Determine how you’d like to distribute codes to students. For example, do you want to laminate them and place them on their desks?

Step 9: Explain routine to students

You did it! Student QR Codes are ready to go and all student activity will be logged into Google Sheets. Now, explain the routine to students and you are ready to go!

Do you have a better system? I’d love to know!

Student Digital Reading Log Using Google Sheets

Why Google Sheets

Collecting student reading logs can be such a tedious task.  There are many websites out there that allow teachers to monitor student reading. However, I prefer Google Sheets as it is so easy to provide students with feedback using the Commenting Tool. Also, students and teachers may easily share the reading log with parents, administrators or educators simply by using the Share feature. And, the Google Sheet is also collaborative. If students were researching together then they could share references, ideas and summaries with their partners. Finally, Google Sheets automatically saves in the Cloud which makes it easy for students to track books they read at school and at home. It will never get lost!

Google Sheets Template

The following digital book log allows students to set a goal for how many books they wish to read. In the image below, the student set a goal to read 100 books. As the student updates their reading log on a weekly basis the percentage towards that goal is automatically updated. So for example, the student in the image below read one book today and they have a goal of 100 books. Therefore, they have met 1% of their goal!

Click the image and Make a Copy to modify and edit your digital book log. 

Screen Shot 2017-10-17 at 10.00.07 PM

Ways to distribute the digital book log

  • Google Classroom-Make a Copy for every student
  • QR Code Generator– Post a QR Code
  • Gmail- Share the file with students and email it to them
  • Teacher Website- Post a link to the URL on the teacher’s website. Remember to change the URL ending from “edit” to “copy” which will force students to make their own copies

Summary

There are many ways to have students track their reading. But, Google Sheets allows teachers and students to easily monitor progress using the amazing features of Google.

How do your students track their reading?