Google Forms – Choice Removal and Choice Eliminator 2 Add-ons

I saw these both featured on freetech4teachers.com and thought I would try them out. Richard Byrne has a quick description and a handy video on how they work which is nice. You can view that video with this link.

These are both add-ons for Google Forms. They are not stand alone form makers, you have to add and then enable the add-on to make these work and I’ll walk you through and let you know how well they work (or don’t). I figured since the Form Ranger (part 1 & part 2) is a bit involved, then I would check to see if these simpler add-ons work just as well.

Choice Removal

This add-on will remove an option once it has been selected. There is no way to configure it to remove after a certain number has been selected. It is always set to one.

The first thing you need to do is create a Google Form. There are a lot of ways to do this which I won’t get into here and the process is usually pretty easy. Here is what mine looks like:

This is super bare bones and obviously, this will only work with Multiple Choice questions (it might work with a drop down, or checkboxes but I didn’t take the time to explore that).

Now I will get the add-on. To do this click on the three dots that are next to your icon, then select Add-ons from the menu.

A new window will appear – search for Choice Eliminator. Then click on it and go through the install process. It will ask you for permission to use this add-on and then you can go back to your form.

I may have to check out the Choice Limit add-on

Now when you are back on your form, click on the puzzle piece near the top right hand side of the screen and select Choice Removal from the menu.

A tiny little box will appear, click on Configuration to get going.

When you do this a configuration pane will appear on the right side of the screen. Now, before it starts to work you need to click the little arrow next to the question and then turn on the Remove Choices feature.

Now it is nearly ready to go. The last option you may have is to have placeholder text when all of the options have been taken. To get to this click on the three-line menu and select Settings.

Type in whatever text you would like and then be sure to hit the Save button.

It works pretty well.

When testing, I was able to have more than one person select the same time. It happened when two people submitted the form at nearly the same time. It is rare, but I was able to do it. If I waited 15 seconds or longer and then tried to pick the same time, I got this error message:

OK – this worked pretty well. Does it work as well as Form Ranger? Well, if you want to limit only one choice and you’re not expecting a huge onslaught of submissions right away, then yes. Yes, this works fine.

If you need to reset the form, you can do it in the add-on settings. Click RESTORE ALL OPTIONS.

Choice Eliminator 2

This was the other option Richard selected. This one is a little different in that you can set limits. Much like Form Ranger, you can set limits with this add-on. Form Ranger is a bit involved and takes a little bit of time to set up, so can this work just as well? No – the short answer is no, but that may not be a deal breaker for some. Let’s take a look.

To get started, make your Google Form and then install the add-on (the exact same way as above).

Once it is installed, you will activate the Add-on by clicking the puzzle piece and selecting it (just like before).

Again, a little window will appear and you will need to click on Configure to get started.

Again, a configuration panel will appear on the right hand side, but there will be a “Note form the author” (that is a direct quote, please see the screenshot below.

Uhhh-not very reassuring here

OK – so I need to change my multiple choice question to a drop down – got it. I don’t have to retype it, I just change the question type and this is what it looks like now.

Now that is ready, let’s get into the settings from the configuration panel on the right hand side. It will probably be populated with From Responses 1 – leave that alone, then click the arrow next to the question that has the choices you would like to limit and click the box Limit Choices. It will think for a little bit.

Once it is done “Creating” go ahead and click the gear icon. When you do that you will see all the choices for that question and you can add limits to each one.

You will notice a green circular button in the bottom right hand corner. There are some nice options here. You can close the window, restore the choices (if you want to reuse the form), pull questions from the form (maybe you’ve updated it and need to add more limits or add a new question.

OK – lets start testing. Like before, I opened up the form in multiple browsers and tried to break the form. What I wanted to see is will it strictly enforce the limits like Form Ranger. The conclusion is that it is not too bad.

If someone picks the last available choice for a time and another person has the form open at the same time, they can obviously see the time. If there has been some time (5-10 seconds) between the first person hitting the submit button and the other person, they will receive this error.

Not too bad. I was able to consistently get it to break, but I had to be very fast. I think the drop down menu causes a slight delay in a person making the choice giving the form some time to update to see what is available or not.

Conclusion

I didn’t have a lot of hope for these two, but they’re not too bad. Again, I wouldn’t use this for parent/teacher conferences or setting up a sign up sheet for After School Activities where limits really mean something, but if you’re looking for an easy way to set up some office hours and want students to set up their appointments ahead of time – this could work.

Episode 191 – Un-Boxing Day

Tony and Patrick are back and having more fun than ever. As always, you can find our podcast on your favorite podcasting app or on Podomatic. Check out the talking points below and if you have any humorous clips you want on the show – reach out to us in the comments!

  1. Tony and Patrick catch up
  2. New sounds for top of the- show
  3. Google Forms + Form Ranger detailed post
  4. Apple’s new event
    1. What would you like to see?
  5. Is Tony done with Chromebooks?
    $33.99 – Really?
    Video editing
    STEM/Maker
  6. Windows 10 – Could be the best option but it is still the most annoying operating system on the market
  7. Dystopian School Follow Up- We will only use Linux, but you can choose your flavor

Download the episode HERE

Episode 190 – Don’t Use Blockchain

We are back again! This time we don’t just talk about ed tech but also give some beverage recommendations as well as some TV viewing choices. Check out the talking points below and as always,, find us on your favorite podcasting app!

  1. Elysian Night Owl & Liquid Death Sparkling Water – Approved by IT Babble 
  2. TV Review – Squid Game on Netflix 
    1. Trailer – https://www.youtube.com/watch?v=oqxAJKy0ii4 
    2. Thoughts? 
  3. Google Forms for event sign up 
    1. Form Ranger – https://cloudlab.newvisions.org/form-ranger 
    2. Plan the form 
    3. Don’t let people type their choices 
    4. Google Data Studio – https://datastudio.google.com/ 
  4. Don’t use blockchain 
    1. Blockchain – https://en.wikipedia.org/wiki/Blockchain 
    2. HyperLedger – https://www.hyperledger.org/ 
  5. Your cloud storage is ruining your life 
    1. Current folders 
    2. Archive folders

Download it here!

Google Form + Google Calendar = Zapier!

UPDATE: I’VE INCLUDED THE LINK TO THE CALENDAR, THE GOOGLE FORM AND THE ZAP AT THE BOTTOM OF THE POST

Here is the problem. Our teachers want to have a shared assessment calendar. That way they can see what tests are scheduled for each class and know how to not overload a kid. What student wants to have four tests on a single day? Well how to do this?

So we are a G-Suite school and we have the ability to create a Resource calendar. This is just a calendar that no one owns and everyone has the ability to create events. The trick here is that when teachers make an event in Google Calendar they will need to select the calendar when they make an event.

Again, this seems easy enough, but it’s also pretty easy to forget to do that. It’s very easy just to type in the test details and hit Save and not realize it’s not on the correct calendar. You can see where this goes.

Enter Google Forms and Zapier. Here the teacher does not need to worry about that information. The form will capture all the pertinent information and then using Zapier – it will create the event and schedule it on the correct calendar. Oh yeah – it is free too.

This isn’t hard but there are a lot of steps. Don’t worry though – I’ll link the calendar and the form out and the workflow from Zapier at the bottom of this post.

What is Zapier?

Most people know what Google Calendar is (if not it is pretty obvious) and Google Forms, but not everyone knows about Zapier. This is a service that allows you to take two different services and make them work together by creating triggers (or Zaps as they call them). So basically when someone fills out the form, Zapier will take that information and put it into a Google Calendar event and then schudule it automatically. Pretty great huh?

Step 1 – Create the calendar

This would be best if you go to your Tech department and have them create a Resource Calendar. This makes it easy for anyone to view and control. If your Tech department is not open to such requests, go ahead and make a calendar of your own.

Open Google Calendar (calendar.google.com) and sign in if need be. On the left hand side you will see Other calendars click the + symbol.

Now some new options will appear. Select Create new calendar.

Now you can name your Calendar and set the appropriate time zone (kind of important that last part).

Now we need to set the sharing settings up. So let’s go back to your calendar list by clicking the left arrow near the Settings 

From the list find your new calendar. Move your mouse over the calendar name until you see three dots. Click those dots.

Now click on Settings and sharing

Make sure that Make available for [your school name] is checked and then save those settings.

Step 2 – Setting up the Google Form

This is pretty easy. Create a new Google Form either through Google Drive or thby going to forms.new and you can make your form look like mine below.

Now that your form is created, we need to create a spreadsheet for the responses. This is very easy. Click on Responses at the top of your form and then it will take you to another spot. From here click on the green Google Sheets icon.

It will ask you to name the spreadsheet and that is all you have to do with that!

Google Forms is pretty intuitive, but if you have any issues just let me know and I’ll give you hand.

Step 3 – Set up the Zap: Part 1 – The Google Form

With Zapier, you can sign in with Google and while there are paid versions of the service there is a limited free version that will be more than enough for my needs. So head over and sign up/sign in to Zapier!

Once you hav access to your dashboard, from the left hand side you will see an option to MAKE A ZAP

Now Zapier will start walking you through the process one step at a time. It is pretty fantastic what it can do. So obviously, when someone submits a Google Form so just type in Form to find the Google Form choice. Then select it.

Now it gives you two options. It can be whenever there is a new response from the Google Form or when there is a new response or if it has been updated. I like to go with the later. So it should look like this now.

This part may seem a little weird, but they want you to grant Zapier access to your Google Drive (which is where the form and the spreadsheet live). Go ahead and give it permission and then click Continue.

Now you need to tell Zapier which spreadsheet to look for and which worksheet of that spreadsheet to look at. Since this is from a Google Form, pick that spreadsheet where the responses will be stored and there should only be one worksheet, so it should look similar to mine.

Now it will ask you to test the trigger. If you have no responses recorded this will fail. If you want to see a result. Go to the Google Form and fill it out then run the trigger.

When it finds a response it will show you something that looks a little weird like below.

Step 3 – Set up the Zap: Part 2 – Google Calendar

Now that the Google Form is all set up, let’s move onto the Google Form part. So this is the Do this… part of the Zap. It will want you to pick what app you will use when it sees the Google Form has been completed. From here select Google Calendar.

It will ask you what you want to do. Select Create Detailed Event. This will allow you to provide much more info on the calendar event. Then click the big blue


Then it will ask you to confirm your account. I guess you could post this on another account if you wanted to. Now we get some options and boy-oh-boy are there options. Don’t worry we will just be dealing with the basics.

The good news is that it will pull data directly from the Google Form to populate the particular field. You just need to click the drop down menu and select it. Check out what I did below to see an example.

I basically put the grade level, subject and what the test was about all in one line. Be sure to put a space between those values so it looks nice.

Then scroll down to find the Start Date & Time and the End Date & Time. This will be the same field from the Google Form.

Now scroll down a little further and for All Day be sure to select Yes. This will put all the assessments at the top of the calendar and in my opinion will make it look a little cleaner and easier to see.

Now scroll all the way to the bottom and select Continue.

Guess what – it’s finally made and ready to test. Once it tests, it will ask you to turn on the Zap – go ahead and click that button!

Step 4 – Test it for real!

So I go to my Google Form and fill it out

Now all I have to do is sit back and wait.

With the free plan it can take up to 15 minutes for Zap to take action and for the calendar event to be created. For the paid account it is much faster, but you still need to wait a few minutes for it to actually work, but work it will!

So here is the calendar for October 1

Only as good as…

This is  a nifty tool that can bring a lot of transparency to a school, but of course there is a weakness. That weakness is noncompliance. If teachers forget on a regular basis or just ignores it all together this tool is not very effective.

That is where leadership comes into play. There has to be a driving push and force behind using this to make sure that what teachers, students (maybe even parents) see is accurate so they can plan. It does not necessarily need to be principals it could be department heads or just some people who are looking to improve things.

I’m not going to lie. Teachers have a lot on their plates and remembering this small simple task is not always on the top of their to-do lists.

The Links

Here are all the links needed for you to test this out for yourself. Just fill out the form, wait for about 15 minutes and you should see the details appear on the Google Calendar.

I did notice that the Zap was saying there was an error, but it never failed putting the event on the calendar. I think it has to do with the event starting and ending on the same day. If it works, it works I guess 🙂

Google Calendar: https://calendar.google.com/calendar/embed?src=c_f56lrph45nl9lgp9vmabs0kac4%40group.calendar.google.com&ctz=America%2FNew_York

Google Form: https://docs.google.com/forms/d/e/1FAIpQLScB2gyWv61il1qK546Zno_ixGieXhOFV0TdYBKKPGhboofySg/viewform

Zap: https://zapier.com/shared/49316bed1b334217ad5de23f6cab26c0876bdb52

 

 

 

 

Google Forms and Sheets – Olympic Scoring – Reflection

Alright! The day of the big event has happened and here is how it shook out. Overall the day was great due to the awesome planning from the PE department. So the day went well but how did the Google Form and Google Sheet hold up? In short it did fine but there are still some issues so let’s get into it.

The set up

We do not have WiFi outside at my school. Originally I envisioned that teachers who were manning the station would open up the form on their phone and enter the results. The organizers weren’t thrilled with this idea. First, we are asking teachers to use their personal phone and their own data which is admirable but maybe not the fairest thing to do. Then there is the possibility that their phone could be damaged and well … that is not a fun scenario. Then there is the possibility that the teacher will enter the information incorrectly causing problems.

So we have the organizers putting inputting all the results. The people running the event scored everything on a score sheet and then turned it in at the end. The problem is that the form was meant for individuals to input the results for a single team per event. For one person inputting all these at once is a problem. It just takes too long. It got done but it was a bit of a rush.

In the future I will make the Google Form so the organizer can input all results for an event at one time. The spreadsheet part will not change too much. The math will still work the same way.

Damn ties

I mentioned before that ties are a problem with the spreadsheet. The vlookup function does handle ties well. Check out the image below to see what I mean.

Now the problem here isn’t that Denmark got all of those points because they did not. Check out the image below for proof.

The problem is the other teams get the shaft a little. Even though the four other teams did just as well as Denmark they receive progressively less points.

I need to figure this out and if you have any suggestions please leave them in the comments below.

Overall

This is a fun event and really the winner(s) is not the most important thing. We have two weeks until school is out and everyone is stressed and this is a great way to help get some of that energy out and forget about your worries for a day.

The Google Form and the Google Sheet did hold up very well. It seemed to have no issues recording and then handling the data. Overall, it worked and worked fairly well. I just need to tweak it and try to make it better.

The good news is I have 12 months to work on it 🙂

Episode 153 – Go for Gold

Tony and Patrick are back for another great post. We all wish Tony a quick recovery with his knee surgery. Check out the talking points below. As always please subscribe to us on iTunes or your favorite podcasting app.

  1. Tony’s knee surgery
    1. http://www.davincisurgery.com/da-vinci-surgery/da-vinci-surgical-system/
  2. Apple’s MacBook Pro recall
    1. Battery Recall
      1. https://www.apple.com/support/13inch-macbookpro-battery-replacement/
    2. Keyboard
      1. https://apple.slashdot.org/story/17/10/18/1530202/maybe-its-a-piece-of-dust
  3. Google Forms & Sheets – Olympic Scoring series – by Patrick Cauley
    1. https://itbabble.com/olympic-scoring-with-google-forms-sheets/
    2. Schools and spreadsheets – is this more important than coding?
      1. A good starting point for programming
      2. Spreadsheets have limits
      3. Office 365 – Sharepoint – https://products.office.com/en-us/sharepoint/sharepoint-online-collaboration-software
  4. How to Increase Pay – No More Summers Off! By Ronnie Burt
    1. https://www.theedublogger.com/2018/04/30/how-to-increase-teacher-pay-no-more-summers-off/
    2. Comments are good in the post
    3. Pay vs Working Conditions
    4. Is pay really the talking point or a distraction

Download this episode here!

Google Forms & Sheets – Olympic Scoring – Part 5

It has been a long road and we are finally at the finish line. This post will talk about how to total everything so you can see who wins without calculating anything! We will also talk about limitations to this form and how it is not perfect. If you are new to this series please check the first 4 parts.

Now open up your worksheet and let’s get into the fun!

Make a new worksheet

We are going to make a new worksheet. To do this click the )+ button in the bottom left hand corner of your Google Sheet.

It will make a new tab and a blank worksheet. If the tab is not where you would like it to be simply click and drag it to the desired location. I am naming this worksheet totals

The first thing I will do is set up the worksheet. So there will be no functions (yet) just typing some text into some cells.

Now I left Column D empty for aesthetic reasons – no other reasons. The other columns are set up for a specific reasons which we will soon see.

Referencing the points

In column E we will be referencing the total points. You could just type them in, but I like referencing them back to their original cell. Here is how we do that.

  • Type in the = sign
  • Click to the Team 1 worksheet
  • Click on the link with their total points
  • Then hit the Enter key

Check out the video below.

Named range

Now that we have all the teams and their total points we need to name a range. I will highlight from E2 to F5. Then I will select Data from the menu bar and click on Named ranges…

I will call this data totalpts. Remember when naming data you cannot use spaces.

Large function

Now that we have that done it is time to start using some functions to rank the teams. In cell B2 we will be using the large function. This does the opposite of the small function we used in Part 2. So basically this will show us the largest value in a set of data. Here is what we type in cell B2:

=large(totalpts,1)

  • The totalpts is the set of data we just named
  • The 1 means to show the largest of the set of data

Now we will repeat this function for cell B3 and type this:

=large(totalpts,2)

And so on all the way dow to cell B5. When you’re done here is what you should have.

vlookup

We are nearly done. Now we will utilize the vlookup function again and we will be using this in column C. What this will do is to look at the number in column B and then match that number up with the team name.

In cell C2 here is what we will type:
=vlookup(B2,totalpts,2,0)

  • B2 is the value vlookup is looking for
  • totalpts is the set of data where vlookup is looking
  • 2 refers to the second column of the data, in this case column F
  • 0 Means we want an exact match.

In cell C3 here is what I need to type:

=vlookup(B3,totalpts,2,0)

Here is what mine looks like.

Uh? Patrick? What happened to second and third place?

OK, here is the time we will talk about imperfections. Obviously ties are not handled very well with the vlookup function. I have no idea how to solve this simply (or even complexly). If you are well versed with spreadsheets please leave solutions below! 🙂

Ties are a problem with this set up that is obvious.

Another issue here is testing the form. You could build this entire spreadsheet, all the worksheets without any data. It is possible, but I prefer to add a bunch of bogus data, build the form so I can see that it is all working properly and then wipe the data out. Not ideal but you want to make sure your hardwork has been done correctly and that once real data gets in there you know it will be handled properly.

Well that is the scoreboard in all its glory. If you have ideas on where it can be improved or what can be added let me know by adding in the comments below!

Google Forms & Sheets – Olympic Scoring – Part 4

Please check out Part 1, Part 2 and Part 3 to get caught up. If you are just jumping in now and have little spreadsheet experience you may feel a little lost.

Last post we made all the worksheets for all the eventa. In this post we are going to make worksheets for each country or team. Lucky for us, there is a lot less “moving parts” here. These sheets will simply compile all the results for all the events for each team and then total those points up.

Named Ranges

Before we start making worksheets for each team or country we need to name some ranges and we will do that back in the event worksheets. So I will open up my spreadsheet and go to the Event 1 worksheet.

I am not going to add or change anything. I am simply going to highlight some data and then give it a name. So on the Event 1 worksheet I will highlight G2 to I5.

Now I will click on Data from the top menu and select Named ranges….

Now I will call these range event1fnl which stands for Event 1 final but you can call it whatever you want just remember to have no spaces.

Now I will do it again for the Event 2, Event 3 and Event 4 worksheets.

Make a new worksheet

Just like in Part 3, we will make one worksheet and then duplicate it for the other teams making the amount of work we have to do a lot less.

To make a new worksheet click on the + icon in the bottom left hand corner.

This will create a new sheet and to rename it just double click the tab.

Now we are going to simply type in a bunch of information. No functions yet. So here is how I set up my sheet.

Again, I just typed this information directly into each cell. Now we are ready for some functions. We are only going to use two today: vlookup and sum.

In cell B3 of the Team 1 worksheet we will type this function:

  • =vlookup(A1,event1fnl,3,0)
  • Here is what is happening in this function.
  • vlookup(A1 is looking into cell A1 (which is the team name)
  • event1fnl is where it will look for the team name
  • 3 means it will look in the third column of the event1fnl data which is how many points were awarded and display that in the cell.
  • 0 means that we want an exact match.

So now in cell B4 we will write this formula:
=vlookup(A1,event2fnl,3,0)

We use event2fnl to show that we want data from Event 2

Then we go on down the list and this is what it should look like.

Believe it or not we are almost done with this post!

sum

No we want to add all those points up. We only have 4 teams and 4 events but imagine you have 7 teams and 14 events! Calculating the totals can take a lot of time. So we will let Google Sheets do the heavy lifting.

In cell E1 I am going to write this formula – it’s very simple.
=sum(B3+B4+B5+B6)

Now there is another, shorter way to type this formula which is:
=sum(B3:B6)

It does the exact same thing. Some people like the longer way that way they can see exactly what it looks like, but it is totally up to you.

Guess what my friendly reader, we are done with this worksheet!

Duplicate

Now we just need to duplicate for each team and then change data in one cell per worksheet. To do this go down to the Team 1 tab at the bottom of the worksheet. Right click the Team 1 tab and then select Duplicate.

Now rename that worksheet to Team 2.

Now you have to make one change, just one and it is easy. In cell A1 on the Team 2 worksheet is the name of the team. Change it from Team 1 to Team 2. That’s it! That’s all that needs to happen and the rest of the worksheet should update.

Now go ahead and duplicate that sheet for teams 3 and 4 and make that one change and you’re done!

In Part 5 we will work with the Totals Worksheet where it will collect the total points for each team and rank them.