Use a Google Docs Spreadsheet to Send Text Message Alerts to Students

SMS or cell phone text messaging alerts can be extremely handy. Research shows most people read text message alerts almost immediately, and when students have an unlimited text messaging plan no additional costs are incurred by them to receive SMS updates. The last three semesters I’ve used the lowest commercial version of TextMarks to send periodic text message updates to my preservice education students. This year, I’m helping our school debate team as a parent volunteer and wanted a free way to send text message alerts to team members. Last week I collected, from students who chose to opt-in to these alerts, their name, phone number, and cellular carrier. The cellular carrier is important because each wireless phone company uses a different email address domain to enable sending text messages using email. I learned about this a few years ago as a participant in the “Film on the Fly” cell phone videography contests. (See this post from April 2009 for our “Pi Day” entry from the previous month.)

Today I created a Google Spreadsheet and used the CONCATENATE function to create or build the email address I needed for each student’s cell phone number. This Google Help page explains how the function works. The formula is shown below.

The results of the formula, after I “filled down” to copy it to all rows of the spreadsheet, are shown below. Each person has a custom email address using their cell phone number and the appropriate mail domain for their cellular provider.

The next step was sending the email message. I tried a test message to myself first, which worked fine, so then I messaged the students. I simply copied and pasted the email addresses from my spreadsheet into an email message, but it’s definitely possible to get fancier than this and use Google Scripts to send email. For example, the “Contact Us Form Mailer” is a free script which can send a custom message to email recipients specified in a Google Spreadsheet. To find this script and add it to your Google Spreadsheet, choose TOOLS and SCRIPT GALLERY, then search for MAIL.

This 1 minute, 20 second video shows how this can work. You need to name your three columns exactly as they are specified in the script.

If you want more assistance with this process, check out the Google Apps Script Tutorial: Automating a Help Desk Workflow. This is more complex than what I attempted this evening, but it’s AWESOME to see the power of scripting in Google Spreadsheets and glimpse some of the flexible power which it can unleash for us. Also check out FormEmailer, which appears to be a more robust and customizable free solution.

I’m going to share this Google Spreadsheet with my son’s debate teacher and show her how she can use it to send SMS alerts to students as well as parents who want to receive them. I also may create a custom form tied to the spreadsheet so she can solicit new sign-ups with an online form. The one change I’ll have to make to the form is inserting a formula in column C so the celluar provider’s email domain is added automatically depending on the carrier they specify. The English WikiPedia page, “List of SMS gateways” includes an updated list.

Have you found a simpler, FREE way to send SMS text alerts to your students or to other groups? If so I’d love to learn about it. (I’m not talking about a “free trial version” of a commercial service, however. I’m talking FREE as in NO COST, ever.) I tried Textmarks Lite, but found the dating / “meet singles in your area” ads which are included to be potentially offensive to some of my undergrad students. Certainly that’s not appropriate to use for K12 settings.

Learn more about the communication tools I’ve used the past few semesters with undergrad students in my December 2010 post, “Lessons Learned Teaching EdTech to PreService Education Students.”

Technorati Tags: classroom, code, free, google, sms, students, teacher, text, alert, alerts, textmarks, spreadsheets, script

---

Remember to follow Wesley Fryer on Twitter (@wfryer), Facebook and Google+. Also "like" Wesley's Facebook pages for "Speed of Creativity Learning" and his eBook, "Playing with Media." Don't miss Wesley's latest technology integration project, "Mapping Media to the Common Core / Curriculum."

On this day..

• Send Web Articles to Kindle from an iPhone with Readability - 2012
• Make free phone calls to land lines (US and Canada) from GMail #gct - 2010
• Easily Sync Google Calendars to your iPhone - 2010
• Why publish school updates on Facebook? - 2010
• Translate your WordPress blog into 40+ Languages - 2009
• iPod Touch Camera and Mic Coming Sept 9? - 2009
• Student Reporter interviews President Obama - 2009
• Oklahoma Social Media Publishers: Throw Your Voice on October 3! - 2009
• StoryChasers Brainstorm 4 - 2008
• Capturing DVD Video for editing - 2008