In the previous two post, I’ve talked about an assessment I gave to the students and they had to “Make a Copy” to share with me as they turned it in. This usually leads to headaches as students can’t find the “Make a Copy” button or someone (if you’ve given editing rights) modifies the original copy. Well you can only give people the ability to copy the document with a little trick – this will save a ton of time.
May 29th – Forced Copy
On any Google Doc (Slides, Draw, Sheets, etc…) you have a bunch of different “Sharing” options. You can share with individuals, share with anyone in the organization, share with anyone with the link, make it completely public and share with anyone with the link (outside of the organization). Typically we share by individual names/email addresses or with anyone in the organization. But a little trick of changing the URL – just barely – will preserve the original and others can make a copy.
1.) Create you document (I’d suggest putting directions on the original document)
2.) Share > Anyone with the Link (might have to select the dropdown, needs to be anyone outside of your organization)
3.) Copy the URL Google Drive gives you
4.) Change the last word “edit” to “copy”
5.) Reload the page and you’ll get the “Make a Copy” screen
Like handing out assignments, students will just need to share with you and you have the original intact!
With yesterdays post on formMule utilizing a Google Form, I don’t actually know how many people are that familiar with the brilliance that is the Google Form App from Google Drive.
April 20th – Google Forms
Google Forms is another free GAFE (Google Apps for Education) that records data into a spreadsheet. You can design the look and have simple questions which the user sees, and then you receive the data in a sortable, searchable Google Sheet.
You can access Google Forms by visiting your Google Drive, then the red “New” button on left hand side, then scroll down to the More > and Google Forms. It’s not considered a “core service” like Docs, Slides or Sheets, but it’s just as useful.
Google Forms has gone through a redesign in the last few months and is getting more user-friendly by the day. In the GIF above I titled the Form, gave a description, titled a question, choose different types of responses and duplicated my question. If you want to sort, make sure that you give selected questions as responses – otherwise the computer will read “computer” and “Computer” differently based on the capitalization.
In the settings for the Google Form you can (as shown in the GIF above): change the color, view a preview of the survey (which is the URL I always share with people), and manage who can access the document. If this is outside the organization, i.e. parents, make sure you don’t have the “only people in my organization can access button”.
Finally Google Forms as Add-Ons which you can access from the three dot menu button on the far right hand side. Add-On’s are scripts that allow you to change and control the form closer. My favorite is formLimiter which controls the amount of people that can submit a response on a form, which is great when people are signing up for activities after school.
After our week of Google Sheets is finished (Day 1 – Day 2 – Day 3 – Day 4 – Day 5 – Day 6 – Day 7) we’ll focus on a great addition to Google Sheets that is very similar to autoCrat, but with different features that might be what you were looking for.
April 19th – formMule
formMule is another great add-on from New Visions which automates simple task that would take you forever to do individually.
formMule is a Script that you add-on which helps automate communication through/from a Google Form. Google Sheets as a program has gone through a couple re-designs since it’s inception, so the video on the actual webpage might be outdated but this Basic Setup Guide is up to date and this video is as well.
Although the video above doesn’t give any classroom uses, think about the power of instant feedback to students on a do-now to start the class and email them their answers back so they can correct them to participate in class discussions. This add-on is worth installing and playing around with to see how it can work in your classroom!
The final day of the “A Week With…” series of Google Sheets walks you through how to make a grade book that you can share with students. If you’re school doesn’t have an online grade book that students can see updated grades at any time. (My philosophy: If I can check my bank account at any time, students should have access to their grades – it might have somethings pending but it typically updated within a few days.)
April 18th – “A Week With…” Google Sheets creating a Gradebook
Creating a grade book isn’t an overwhelming task – just putting together the skills we’ve learned here and using two new formulas to link sheets and spreadsheets together to give access. In “pseudocode” (or plain english), we are going to create one spreadsheet that you can input all the grades onto and update as more grades come in. Then you’ll create an individual spreadsheet for each student, share it with them (and parents if requested) and link it to the original spreadsheet and if you give yourself enough room, you only have to worry about the original because the shared will update.
In the GIF above I take you through a sample grade book I’ve setup. Rows 1 – 7 give all the information including the number of assignment (row 1), description of what it is (row 2), marking period (row 3), category because they are weighted (row 4), date (row 5), possible points (row 6) and a “=HYPERLINK(” formula to the presentation so the student can have a reference point for that grade (row 7).
Rows 8 – 18 are students, and then the columns are the assignments. I have a few sheets on the bottom:
Grading Sheets are links to the individual students
Extras are assignments that I’m not using
Vertical is a spreadsheet that has a “=TRANSPOSE(” formula (in cell A1) to flip the grade book from horizontal to vertical, which was feedback and easier to read for students
In the GIF above, I preview the Vertical sheet which uses the “=TRANSPOSE(” formula (in cell A1) which takes the vertical columns and makes them rows. Then I preview the “Grading Sheets” sheet with uses the “=HYPERLINK(” formula to link to another spreadsheet which I’ve shared with students. Every student has the same “=HYPERLINK(” command in cell A1 because it’s just the general information from the first 7 columns, which were the first 7 rows above. The students score is the “=HYPERLINK(” and importing only their column (the example has column H with rows 1 through 92 to give myself plenty of room). If a student tries to change anything, the scores disappear so they can’t fool the system.
In all honesty it took me a little while to get this right while getting feedback from students. In the “Student 1” spreadsheet the student can see all the information as they scroll down the page, which makes sense when you are the only one there, but in a multiple student view it makes sense for the students to be rows that I can easily fill in scores. This also leads into a great conversation about user experience and students can write about this, cross curricular mic drop.
One very powerful thing about Google Sheets and spreadsheets is the ability to have placeholders which you can then put in the values and the sheet (if setup correctly) would automatically add the values (using the “=SUM(” formula or something similar). Sometimes you need to have one page that is a culmination of other formulas and sheets. In Google Sheets (and Numbers and Excel) you can reference these different sheets to have one clean sheet that just displays information.
April 17th – “A Week With…” Google Sheets and Linking Sheets Together
Being a self proclaimed spreadsheet geek, I end up making one at least one a day. It’s how my mind organizes information and it doesn’t stop during the school year. For the past few years I’ve been sharing a spreadsheet with my wife about our summer travel plans. The problem is sharing a huge sheet with all of the different options can get overwhelming – so I’ve created multiple sheets on the bottom which are each of our days and then one overview sheet that give a quick look (it’s easier and she can see the details on the individual pages).
In the GIF above I’ve created a mock up of travel plans with the days as sheets on the bottom of the spreadsheet with one as “Overview”. Each day can flex and change, but as long as I link it back to the original “Overview” sheet it’s easy to keep track of. The “Overview” page just has the grand total and the total for each days spending, and on the individual day, the total can change based on what is happening that day (in our case if tickets are available). To link I simply select the cell I want (B3 in example) and start “=SUM(” then click on the sheet below and then the cell and press enter. You can see it fill in as “=SUM(‘Aug 12th’ !B6)” which means the sheet and then (!)cell number. This way I can control the individual details on each day and the “Overview” is very simple.
***Disclaimer: I know there are a ton of helpful spending tracking apps out there, but honing my skills makes it easier to create the grade book which is tomorrow’s post.
Yesterday I spoke about Formulas in general and how powerful they can be. In addition to the 5 or 6 I suggested, their are dozens more that you can explore. I mentioned “=If(” as a formula and today I’ll explain a little further and how you can use it in your classroom.
April 16th – “A Week With…” Google Sheets and If/Then Formulas
This one is close to my heart – this formula is exactly what I teach in the first few lessons of any Scratch course and have been working it into conversations constantly. If/Then even has two (and probably more) websites devoted to it (IFTTT and Zapier) so it makes sense that you could use it in Excel, Numbers (the Apple product) and Google Sheets.
The formula breaks down as =if(<<condition>>, <<points if condition is true>>, <<points if condition is false>>).
In the GIF above I have:
Created a new column to the right of “Question 4” to evaluate points if the question was answered correctly
Continuing in our “A Week With…” series on Google Sheets, in the last post we discussed conditional formatting, but I also mentioned a “=AVERAGE” formula – today I’ll discuss those in more detail.
April 15th – “A Week With…” Google Sheets
Google Sheets has turned a lot of things upside down. The addition of Google Forms allows people to collect data so easily, that you can see what’s going on more really understanding the basics of spreadsheets in Google Sheets, before this the very first lesson I would teach would be formulas because you always had to input data ourselves – this is progress!
Formulas are what make spreadsheets work. simply by selecting a cell and typing “=” you’ll get a dropdown menu of options to choose from and then a brief guide on how to use it. The main go-to formula I use is “=SUM(” The open parentheses means that anything that follows is going to be added together. In the GIF above, I’ve started with a “=SUM(” and then clicked and dragged over Student #1 to see what they scored on the rubric. Then clicked on the bottom right corner of the cell and drug the blue box over the rest of the cells to copy that formula – instant grades for everyone!
Other great formulas to play with are:
=AVERAGE – to get an average of the cells selected
=RAND – to get a random number between 0 and 1 (inclusive)
=RANDBETWEEN – to get a random number between a min and max that you assign
=HYPERLINK – to input a hyperlink with a different text
=IF – a conditional statement if the cell is equal to a value or “string” then assign a number
=VLOOKUP – is really powerful and allows you to search places for anything you assign