Blog Post

Google Gradebook Templates

Over the weekend, I went hunting for a gradebook to incorporate in my class website. I was looking for something intuitive (for me) and accessible by individual students (obviously I don’t want them to see everyone’s grades). I hoped to use Google sheets, as I already run activities and assignments through Google Forms in class and I’m encouraging the use of Google Docs for students’ group writing projects this semester.

I didn’t quite find what I was looking for, but I did find lots of piecemeal instructions that allowed me to create what I needed. In particular, I found some elements’ of Anthony’s Google Sheet/Script Editor app helpful (the code is, alas, too buggy to use) and I appreciated the assistance of a few good souls at Stack Overflow who suggested fixes for the code. (Javascript is, unfortunately, a bit beyond me at the moment.)

In the end, I cobbled something together using basic functions in Google Sheets. As my dissertation project touches on the Internet’s potential to produce collaboration and encourage transparency about how ideas, objects, and knowledge are produced, I thought I’d share what I’ve made in the hopes of demystifying the process and improving it through collaboration with yet-unknown others.

The files below can be used as templates. These are particular to the needs of my class, but should provide a decent foundation that can be customized for any classroom. There is a “Master Gradebook” that compiles the grades of all students and an “Individual Grades” template that can be endlessly reproduced and adjusted depending on the number of students you have. If you’re comfortable with Google Sheets/Excel, feel free to download and open up the grade books and start adjusting them to suit your own needs.

Master Gradebook Template

Screen Shot 2015-08-06 at 12.49.07 PM

Individual Grades Template

Screen Shot 2015-08-06 at 12.49.25 PM

If, however, you’re learning Sheets/Excel as you go (like me), here are some of the nifty formulas that make things work in the spreadsheet.

  1. SUMPRODUCT: On the first sheet of the Master Gradebook (‘Master’), I calculated weighted grades using the SUMPRODUCT function rather than a basic mathematical formula – i.e., [(E3*5+F3*15+G3*5+H3*15+I3*5+J3*15+K3*10+L3*15+M3*15)/100]. Not only is the SUMPRODUCT formula cleaner, it also updates automatically if you choose to change the weight of a grade.
  2. TRANSPOSE: The second sheet in the Master Gradebook is the ‘Transposed’ sheet. I wanted student’s grades to display on a single sheet in the Individual Grades sheet, so transposing information from rows to columns seemed like the way to go. The information remains the same, it just changes how you view it.
  3. LOOKUP: The LOOKUP function allowed me to create a grade scale and then transform the percentages into letter grades. It took a little finagling to get it to work (the trick is to sort in ascending order in the percentages column), but it’s fantastic to see the formula switch things over automatically as the grades are adjusted.
  4. IMPORTRANGE: This is the thing that makes it possible to create individualized grade sheets from the master sheet. The function utilizes the Spreadsheet Key and an established range from another spreadsheet to filter data into individual sheets. To share with individual students, I will send an email invite to each student from her/his Individual Grades spreadsheet.

The rest of the formulas in the spreadsheet are very simple mathematical functions (sums, division, etc.) that can be adjusted as you go.

Please Note: changing the text formatting in the ‘Transposed’ sheet on the Master Spreadsheet will not transfer to the Individual spreadsheet. You’ll have to manually format the text in that sheet. Adding or subtracting cells/rows in the ‘Transposed’ sheet does, however, does directly affect text formatting in the Individual spreadsheet. That is, it completely destroys all of your formatting below the point where you added the row. I haven’t quite figured out how to fix that yet (except to just remove all text formatting), but I’ll update this post and the document as I can.

If you have questions regarding how something works (or if something stops working), leave me a note in the comments. I’d also love to hear suggestions for improvements from people more spreadsheet savvy than myself. You can add comments to the spreadsheet itself or leave me a note below.

Blog Post

Things I Found This Week: Dissertation Distractions Edition

The best/worst part of a dissertation project involving social media and technology is the constant discovery of blogs, online journals, and tech tools for teaching and researching. After reading any book or article, after every discussion with a peer or scholar, I’m left with a lengthy list of new resources – which at the moment seem far more interesting and exciting than the harder work of sitting down to, you know, actually think, read, and write about my topic. For instance:

I’m itching to play around with Omeka and Scalar, two resources a fellow Drew student, Jessica Brandt, was good enough to alert me to.

Omeka looks like a more traditional blog/website platform, but it’s designed to assist scholars (amateur and professional) and institutions in creating top-notch online archives, exhibits, and narratives. The platform allows for beautiful image collections, searchable tags, interactive images, and customizable themes, fonts, etc. The software is free and open source and looks like a powerful story-telling tool.

(What Is Omeka from Omeka on Vimeo.)

Scalar is exciting for the way it allows scholars to structure their narratives in fully digital ways. The platform’s purpose, according to creators, is to give “authors tools to structure essay- and book-length works in ways that take advantage of the unique capabilities of digital writing, including nested, recursive, and non-linear formats.” Authors can create multiple paths through the same project, tag paragraphs and sections to create relationships throughout the “document”, and insert multimedia content related to the text portions of the project. My project could, I think, benefit from all of these possibilities and I’m excited to give it a test run once the topic is a little more structured.

(Scalar Platform — Trailer from MA+P @ USC on Vimeo.)

I’m also super tempted to enroll in one (or…all) of the online courses offered by Hybrid Pedagogy. The upcoming course topics are “The Flipped Classroom,” “Teaching with Twitter,” and “Learning Online” – all topics of interest to me and all for very reasonable prices ($250-350, with discounts for adjuncts and students). Unfortunately, I’m pretty sure that adding an online class to the mix of dissertation prospectus + two classes + training for half-marathon would turn out to be a little much… Alas. I’ll just have to keep an eye on the offerings in the Digital Pedagogy Lab in the future.

Two final resources/projects on my radar this week: Educause Review (my thanks to Gamin Bartle for this one), which looks to be full of all sorts of thoughtful pieces regarding technology and the digital age in and education, and the Wikipedia page for the feminist sci-fi film, Advantageous. The film is gorgeous and provocative and made my cyborg-loving self terribly happy. The Wikipedia page doesn’t do it credit – by which I mean the information is super basic. So I added a link for one of the actresses last night and today (or tomorrow or whenever I decide to neglect other work), I’d really like to add a plot summary or something about critical reception, and them maybe begin work on pages for Freya Adams and Samantha Kim. I’ve been meaning to make a foray into editing Wikipedia for awhile and this seems like a good place to start.

Leave a comment if you’re using similar resources or if you want to talk ed-tech, social media in the classroom, or dissertations. Or anything else. Now to the real work of the day for me – updating my class website to include the new syllabus and a list of topics for the semester’s portfolio project. (Will provide links for those once they’re ready to go…) I also need to finish off an e-book of last semester’s blog posts for my students – it’s the closest I can get to preserving their work for the moment and that task has been on the back burner for far too long.

Blog Post

Streamlining Classroom Technologies

I get a little over-exuberant about educational technologies – and can go a little overboard with the number of tech tools I use in the classroom. This semester, I started with three technologies: Socrative, a Facebook Group, and a class website where I’m hosting the syllabus, Google forms for assignment submission, and our class blog (currently private, per our class consensus). Those three technologies all have different advantages that I hoped to tap into:

Socrative is a free clicker app that lets me do real-time polls in class, collect attendance, and ask for feedback at the end of class. I used it last semester and I liked the platform – though it can be a little unpredictable. (The mobile app simply refuses to respond sometimes.) The responses are neatly compiled into a spreadsheet that I can either store for later or send to my Google Drive immediately. I can open and close polls – which means I can track which students are late based on who responds to the exit quiz at the end of class, but not the attendance quiz at the beginning. I can also switch up the room name, which reduces the possibility of students logging in remotely, thereby receiving credit for attendance without showing up in class. (For a variety of reasons, that happens from time to time.)

The Facebook Group is working well as a space to share links related to class and to post short announcements without flooding student inboxes. It also allows me to message students privately or as a group – and they’re wonderful at responding promptly. I’m hoping the FB Group will eventually grow into a space where students collaborate with one another and share links and articles they find as well.

The class website is the hub of all of our class information and is proving to be a super flexible online space. WordPress has a couple of limitations (most notably, there’s no way to create tables on pages or in posts) but there are workarounds (I upload screenshots if something must be presented in a table – like our blog post/comment rubric or the grade scale in the syllabus). Otherwise, it’s been easy to adapt the website to what I need it to accomplish – I started with pages for announcements, the syllabus, and a blog, and have since expanded the pages/categories to include a space for class slides, organizational resources, and the schedule for students’ blog posts.

My favorite feature by far, though, is the ability to embed Google Forms into pages and posts. This means I can ask students to submit discussion questions and other assignments directly through the class website. Their submissions are all compiled into a neat spreadsheet on Google Drive – just like Socrative.

Which brings me (finally) to the title of this blog post: I really need to streamline my classroom technologies.

Because, at the moment, I am using two technologies that are essentially accomplish the same things. Both Socrative and Google Forms can be used as polling mechanisms, I can take attendance through both (submissions through forms are time stamped), and asking for the exit quiz is a non-issue (I don’t read that until after class anyway). Plus both tools funnel collected information into spreadsheets on Google Drive. Socrative has a couple very slight advantages over Google Forms – it’s more aesthetically pleasing and updates more effectively for real-time responses – but Google Forms works more consistently. For me, that’s a decisive advantage and using Forms alone allows me to simplify the technologies used in the classroom.

So, for today’s class, I created a page for “Quizzes” on our class website and a post that contains Forms for today’s attendance quiz, a brainstorming/definition quiz, and the exit quiz. I’ve provided details regarding the path to access the Forms on my slides for each quiz (“Class Website -> Quizzes -> Quizzes for 2/4”) that I’m hoping will make the switch fairly seamless. Keep an eye out for future posts on the success or failure of this little experiment!