Scripting Google Spreadsheet to do email merge

I recently posted about using TextExpander to semi-automate the process of sending grade updates to students. That post got me poking around for other ways to do a more thorough mail merge, and I found a tutorial for scripting Google Spreadsheet to send emails. With some minor modifications, I now have a spreadsheet set up as a grade book that can email each student with their current point total and class average at the push of a button. Below is a description of how I adapted the original spreadsheet to make it do what I wanted. You can open my spreadsheet and make your own copy to modify, too.

The original file is designed to collect user information with a form, save it to a spreadsheet, and email the user. Working from the copy of the tutorial spreadsheet, the first thing I did was to delete the form, as I do not need it in my application. Then I rearranged the columns and added some for my assignments and for totals. I left the original columns for first name, last name, and email address intact to minimize the need to edit the script. The script uses the first row of each column to identify which variable that column holds, so it’s important to respect those labels.download film The Boss Baby 2017 now

When I had the tutorial spreadsheet how I wanted it, I customized the text of the email template to suit my purposes. I added two new variables, based on two new columns, Total Points and Class Avg:

template text to send email

Then I ran the script with myself as the test recipient, and I was disappointed to find that the value for Current Avg did not get filled in. I returned to the script and began looking for the place where the data range is set, finding it in line 4. The original tutorial spreadsheet has 4 columns, so the range is set to 4. I have 5 columns I want the script to read from, so I changed the dataSheet.getMaxRows value to 5:

screen shot of script text

I ran the script again and it worked as expected.

The last step I took was to customize the subject line for the automated email. In the tutorial spreadsheet, this subject line is hard-coded in the script, which seemed a little too permanent or hidden or something. I changed it to set the subject line by reading it from a cell in the ‘Email Template’ spreadsheet.

Any time I want to update my students on their grades, I just run the script by clicking on the Tools menu, selecting Script Manager, and clicking ‘Run’. This solves one more of the problems I’ve had weaning myself from the tyranny of the LMS.

 

 

How Vitamix Sells Pricey Blenders to Affluent, Health-Conscious Foodies

Having recently joined the Cult of Vitamix, I enjoyed this piece in BusinessWeek on the company that makes these awesome machines. Although I wasn’t the one driving this particular purchase, this pretty much sums up my burgeoning relationship with it:

For all its appeal to celebrity chefs and extreme athletes, a Vitamix is tailor-made for the semi-enlightened male vaguely inclined toward better nutrition yet still rooted in his natural state of couch-bound torpor.

On a somewhat related note, it also makes a mean whisky sour.

Using TextExpander for email merge

TextExpander iconIt’s the end of another semester, and a lot of my lab students have been asking me what their lab grade is going to be. I don’t keep an online gradebook for my labs, so I needed a quick way to send them an update with their current grade. I keep their grades in a spreadsheet, and in the past I went so far as to create a mail merge report and send each student a PDF of their results, which was a fairly time-consuming process. Instead I turned to a Swiss Army knife called TextExpander.

TextExpander is a program that runs in the background and waits for you to type a specific sequence of keystrokes. When it detects that sequence, it fires and inserts the text you have associated with that shortcut at the point of your cursor. Not only can it insert the prescribed text, it can also do some thinking and use variables. For example, here is my snippet for the text of an email to a lab student:

Lab Grade Update

You have a %clipboard in lab, which includes your lab exam grade and all assignments handed in to date.

The real magic in this snippet is the %clipboard part, which TextExpander fills in with whatever is present on the system clipboard when the snippet is expanded. Before expanding the snippet in the body of an email, I just select the grade in my spreadsheet and copy it. When I type the couple magic keystrokes, the text above is inserted, complete with that student’s score.

OK, so this isn’t so much a real ‘mail merge’ as a ‘data merge’, but it’s still a time saver and requires effectively zero setup. It also works with whatever is on the clipboard, meaning it is not tied to a specific data store, unlike a traditional mail merge and its data mapping requirements.

Chromebooks and the ‘technology floor’

A few weeks back I wrote about using Chromebooks in some of our biology labs, and now that the Acer C720 has started shipping, I ordered two of them to start testing. I’ve only had them for a day, so this is not a performance review in any way, but I will say that it seems like a very functional computer. I’m used to the 11″ MacBook Air as my daily computer, and the screen size and keyboard are on par with that, although the color gamut seems more restricted; so far the battery life seems much better than the Air.

Part of what I want to work through as I’m testing is what, exactly, is the service model I’m aiming for — what is the purpose for these? The current computers are used to run evolution and ecology simulation software and a statistics package. I didn’t even bother requesting them this semester for our new bioinformatics exercise, opting instead to encourage students to bring their own, which worked fine. So why not just continue to do that instead of investing in lab-owned notebooks? If we are going to have to virtualize some of the software anyway, why not just give students access to it on their own machines?movie Sleepless 2017 download

This would be consistent with the trendy practice known as ‘bring your own device’ (BYOD), but I’m not convinced it’s the right way to go for us. One of the biggest weaknesses of this policy for education is that it lacks any kind of predictability. I’m not referring to predictability in terms of make and model and minimum specs, I mean whether the student brought their computer that day. There is a great benefit to being able to count on certain equipment being available and functional when planning a lab. For example, I know that we have a number of nice spectrophotometers, so I can design a lab exercise that requires them. Knowing that each student or pair of students is going to have access to a computer, and knowing what that computer is capable of, changes the design of the lab, to put it simply.

Here are a few activities that come to mind:

  • The lab manual could be moved online. As it stands, we have the manual printed for the students and (try to) collect the cost from them, which turns me into a cashier. This could be as simple as a PDF or as complex as a real ebook with interactive content.
  • We could produce short instructional videos for routine lab techniques and link to them from the online lab manual. These would be for things like pipetting, using the spectrophotometer, setting up a TLC experiment, or even setting up a slide on the microscope, which seems like a neverending mystery to many students.
  • Get into more detail on the practical side of data management and statistical testing. As it stands, we send students away and ask them to perform simple statistical tests on the data they have collected, but what they take away from this varies widely across the class. Some really get it, but others can’t get a handle on it. It would be nice to do more show-and-tell before sending them away to work alone.
  • Do some real training in literature searching. We have a light requirement for incorporating primary literature into the 2 formal lab reports, but we don’t spend time in lab talking about how to do this. I’d like to change this.

I could go on with a dozen other examples, but none of these is surprising, nor do any require anything other than a computer with Internet access. But you have to know it’ll be there. Right now, the range of access to a computing device begins at ‘none’, and having a set of lab computers would drastically improve that to ‘something’. I guess that is what I find so attractive about this whole idea: it offers a ‘technology floor’ where there is none now.

The idea of a technology floor works on a number of levels here. It supports the objectives we decide on teaching toward in any particular lab, that’s its primary job. But it also doesn’t have to remain exposed, students could choose to bring an equivalent computer of their own and use it. I’m thinking of the difference between vinyl flooring and travertine tile — they look and feel quite different, but ultimately serve the same function.

Losing net neutrality

Once the court voids the nondiscrimination rule, AT&T, Verizon, and Comcast will be able to deliver some sites and services more quickly and reliably than others for any reason. Whim. Envy. Ignorance. Competition. Vengeance. Whatever. Or, no reason at all.

via We’re About to Lose Net Neutrality — And the Internet as We Know It | Wired Opinion | Wired.com.

MOOCs: Corporate welfare for credit – Salon.com

Smart article on Salon about the apparent strategy adopted by the big MOOC providers, which echoes that of the voucher/charter school proponents of the last two decades:

The plan is simple. First, declare a crisis in education that doesn’t actually exist. Second, declare that a for-profit model can fix the crisis. (This is easy when you get to invent the particular calamity.) Third, rather than starting small and building empirical support from experts in the field, seek sweeping legislative changes that lock your position into the system.

This isn’t, however, a head-in-the-sand piece about how everything is fine in higher ed. They point out that the MOOC providers have fabricated a story about how the problem is about access to college due to costs, when the real problem is about retention and degree completion.

Using data to improve retention

The University of Kentucky is making a major investment in data analytics to try to improve student retention. The approach is described in an article at Inside Higher Ed:

Every time students open the app to check their course schedule or the date for the next Wildcats game, they may be faced with a quick question: Have you bought all your textbooks already? Do you own a tablet? On a scale from one to five, how stressed are you?

The university collects a student’s responses to these kinds of questions on a per-student basis. To that record, they also add a student’s interactions with the campus LMS and participation in campus events, which are tracked through a card swipe-based attendance and incentive system.

All of these systems alone represent a big investment in tracking, but analytics is about doing something with all that data. UK has made a major push to make meaning from the data by hiring a team of 15 data analysts to develop and refine a predictive model of student engagement. The end goal is to increase retention rates which, assuming they’re even marginally successful, will more than pay for the investment in all the staff and databases.

Here’s how:

  • The cost of attendance in-state is about $20,000, and $30,000 for out-of-state (source)
  • The average financial aid award is about $10,000
  • So net revenue per student is about $10,000-$20,000 (assuming in-state students); let’s call it $15,000 for simplicity’s sake.
  • The freshman enrollment was about 4300 students
  • A 1% increase in retention is 43 students
  • 43 × $15,000 = $645,000 additional revenue
  • $645,000 × 4 yrs = $2,580,000
  • $2,580,000 ÷ 15 staff = $172,000 per additional staff line

And that’s making very conservative estimates throughout. That’s also not including the cost savings on the enrollment side of not needing to recruit as large a class.

Mine your own business, learner

Audrey Watters, who writes at Hack Education, has posted a transcript of a talk she gave at Columbia as part of their Conversations About Online Learning series. Setting aside the envy I have of a place that holds a lecture series about technology and higher learning, Watters goes deep on some of the implications of “data mining” in education, fleshing out some of the ways such data might be used and pointing out how risky that might be for students.

…all this data that students create, that software can track, and that engineers and educators and administrators can analyze will bring about a more “personalized,” a more responsive, a more efficient school system.

How will this magic happen? Using the same secret algorithmic sauce that companies like Google use to tailor search results and ads, and Amazon uses to sell you, well, pretty much anything. So what’s the hitch? There are at least two, according to Watters: privacy and money.

It may be obvious, but if data is going to make a big difference in student learning, that is going to require a sea change in the rules surrounding access to that data. Or is it? It appears that right now, the rules are being skirted by private companies that don’t have the same restrictions as actual schools. I suspect that most students and their parents aren’t aware of this end run around educational data privacy. It is access to this kind of data that will be necessary to assist with learning, in the absence of actual human interaction.

And the money? It’s not money in the sense of cost to students. On the contrary, most ‘big data’ education projects are free to the student, meaning someone else is paying. For now, the bills are being paid by venture capital investors that are expecting BIG returns. We’re in the early days, the thinking goes, of a major shift in the way education is done, and one of the biggest parts of this shift is the privatization of education. Sure, there has been some suggestion that these programs will lead to a system of credentials not unlike a degree, and some programs have even been rolled out. But for the most part, the schools with the biggest stakes in this territory thus far are not talking about any kind of equivalency between their live and online programs.