Category: Sheets

Faster, No Math Required Feedback

I am on a quest this semester to make feedback as fast and effortless with the least amount of mathing.  I hate trying to math by myself.  Give me four numbers and unless they’re all 2, I’ll have a different answer each time I add them up.  It’s mind blowing how terrible I am.  I’ve pretty much decided that from this point on in my life, I am going to math only with the help of a spreadsheet because it’s just less frustrating for everyone.

In the past, I’ve never used Google Forms as a way to “grade” (give feedback) on student presentations because I couldn’t figure out how to have it automatically add up the points.  Then, my friend told me about Form Publisher Add-on.  And I thought, how did I not know about this??!!

Form Publisher is a Google Form Add-on that allows you to publish your form entry to a doc or an Google Sheet.  (And the Google sheet means no mathing by yourself!)  Add-ons are amazing little programs that help you do great stuff in Sheets, Forms, Docs and Slides.  In Forms, to get an add on, click on the three dots then choose “Add-ons.”  Search for Form Publisher and then click add.  To run it, go to the puzzle piece and chose it. (You’ll run it after you set up your form. )

The Why

For as long as I can remember, I have always had intermediate students do a two minute oral exposé once a month or once a chapter.  I give them the topic:  Research a store, a country, a famous scientist, a problem, present your survey results, etc – you get the idea.  When I had my combined level 3 and AP class, I used the topics to help the AP students prepare for AP.  (I tricked them and had them preparing even in level 3!  They had no idea.)  I continued this even with my college classes. Sometimes I have it really structured- sometimes I don’t.  This unit for college they doing some reading and comparing and presenting a thesis.  The first one was, “Tell me about yourself,  your family and friends and what role do they play in your life.” I like it.  Students like it.  It’s a nice way to have some presentational speaking on a specific topic that goes along with your theme.    I needed a paperless way to add up these points.

The Set Up

There are two things to set up for Form Publisher.  One is the form and the other is destination document.  For my form, I took the student email addresses from my Google Classroom and entered this into the first question.  This way I could click on the student and populate the column that Form Publisher would use to share the document.  (You won’t see this in the example.)  Next, I added the names of all of the students in the class along with my own.  I have always had students peer grade for oral exposés.  A student would get two reviews: One from me and one from the reviewing student.

The Rubric:

I really like the presentational speaking rubric for AP.  It’s easy to follow.  It’s clear enough without being too wordy.  And I’ve used this for years in the high school as well in my college class.   (Note: When I had the combined 3/AP class, I used the same rubric for the 3s.  Only “appropriate structures” has a different meaning for level 3 and AP.)  I wanted the rubric to add itself up, so I made one question with a number  for the points (5,4,3,2,1) and one with the comment for that point value.  I wanted them to be side by side, but without fancy coding in your sheet you’ll need to have two questions.  I repeated until I had all of the criteria.

The Destination Sheet:

I made a template sheet in Google Sheets.  Form Publisher had a pretty picture in their example, so I tried to mimic that idea with relatively little success.  Because the destination sheet pulls information from the form, you have to have “markers.”  These are marked by << >>.  (I don’t know what their English name is, so I refer to them as “French quotation marks.”)  These have to match EXACTLY what is on your form questions or it won’t work.

I used a basic formula to have the points add up.  Here’s an example of what it looks like for the student.  (This was a test one.)

Each time you submit the form, Form Publisher creates a new document (or sheet) and can share it with the student.  It’s brilliant!  No paper!  Instant Feedback!  I have a section on mine for the students to do a little reflection after their exposé and after they review their feedback because if you give feedback and you don’t have them do something it’s a waste of your time.

You can make a copy of my form here.

You can make a copy of my destination sheet here.

Form Publisher has its own really easy to follow tutorials here.

Data Validation Saves You Time

I’m always surprised how little teachers use Google Sheets (or Excel) because it has so many features that help make all the little tasks we need to do as teachers less time consuming. One of these is Data Validation.

Data What?

Data validation is a neat little feature that adds a drop down menu to your cell so that you have some pre-set choices.  You set the choices.  Highlight the cells in which you want the drop down menu to appear, then in the tool bar choose Data>Validation.  From there, I generally choose “List of Items” and list what I want my choices to be.  (You can also require that the cell be filled in with a number or a certain text, but I never use those.)  Click to view a video that walks you through the steps.

 

Applications for Education

Faster Feedback

When I give an assignment, I have a general idea of all of the items I may need to comment on.  For example, I know I want to give at least one positive comment, at least one “work-on” comment and then tell them what to do next.  I’ve used data validation to give feedback faster, by setting up data validation with the most common comments I think I’ll use.  Then I don’t have to type them in one by one, over and over for each student.  It makes the mechanics of giving feedback much faster.  I can still type in a unique comment if needed, but I don’t waste time typing in the same thing multiple times.  I use a Google Sheets Add-On like Autocrat or FormMule to send or share the feedback with the students with a few clicks.

Story/Sentence Creation

Students who can’t produce their own language just yet, can create stories using data validation.  Here’s an example I made with vocabulary from the first hours of French.  Click on the image to make your own copy and to see all of the choices. It’s like structured sentence creation.

Student Choice

I use data validation to set the choices for my homework choices sheet.  Students click on the arrow and it gives them a list of approved choices.  I’ve also used it for Flipgrid review.

Attendance

You probably keep attendance in your classes in your school’s student information system (SIS), but data validation can be a quick way to keep track of attendance for clubs or extra curricular activities or anything else you might need to “check-off” over a period of time.

Grading

I use a spreadsheet to grade my end of unit assessments.  I have four columns for each of the four sections.  I use data validation to put in the possible scores and then as I’m listening to students or reading what they wrote I use the drop down menu to input their score.  I set the spreadsheet up to automatically add up the points.  It’s much easier to click as I’m walking around with the iPad listening than to type.

Go ahead, open a Google Sheet and see how Data Validation can save you time!

 

Week in Review and a Bonus Flipgrid Self-Reflection!

This past week Kicked. My. Butt.  Nevertheless, it was quite a productive little week.

Monday

Because I don’t know how to stop giving myself more work, my massive effort to visit every teacher in his/her classroom to find out how I can help them integrate technology thoughtfully kicked off and I visited 20 teachers. #tired.  I began to have feelings again for my iPad.

Tuesday

I am lucky enough to be considered part of the CAMP (Computer and Media Pathway – a CTE/CPA Distinguished Academy) Team and Tuesday we spent the day team building with students as a disguised method for teaching these kids about college.  It’s called the Ropes to College and is an model example of why academies and pathways are good for kids.

I also submitted my workshops (2) and interest sessions (2) for the CLTA Conference “The Quest for Proficiency” in Ontario in March.    You’ll be there right?  Right?

Wednesday

Full-fledged, 100% fell back in love with my iPad.  I was giddy.  I was so crazy in love that I bought my iPad a stand so it can take it’s rightful place on my desk.

I also took 19 seconds of a different video and turned it into another fifteen minute activity, using Google Forms.  Why had I never done this before??  Instant data!  Click on the image to see the slide show.  (Is it still a slide show if there’s only one slide?)  And click here to see the accompanying form.   (You’ll be asked to make a copy.)  My next step is to add these little video gems into EdPuzzle.

 

And then we talked about the data which was a great way to practice numbers.  Because there’s never not a good time to throw in a little number practice.

I also played around with Flipgrid, pretty much having the same experience as Colleen at Language Sensei. Except, today when I was listening to the responses,  I had what I hope is going to be a brilliant idea: I think it will be more effective if they listen to their responses for the things that I heard over and over again.  Instead of me giving feedback, they could hear it themselves and hopefully correct it.  Jo Boaler said that your synapses fire when you realize you’ve made a mistake and when you correct it, so I’m hoping this will cause some synapses to fire twice which will in turn have some effect the next time they talk.

Today I made a Google sheet, which I’ll pass out in Classroom so they each have a copy and here’s the brilliant part, I used a formula so if a student answers “Non” to any of the things they should be listening for a message pops up telling them to re-record!  At least I got the formula to work on the first try making me feel like my formula skills are improving.  Click on the image to see the form.  You’ll have to make a copy to see the down arrow (and my fancy formula skills.)


Thursday

Confirmation that the House voted to restore funding for 21st Century Community Learning Centers.  This is a good thing.

At my Technology Leaders meeting the meeting leader said he wants to do a book study of The Innovator’s Mindset by George Couros and he was so excited about the idea that I’m going to participate just because he was so enthusiastic about it.

Friday

Continued classroom visits with my love, iPad and catch up day for everything I haven’t been able to get to including starting the catalogue of the performance based assessments the World Language teachers will be giving in a couple of weeks.

Despite being beat up by the week, I can’t complain because I have the best job(s) in the world!

#edublogsclub – Assessment

Student Reflection with Google Sheets

Ok- so I actually started this post in February.  February!

What had happened was:

My principal came to me last semester with a copy of John Hattie’s Visible Learning into Action and said that it was a book he wanted all of the instructional coaches to have.  I thought it was adorable that he thought that I had time to read the book, but I did try and carried it around in my backpack for months.

What I did get to was that self-reported grades have a huge impact on student achievement and so I set about trying to implement some kind of goal based reflection/portfolio/tracking system.  I wanted to have students submit samples, so we could see growth over the course of the semester and on which they would reflect and grade themselves and I could get an idea of where they were.

What I did:

I decided to use Google Sheets and Google Classroom because I thought it would be easy to track and while I know there are many sites out there for portfolios I did not want one. more. login or platform.

I created a Google Sheet called Weekly Reflection and shared it in Google Classroom as the only assignment with the “Weekly Reflection” topic label.  Students could find it easily in the Stream by clicking on that. Obviously from the title, I had planned that students would do this at the end of each week, but I quickly realized that once a week was too frequent to show any growth, so it ended up being every 2-3 lessons.  In the spreadsheet the students chose either Recap or Writing (I told them what they were doing) and then they evaluated themselves based on the rubric on sheet 2.  Lastly, they set a goal and made an action step and turned the sheet in through Google Classroom.  They did this several times during the semester.

Here’s a short video that shows how it works.Weekly reflection

Once they had turned it in I went and read or listened and then evaluated them based on the rubric as well.  Sometimes these were way off.  The very first time I had several students that gave themselves a 7 or 8 and they were writing “je suis mange.”  I don’t know if they didn’t read the rubric or if they felt like they “had” to evaluate themselves high.  Either way, after the first time their evaluations were closer.

The Google Sheet also has tabs for the language goals to see where they are and what they might do to advance.  Someone shared this somewhere and I don’t know who it was, so if it was you, thank you!

Some things I did:

I gave them these assignments with only 10-15 minutes left in class because I wanted to see what they could actually do and not what they could look up or plan.  It was pure sneakiness on my part.

Students had a paper rubric to refer to so they didn’t have to go back and forth between tabs because that gets annoying.

In the column labeled “notes” at the end of the sheet and I responded to their goal setting and writing or listening sample.

This was not given a grade.  It was purely for reflection purposes.  If students didn’t do it, nothing happened except for they didn’t get the benefit of my feedback and comments.

Goal setting language was not part of the Student Learning Outcomes I was given to use, so I had them do the reflection and goal setting in English.

What went well:

Using conditional formatting to color code the numbers was brilliant because their progression was represented visually with color.

Students asked me questions about their writing.

Students wrote some great goals and ones that I would never have guessed for them.  They saw weaknesses where I didn’t.

What didn’t go well:

Sometimes students only wrote 2-3 sentences and it was difficult for me to evaluate.  In these cases I wrote that they didn’t write enough for me to evaluate.  That didn’t happen when I gave a Recap to do.

Students didn’t always write an action to do to achieve their goal.  This is my fault because I didn’t give examples and non-examples.  Easily fixed next semester.

I didn’t have a system for them to reflect on their previous goal setting.  #nexttime

I didn’t give enough time.  Honestly, I wish I could have them do this at home, but I was too afraid that the Translators and the Internet would cause too much interference and not give me a true sample.

A note on the rubric:

I used the rubric our district is using from our trainings on performance based assessments with Kara and Megan from Creative Language Class. Why I didn’t have students evaluate themselves as NL, NM, etc. instead of numbers?  I can’t really say.  I’m sure at the time I did it, it made complete sense.  I’m going to leave it.

The semester isn’t quite over yet, so I will ask students their thoughts about the efficacy of the rubric/reflection in their end of the semester survey.

Click here to see the Weekly Reflection Google Sheets.   You’ll have to make a copy in order to see the fancy down arrows that will color code once you choose a number.  Note: Number 1 has no color.

 

 

Homework Choices

I was out wandering the Internet a few weeks ago, when I came upon musicuentos.com’s Homework Choices and I said, “OMG, I need that.”

I was looking for something in my college class to get them to have some other cultural experiences.  I’ve tried different tactics in the past and most were not as successful as I would have wanted.   (Read: Good intention, bad idea.) I was all over this homework choices.  I particularly loved that some of the choices said listen or watch more than once.

First, I made my own French version which you can here here.  I added a category for students who had had French before because they needed their own category.  Then because I was no kinds of interested in actually counting up points, I created a Google Sheet that automatically adds up all their points.  I used data validation to create a drop-down menu of the choices in that category.  I shared the spreadsheet with students as “make a copy” so they could edit it.  My goal was that the total number of points would be visible in the thumbnail in Google Classroom, but alas, this was not to be.    (Maybe if I take out some of the rows at the bottom? I’ll try again next unit.)  The students are required to do ten points for one unit or roughly every three weeks.  I didn’t want them to have to do something every day, but a couple of times of week.  This is more about pleasure and exploring the language.

Homework Choices

Spreadsheet Template (You’ll be asked to make a copy)

Add up points automatically

Reset student responses in quizzes in Google Forms

If you are using the new Google Forms for quizzes and have it set to allow only response, but then find yourself needing to let a student take the quiz again you can work some form magic to do it.  It will delete the student’s original response, but if you’re ok with that follow these directions.

From Forms itself:

Click on responses.

2016-08-13_13-35-33

 

 

 

 

Choose the student’s name under who has responded.

2016-08-13_13-51-43

 

 

 

 

 

Then hit the trash button.  This will delete the student’s response from the form and the form will allow them to do it again.

2016-08-13_13-52-32

 

 

 

You can also do this in the Google Sheet associated with your your Form.

In Forms click on responses.

Click on the green cross icon to open up the sheet.

If it asks, tell it you want to create a new sheet.  (If you’ve already done this it won’t ask.)

In the sheet, right click on the number next to the row of the student who you want to be able to re-take the test.  Choose “delete row”.  Now the student will be able to re-take it because the sheet no longer sees their name.

2016-08-13_13-41-07

 

 

 

 

 

 

 

Now the student will be able to re-submit the form!