The Elusive Google Site Twitter Feed Found!

I love Google Sites, I really do. I encourage my teachers to use it for their class sites by expounding how great it connects with Google Drive. My pitch falls short when the teacher tries to embed her class Twitter feed and discovers that Sites doesn’t play nice. Never fear, all you have to do is create a custom Twitter gadget for Sites - and it isn’t even hard.

What you need to make your custom Twitter gadget for Sites:

• A simple text editor.  On my Mac I’ll use TextEdit. For Windows you can use Notepad.
• A Google Site with “Attachments” turned on
• A Twitter feed or list that you want to add to your site
• The HTML for your feed gadget

Create Your XML File

Lets start by creating a gadget XML file and open a new document in your simple text editor. You don’t want any formatting, so the simpler the editor, the better. (edit: Since I originally wrote this post it's clear from the comments that some have run into problems with their text editors. The file that you save must be a raw text file and lots of desktop editors don't do this without some fiddling. I recommend using the Chrome App "Text." It's free, it's cross platform, and it works great.) This XML file will be the delivery mechanism for your Twitter gadget. Copy and paste the following code into the new text file:

<?xml version="1.0" encoding="UTF-8" ?> <Module> <ModulePrefs title="Twitter Timeline"/> <Content type="html"> <![CDATA[

//Twitter feed goes here

]]> </Content> </Module>

Then save this file as “twitterFeed.xml.”

Get your Twitter Gadget HTML

Now you are ready for your Twitter feed. Log into Twitter and click on your avatar at the top right of the screen to access the setting menu, then click “Settings.” Once in the Settings window, click the last item in the left sidebar, titled “Widgets.”  Then, click the “Create New” button on the top right.

The “New Widget” screen gives you lots of control over how your gadget will work. Customize your gadget and click “Create Widget.” Then, copy the code on the bottom right. Return to the XML file that you created earlier and replace the text “//Twitter script will go here” with the feed code you got from Twitter. Now save the XML file.

Host Your Gadget

Hosting your new XML file is simple, all you need is a public Google Site. I’d recommend using the site where you are adding the Twitter Gadget. Scroll to the bottom of the Sites page look for the link to “Add files.” If this can’t be found you need to either turn on attachments (Settings > Page Settings) or sign into the Google Site.

Click “Add files” and attach your new XML file. Then, right-click, or command-click, the download icon to the right of the file and select “Copy link address.”

Add Your Gadget to Sites

Edit the page where you want the Twitter feed and click “Insert.” After this, click “More Gadgets.” Finally, click “Add Gadget by URL” and paste in the Twitter gadget XML link. Once you press the “Add” button you’re done: you’ve successfully created a Google Gadget for Twitter!

A Rebuild of the Moderated Twitter Form

A few months ago I blogged about a system that would allow students to tweet to a class Twitter feed after teacher moderation. Since that post, the good folks at the +New Visions for Public Schools Cloud Lab rebuilt Form Mule (an important component in the system) to work in the new Google Sheets. This post will explain how to build the Twitter Moderator system using the New Sheets, which I highly recommend you use. A few teachers asked for a video tutorial. I like a tutorial that I can read so I created both. I hope it's helpful!

This recipe has five components:

• A simple Google Form linked to a Google spreadsheet
• The FormMule Addon from New Visions for Public Schools
• A Gmail account created specifically for this process
• An If This Then That recipe
• A classroom Twitter account

First, create a simple Google form with a paragraph text question for the exit ticket. This PDF from Classroomfreebies was the inspiration. I used data validation to set the maximum character count of the question to 140 (this is a simple but important step.) Click “Require [Your Domain] to view this form” and “Automatically collect respondents username” if you are using this with students. Now, click “view responses” at the top of the form edit window to open the Spreadsheet attached to the form.

Then, add a column labeled “Moderated” to the responses sheet and install the New Visions FormMule Addon. Click “addons” in the menu bar, then “formMule”, then “Launch.” Once launched, select the Form Responses sheet for the source of merge data. Then, activate the time based trigger switch and set the trigger to run every hour. Click “Next: Templates and Send Conditions.”

We are only going to be sending one type of email. Change the send condition to point to the “Moderated” column. I usually use “Not Null.” This means that anything typed in the “Moderated” column will send the corresponding tweet to your Twitter account. If you want to be more specific, use a command like “yes.” Then, click “Next: edit templates.”

The email template is very simple. Click into the “To:*” field and add the GMail address that you created for this project. Click into the “subject” field and set this as well. Personally, I like using the Timestamp as the subject. You may also want to use the username. Then click into the “body:” field and then click the “What would you like to tweet about today?” tag. Finally, click “Preview and send all.” The Google side of the system is now done! Now we need to set If This This Than That (IFTTT) to send moderated tweets to Twitter.

Log into IFTTT, your new GMail account, and your class Twitter feed. Return to IFTTT and open this recipe. Once you add the recipe you will need to connect IFTTT to the Twitter and GMail accounts. Once the recipe is activated, scroll down on the edit window to the trigger and set the address to be the same GMail account you used in formMule. Click “Update” and you are done!

There you have it - the updated Twitter moderation feed using the new Google Sheets and formMule Addon. As before, let me know if you use it in your classroom! If you think of a way to make it better, let me know about that, too!

Student Timelines from a Google Form using sheetSpider

An update for this project has been posted here.

The goal of this project is to give students a personal timeline of the wonderings and observations that they make using a Google Form. Then, this slick timeline can be embedded in a student portfolio. This project can be integrated into the moderated Twitter exit ticket that I blogged about a few weeks ago, or it can stand alone.

Here is what you need:

• A simple Google Form linked to an “old” spreadsheet that required students to sign into your domain.
• The SheetSpider Script from New Visions for Public Schools (thus the “old” spreadsheet)
• An affinity for nice looking timelines

I can’t wait to implement this in my school, and hopefully build it into some student portfolios. One word of caution: this system needs the student satellite sheet to be published to the web in order for it to be read by the Timeline application. The next section explains how to do this yourself, step by step. There currently isn’t a moderation component although one could be built in, similar to the one used for the Twitter system.

So How Does This Work?

SheetSpider is a nifty script that lets you distribute values to multiple sheets based on a criteria - in this case the criteria is the student Google ID.  Based on this ID, sheetSpider will send each student’s exit ticket responses to a unique Sheet for that student. SheetSpider can be set to use a template when making the satellite sheets and we will use the template file from Timeline JS. If you haven’t seen this Timeline creator, you should.  It does a beautiful job.

Make a copy of the Timeline template and then use the “copy to” feature to add the sheet to your “Exit Ticket” sheet file. The timeline sheet has more columns than we need for our simple exit ticket form, but we need the whole template when we copy the student submissions to the satellite sheets. Specifically, we want to send the form response timestamp to the “Start Date” column, the exit tweet column to the “Headline” column, and the username column to "Media Credit." A simple function like the one below can move the data from tab to tab, you'll see it at the top of the previously mentioned columns in the demo sheet.

= ARRAYFORMULA ( If ( 'Form Responses 1'!C2:C = "", "", 'Form Responses 1'!C2:C ) )

Now it’s time to install the sheetSpider script. Keep in mind that as of this writing, sheetSpider is only available with an  "old" Google Sheet. You must click "tools" > "script gallery" and search for "sheetSpider." A tab named “SpiderSheetEntities” will be created when you install and initialize the script.  Then, click the sheetSpider menu item and proceed to “Step 1: Set Up Entity Sheet.” 

We have to set the script to run on “Manual Push” because we are using the copied timeline sheet to trigger sheetSpider instead of the form responses sheet. All of the items in Step 1 can match to the columns in the “SpiderSheetEntities” tab that was already created. You might want to create a folder for your satellite timeline sheets and paste the folder ID into the “Key of Primary Folder…” field.

Now proceed to “Step 2: Provision/Update…” In the first field, paste the document Key of the Timeline JS template. The feeder sheet needs to be set to “od1” which is the tab that we copied over from the template. The unique entity name can be set to “Media Credit” which is where we fed in the student usernames. At this point, click the “Save and Provision…” button.

“Step 3: Disagregate and Push Data” is the last sheetSpider step we need. Make sure your settings are “Manual Push” and “Append only new unique records.” The uniqueness criterion can be both “Start Date” and “Media Credit” to ensure uniqueness. Running Step 3 will create the satellite sheets. You will need to publish those sheets for the timelines to work.

Now lets connect the timelines.  Open the “SpiderSheetEntities” tab and insert two columns to the right of Column F. Name these “Timeline Embed” and “Timeline Link.”  Then, add one column to the right of “J” and name it “Spreadsheet Key.”

Paste the following function into K2:

=ARRAYFORMULA(right (I2:I,23))

Paste the following function in G2:

= ARRAYFORMULA ( IF (A2:A = "", "" , "<iframe src='http://cdn.knightlab.com/libs/timeline/latest/embed/index.html?source=" & K2:K & "#gid&font=Bevan-PotanoSans&maptype=toner&lang=en&height=350' width='100%' height='350' frameborder='0'></iframe>"))

Then, paste the following function in H2:

= ARRAYFORMULA ( IF (A2:A = "", "" , "http://cdn.knightlab.com/libs/timeline/latest/embed/index.html?source=" & J2:J & "&font=Bevan-PotanoSans&maptype=toner&lang=en&height=650"))

These last functions complete the project.  Student submitted exit tickets are aggregated to personal spreadsheets, and once the spreadsheets are published, they are used to create embeddable timelines. The embed code works great but not in a Google Site. Use the "Timeline Link" and the iframe gadget if you are using a Google Site. Let me know if you use the project or make it better!

Using FormMule and IFTTT for a moderated student twitter feed.

This project has been updated to use the new Google Sheets and formMule add-on, read about it here

Just about a year ago, I had the pleasure of camping with +Jasper Fox. We talked about Twitter in the classroom as we sat around the fire.  As usual, the conversation was great, we strategized about a Google form that a would allow students to compose tweets and the teacher to moderate them directly to Twitter. It took me a year to realize this simple workflow, but I think my teachers will love it in September. It’s purposeful, student centered, helps to reinforce digital citizenship, has an authentic audience, and promotes a positive home/school connection: win, win, win, win, and win.

This recipe has five components:

• A simple Google Form linked to an “old” spreadsheet
• The FormMule Script from New Visions for Public Schools (thus the “old” spreadsheet)
• A Gmail account created specifically for this process
• An If This Then That recipe
• A classroom Twitter account

First, create a simple Google form with a one paragraph text question for the exit ticket. This PDF from Classroomfreebies was the inspiration. I used data validation to set the maximum character count of the question to 140. Then unlink the form from the original spreadsheet, create a copy of this “old” spreadsheet, and relink the form to the new copy.

Then, add a column labeled “Moderated” to the responses sheet and install the New Visions FormMule script. After initializing the script, proceed to step 2A and set FormMule to fire an email when the moderated column equals “1.”

After Initializing FormMule, you will see a new sheet titled “Email1 Template.” Go to this sheet and set the “To” field to equal the gmail address where you want the moderated tweets sent. The “body” should equal the header of tweet column. You can copy  and paste the variables from the bottom of the sheet. For my example, the merge variable needs to be: “${"What would you like to tweet about today? "}”  The tweet is ready to be sent when a teacher puts a “1” in the “moderated” column. Now, all the teacher needs to do is “perform the merge” in the Form Mule menu and the moderated student exit tickets get emailed to the special gmail account.

Finally, set up an IFTTT recipe that forwards the body of any email sent to the gmail account to the class Twitter feed. This recipe was successful for me. And there you have it - a Google form that can collect exit tickets from your students, and tweet them to the class feed when you moderate them. Let me know if you make this better, or use it in your classroom!