Yesterday, I followed #edCampSWO and #edCampLDN from afar, and wrote about the experience. Here are some of the key pieces for easy access:
The Interactive TAG clouds & Twitter archive
The shared gDoc Notes
Forms to Gather Follow-Up Blog posts
Addendum: What I Learned TODAY!
I will confess I spent about an hour last night trying to get the #edCampSWO and #edCampLDN Blog Post Google Spreadsheets (above) to display something other than the raw responses from the Forms. I did manage to find posts on the web referencing the HYPERLINK and CONCATENATE functions with the intention of combining the Title and the URL cells from the Form Data into a single cell which would display the Title but link to the URL. I didn’t have any success, as the formula continually reverted to just the Title without the link. I similarly tried to create an active link to each authors’ Twitter profile and achieved the same, non-clickable result. I also wanted to remove the Timestamp column — automatically inserted by the Form, but not really needed in the display. It would have been a simple five minute task to simple hand-code the information into the post — but I wanted it to dynamically update as new posts appear via the submission forms.
This morning, as I received notification that it was my turn in my Words with Friends game with my #ds106 colleague and friend Alan Levine (@cogdog), I sent him a chat message asking if he had any experience in fiddling with Google forms to display properly. His reply referenced “Google being picky about wanting double quotes on strings” and “Try doing the formulas on a second sheet. It doesnt like messing with form response.” I shared the spreadsheet with him, and in short order, he had a clickable @Twittername link. I had to fiddle with the third column formula to get it to display the blog Title rather than the URL, but it was easy to get it to work following the Twitter example.
Note that the now-published columns come from a new sheet “Blog Posts” and so the cell references below are back to the original sheet “Form Responses.” To leave out the Timestamp column (or any other unwanted column), simply don’t include it on the now-published sheet.
For reference, the following formula displays the Title from cell C2 but links to the URL in cell D2, provided that there is data in that row (determined by an entry in cell B2, which contains the author’s name):
=if(‘Form Responses’!B2=””;””;(HYPERLINK(‘Form Responses’!D2, ‘Form Responses’!C2)))
The formula to concatenate the users’ Twitter handle onto the base Twitter URL and display it as a link is:
=if(‘Form Responses’!B2=””;””;HYPERLINK(CONCAT(“http://twitter.com/”,’Form Responses’!E2), ‘Form Responses’!E2))
Thanks, Alan! The learning continues! What will YOU learn today?