My Birthday Workflow: How to App Smash Birthday Tweets to Students
Wednesday, September 16, 20159:11 AM
Sure, teachers get summers “off,” but if you’re anything like me, school is always somewhere on your mind. It doesn’t matter where or when--and often it’s too often--but I found myself jotting down notes and ideas all summer long.
Here’s one that stuck with me: use social media to wish students’ a happy birthday. One of my goals this year is to incorporate social media into my classes in meaningful ways, and this seemed like a nice idea. Turns out it was fairly easy to automate, too, using some of my favorite tools and some app smashing. I wrote about Automating Blogging Workflow with #IFTTT a few weeks ago, and this post is something of a follow up to its examples.
Here’s what you need: Twitter (a personal or class account), A Google Apps account (for Forms, Sheets, and Add-ons), If This Then That (to send the info to be Tweeted), and a new GMail account. To make this work, you should create a brand new email account that is only used to automate Tweets. This will be explained around step five--just trust me for now. I know, I know. It seems complicated. I tried to make the steps clear and use a lot of screenshots to help you automate happy birthday Tweets for your students, too. Please ask questions, comment, and let me know how it goes!
Google Forms & Sheets
As online data collecting so often does, it all starts in Forms.1. Create a Google Form that collects students’ birthdays.
Make sure to ask for name, Twitter handle, and any other relevant info. Find an example Birthday Form here, but I usually incorporate this into a larger form to get to know students at the start of the year.
2. View Form Results.
You can either program this whole process to automate itself or wait and do it after students complete the form. If you are automating, fill out the form with some dummy data, like I did below.
3. Add a Formula Column
View my results spreadsheet here if it helps. Columns A through F are filled with the basic form results. Now, in column G I added a column titled Birthday?.In cell, G2, copy and paste this formula:
=if(month(E2)&day(E2)= month(today())&day(today()),"SEND","")
The formula above assumes that cell E2 has the first student’s birthday in it. Then, it says if the month and day of today match the month and day of E2, then SEND. If today is the student’s birthday, the cell will actually read “SEND,” otherwise it will remain blank. This is essential for the next step. The formula looks a little complicated, but you don’t need to change anything unless your birthday column is not E. Thanks a lot to Alice Keeler for help with the formula. Believe it or not, my first draft was much more complicated, and she gave me the one above, which is much simpler and elegant. Follow her for all the best Google tips.
Now you need to fill that formula down the entire column. The simple way to do this is to click the cell, grab the blue square in the right corner, and simply drag down the column after the data is submitted in the form. This means have the students complete the form, then fill the code. Alternatively, check out the Copy Down add-on for Sheets to automate this if you want to have the formula fill each cell in the column as data is submitted live.
For this task, Form Mule is used to merge the data into the subject line of an e-mail.
Once you install an Add-on, it’s there to stay and can always be found on the Add-ons for that particular Google App. For this task in Form Mule, most of the default settings are where we want things. Once you launch, a box like the one below should pop up. Select Form Responses 1 as your sheet with the merge data (or the appropriate one if you’ve made any changes).
To help automate the process, turn on Time Trigger. Select Day timer and pick a time for the process to run every day. I suggest using a time that students are in school, or at least presumably awake, because this is the time that the Tweeting process will automatically run.
When your settings match mine below, click Next
Next, click Save and then Edit on the bottom of the panel.
Finally, we have to tell Form Mule what to send in the e-mail, and it’s fairly simple. The only fields that need any text are To: and Subject:.
In To:, type the new e-mail you have created for automating Tweets.
In Subject:, we need to type the message that you want Tweeted. What you type here will be the exact message Tweeted, but the student information from the form will replace the information in the brackets.
For example, this subject line:
Happy Birthday, <<Last Name:>> <<First Name:>> <<Twitter Handle:>>!
Will Tweet (which you can see by clicking preview in the window above):
Feel free to adjust the subject line to meet your needs. Include a class hashtag or other message. You can merge any additional information from the form as necessary, too, but make sure to be mindful of the 140 character limit.
To do this, we will use If This Then That recipe gmail to twitter. The key here is to create your #IFTTT account using the same e-mail you entered in the To: field of Form Mule. Once you do this and authorize the Twitter channel, you are almost done.
Once the recipe is turned on, enter your e-mail address in the Trigger field. This is your normal e-mail--the same Google Account you used for the Form, Sheet, and Form Mule. In Action, it should say Subject, just like it does below. Basically, this means that when the account e-mail (the new one you created) receives an e-mail from you (which is what Form Mule will do on the birthday), Tweet out the message.
I know it seems complicated, but trust me: it gets easier. App smashing with add-ons and #IFTTT is a powerful tool for responsive blogging and automating the process. If you give it a try, let me know how it goes. If you have any ideas to make it better, I’d love to know that, too.
The formula above assumes that cell E2 has the first student’s birthday in it. Then, it says if the month and day of today match the month and day of E2, then SEND. If today is the student’s birthday, the cell will actually read “SEND,” otherwise it will remain blank. This is essential for the next step. The formula looks a little complicated, but you don’t need to change anything unless your birthday column is not E. Thanks a lot to Alice Keeler for help with the formula. Believe it or not, my first draft was much more complicated, and she gave me the one above, which is much simpler and elegant. Follow her for all the best Google tips.
Now you need to fill that formula down the entire column. The simple way to do this is to click the cell, grab the blue square in the right corner, and simply drag down the column after the data is submitted in the form. This means have the students complete the form, then fill the code. Alternatively, check out the Copy Down add-on for Sheets to automate this if you want to have the formula fill each cell in the column as data is submitted live.
Form Mule Sheets Add On
Form Mule is a fantastic mail merge add-on for Google Sheets. It plugs the information from a row and merges it into an e-mail to match a template you design.For this task, Form Mule is used to merge the data into the subject line of an e-mail.
4. Install Form Mule
In sheets, click Add-ons on the top menu (between Table and Help). Then click Get add-ons. Search for Form Mule, click the green Free button, and install it. Follow the directions to authorize it to use your account.5. Launch Form Mule
Once you install an Add-on, it’s there to stay and can always be found on the Add-ons for that particular Google App. For this task in Form Mule, most of the default settings are where we want things. Once you launch, a box like the one below should pop up. Select Form Responses 1 as your sheet with the merge data (or the appropriate one if you’ve made any changes).
To help automate the process, turn on Time Trigger. Select Day timer and pick a time for the process to run every day. I suggest using a time that students are in school, or at least presumably awake, because this is the time that the Tweeting process will automatically run.
When your settings match mine below, click Next
5. Edit the Send Conditions and Template
Form Mule will run for every submission unless we set a specific condition; we only want it to run when the current date matches the student birthday. To do this, we need to create a send condition. On the Send Condition drop down menu, find the BIRTHDAY? column we created a few steps back. In the blank box on the right, type SEND. This will match the formula from step 4, which means that the e-mail to automate the Tweet will only send if the dates match.Next, click Save and then Edit on the bottom of the panel.
Finally, we have to tell Form Mule what to send in the e-mail, and it’s fairly simple. The only fields that need any text are To: and Subject:.
In To:, type the new e-mail you have created for automating Tweets.
In Subject:, we need to type the message that you want Tweeted. What you type here will be the exact message Tweeted, but the student information from the form will replace the information in the brackets.
For example, this subject line:
Happy Birthday, <<Last Name:>> <<First Name:>> <<Twitter Handle:>>!
Will Tweet (which you can see by clicking preview in the window above):
Feel free to adjust the subject line to meet your needs. Include a class hashtag or other message. You can merge any additional information from the form as necessary, too, but make sure to be mindful of the 140 character limit.
6. If This Then That
Finally, we are ready. We have used a Form to get the information, used a Sheets formula and add-on to automate an e-mail to wish the student a happy birthday on the right day. Now, we need to make that e-mail trigger a Tweet.To do this, we will use If This Then That recipe gmail to twitter. The key here is to create your #IFTTT account using the same e-mail you entered in the To: field of Form Mule. Once you do this and authorize the Twitter channel, you are almost done.
Once the recipe is turned on, enter your e-mail address in the Trigger field. This is your normal e-mail--the same Google Account you used for the Form, Sheet, and Form Mule. In Action, it should say Subject, just like it does below. Basically, this means that when the account e-mail (the new one you created) receives an e-mail from you (which is what Form Mule will do on the birthday), Tweet out the message.
Happy Birthday to You
So to recap: The form results populate the sheet, which copies the formula to verify the birthday. Then, when the birthday occurs, the formula fills the column with SEND, triggering Form Mule to merge the row into a birthday message, sent to your new e-mail address. Finally, #IFTTT, with the gmail to Twitter recipe, sends the subject line of the merged e-mail to Twitter. Still following?
Happy Birthday, Bruce Wayne @IAmBatman!
— Adam Schoenbart (@MrSchoenbart) September 16, 2015
I know it seems complicated, but trust me: it gets easier. App smashing with add-ons and #IFTTT is a powerful tool for responsive blogging and automating the process. If you give it a try, let me know how it goes. If you have any ideas to make it better, I’d love to know that, too.
aschoenbart@gmail.com
@MrSchoenbart
+Adam Schoenbart
aschoenbart.com
