Multi-Dropdown Datavalidation Creator

This tool came about as a collaboration between Dan Sharpe and myself. He had the idea that there needed to be a better way to create the data validation dropdowns in Google Sheets. Since I was not super familiar with data validation I jumped on the opportunity to learn some new parts of Google Apps Script.

Check it out on the app store, or play around with the code below!

Coding with #COL16

I participated in a Hangout on Air with Daniel Sharpe, and the result was pretty great! We sat down and coded a decent start to an app that displays form responses in the style of Pinterest or Padlet. My internet crashed 4 times during the recording, but despite my terrible connection we would up with a decent product

Probably not for the absolute beginner, but I think there is something in there to be learned for an aspiring coder. (Experienced coders will probably ridicule my lack of style)

Do people want to see more of this? What can we introduce as we go along? Tweet at us @rheajt and @get_sharpe

 

Easy Grader for Google Sheets

Making using google sheets as a gradebook a little bit easier.

[su_youtube url=”https://www.youtube.com/watch?v=SOImg-iwT6c”]

[su_button url=”https://chrome.google.com/webstore/detail/easy-grader-add-on/bkloilagbahbeblhjgkojapenendkkpk” target=”blank” style=”flat” background=”#0c1790″ color=”#ffffff” size=”8″ wide=”yes” center=”yes” radius=”0″ icon=”icon: pencil-square-o” text_shadow=”0px 0px 0px #000000″]Find out more in the chrome web store…[/su_button]

I enjoy using spreadsheets to hold a lot of grades. There are many services that will send you reports in spreadsheet files, so it just makes sense to keep you other grades in that format.

The thing that started to frustrate me was entering grades from classwork. I created this add-on to try and alleviate those struggles.

After you install the add-on, open it inside of the spreadsheet where you want to enter grades.

tutorial-open

The sidebar is fairly straightforward. The select box at the top allows you change which column your grades will go into. Use the settings at the bottom to change which columns the Easy Grader will consider as grade columns.

tutorial-overview

Once you are setup, just select the box for student name. It will use the first three columns as its search field. Just type a few letters of the name and it should appear. Hit enter, then tab, type the grade, hit enter again. The grade will automatically appear in the spreadsheet and the focus will return to the student name box ready for you to enter the next grade.

tutorial-tab-power

As always, I appreciate feedback! Let me know how I can make this better.

 

Google Sheets: Functions for grading!

It is that time of the year when entering grades becomes the all-consuming activity for teachers everywhere. Since I am crazy about learning how to better use Google products to make life easier I thought I would share a few functions that have made grading easier for me. Excel provides similar functionality, but you will have to check the docs to figure out the syntactical differences.

Before I jump into the formulas, let me make sure everyone is aware of how to select data on a spreadsheet.

A1:D1 This will select all of the data in cells A1, B1, C1, and D1 and allow you to manipulate that data.

A very basic example of that would be: =AVERAGE(A1:D1) will provide you the average of the data in the selected range.

To take that a step further, we could select all the data in a column: =AVERAGE(A:A)

Or maybe we want a set of data from a different worksheet inside the file: =AVERAGE('Another worksheet'!A1:D1)

These functions can then be nested inside each other: =ROUNDUP(AVERAGE(A:A))

The functions purpose is usually pretty explicit from the name. Obviously, AVERAGE provides the average of a data range. ROUNDUP takes a number and rounds it up to the nearest integer.

That is a very basic explanation of how to select data in Google Sheets. Now onto the functions that I found helpful in my gradebook.

I want to take the average of the top 4 grades…

=ROUNDUP(AVERAGE(LARGE(A1:F1, 1), LARGE(A1:F1, 2), LARGE(A1:F1, 3), LARGE(A1:F1, 4)))

Looking at this formula from the inside out, first you see the LARGE(range, position) function. This takes the 1st, 2nd, 3rd, 4th values respectively from the range A1:F1. (Replace that range with whatever you want)

Then that is wrapped in the AVERAGE function, which is in turn wrapped in the ROUNDUP function. This will get an average, and roundup that average to the nearest integer. Make sense?

I want to count text fields as numerical values…

Sometimes I like to mark in my gradebook “Complete”, “Incomplete”, “Missing”, or “Absent”. When you use a function like AVERAGE or ROUNDUP then it completely ignores these text fields. But let’s say I want “Complete” to be equal to 100, “Incomplete” to be a 50, “Missing” to be a 0, and “Absent” to be ignored. For this we will need to create a custom function. Along the toolbar select ‘Tools’ > ‘Script editor’ and create a blank project.

Copy and past the code above into the blank ‘Code.gs’, save your work, and return to your spreadsheet. You now have access to a =AVGCOMPLETES(range) function that will perform the required tasks.

Hopefully this has given you an idea or two about how to better manage student data in Google Sheets. Post a comment if you have any questions.