Great Ideas Are Started With: “Is there a way…”

Here’s the setting. The teacher resource specialists for technology are gathered together for a team meeting. On the agenda, our, what has now become annual, Tweet 10 Challenge. One of our team members read a blog post by Alice Keeler. In it, she discussed ways to get students tweeting from class to a class Twitter account. Then the question came… “Is there a way to streamline this?” Yes, give me an afternoon and I believe I can setup a means to facilitate this through a Form.

First, I reached out to Alice

Next, using the resources from Alice’s postGoogle Support information, and a Form/Sheet combo created by my supervisor, Allan Johnson, for mileage tracking; I was able to combine them all to form a system, which in my humble opinion, would work effectively.

Alice’s resources set me up with the foundation of a Sheet which takes the information inputted (Tweet message, Tweeter, & Hashtag) and creates a link to tweet it out. Google Support’s page discussed a means of restricting ‘paragraph’ responses on a form to a certain number of characters. Allan’s Form/Sheet provided the bridge needed to connect the data collected in the Form to process correctly in the Sheet.

Now the kinda technical stuff:

  • The Form data is sent to a response sheet. Since each time a response is submitted a new row of information is inserted, I need a mediator Sheet in-between. The Data Sheet collects all information inputted on the Response Sheet via a formula. The Tweet Sheet pulls information from the Data Sheet to specific cells via other formulas. These multiple tabs are no longer needed with the update from 10/20/2015 This information is then constructed in such a way that the link created will display “Tweet Message” via “First Initial”. #”Class Hashtag”. Seems complex, but actually thanks to the work of Allan, Alice and Adam Schoenbart plus the work of formMule & copyDown add-ons it was kinda simple.
  • Now the Form needs to be adjusted for the number of characters allowed in the tweet message. A tweet has a maximum of 140 characters. We need to subtract from that number each character for the ending of the tweet ” via _. #__________” So for instance, if we used this Form to tweet to our district hashtag of wwprsd, then the character count would be 15. The Form’s first question would need an advanced setting of data validation – text – maximum character count: 125. This way when someone is typing in a tweet message, if they go over the character count of 125, they would receive a notification that their message is too long. I set this same method up for the 1st initial of the tweeter. Not only would full first names create a scenario of varying amounts of characters, but it also has some people uncomfortable with posting names through social media.

Now a student fills out the Form, the teacher can go into the Sheet to the Tweet tab, look over the tweet to approve it, then click the link created to Tweet. I would also recommend setting up a Notification under the Sheets Tools tab, just so you dont have to make sure you remember to check it. Embedded below are the Form and the formResponses tab of the Sheet. This is currently setup to allow for 2,000 tweets. When that limit is reached all the teacher would have to do is go to the Responses tab in the Sheet and Delete the previous rows of Form submissions.    

Do you have your students participate in tweeting? Do you think this could work for you? Reach out to me if you are interested in the above resources. 


Big Update!

So Add-ons… Mighty, powerful add-ons! I watched the inaugural episode of the ‘Schoencast‘ with Adam Schoenbart. After reaching out to him on Twitter, I asked if it was possible to use formMule‘s email to send me a clickable link of the tweet. It does, but after a little playing around with HTML. That playing around had me exploring formMule and noticed it’s cousin add-on copyDown. Well, talk about seeing the light! All of that data not playing nicely where I needed to have three tabs in my Sheet went good-bye; as did the notification setting on the Sheet. Now my setup is nice:

    • Form gets submitted with Tweet and first initial of first name.
  • Data from Sheet has formulas copyDown from designated row.
  • formMule sends me a pre-generated email containing the link to click to Tweet.

All the work is organized for me to simply check my email and click a link.


Here is a Google Doc with Directions for setting this up:


Leave a Reply

Your email address will not be published. Required fields are marked *