Assisting My Colleagues with a Holiday Inspired Tech Exchange…

If you have read previous posts on here, I tend to get asked questions like, “Would it be possible to…” Well two of my colleagues, Kim Lowden & Jess Verrault, are putting together a pretty cool way to celebrate the holidays with a ‘tech-ified’ gift exchange.

They will have teachers fill out a Google Form where they submit a digital resource of some type & request one in return. Then they will go into the Sheet results and try to match up, as best as possible, each staff member with someone who submitted a resource they are looking to receive. Utilizing a couple of formulas & add-ons (I’ll describe them in more detail) a Google Doc will be created which will be delivered to the staff member’s inbox. In the Doc will be the link to the resource, a description of how it is used (with students, if possible) and a means to send a pre-formatted “Thank You Card” email back to the giver. Below I will embed all of the Google-y things (Form, Sheet, & created Doc from the Template with <<tags>>)

Formulas & Add-ons, Oh My…

I needed a means to create a pre-formatted link to an email address. I had to insert a few columns into the Sheet:

  • Column B: =IF(A1=“”,“”,“mailto:”)
  • Column D: =IF(A2=“”,“”,“?subject=Thank%20You&body=Dear%20Secret%20Snowman,”)
  • Column E:  =CONCATENATE(B2:D2)

This will create all the text needed to take the ‘response recorded’ email address in Column C and map it out to create a hyperlink ‘mailto’ trigger with a specific subject and body.

Next, they wanted it to be as personalized as possible using the correct pronoun in the Google Doc. I added a column to the Sheet which uses Data Validation to create a dropdown list for “he” or “she”. Kim or Jess will have to select the pronoun when they are matching up colleagues so the correct pronoun will appear in place of the <<tag>> in the template…

Finally, another column was added where Kim or Jess could input the email address for the “gift” recipient.

Now as for some Add-ons used:

  • copyDown – will insert each formula when a Form is submitted
  • autoCrat – will insert data from the Sheet into <<tag>>ed text within a Google Doc template

Have you participated in a technology holiday gift exchange? I would love to read about your experiences below.

‘Force a “Comments version” Copy’ Integration Activity…

Google recently announced the ability to make a copy of a Google Doc w/ all the comments &/or suggestions from the original document replicated. (You can read about it here)

By the time of this posting, after November 29th 2017, all users have the feature and can make a copy of a document with the comments and suggestions carrying over.

Eric Curts over at Control Alt Achieve, posted a means to modify the ‘Force a Copy’ trick in conjunction with the copying of comments. I’m not going to go into too much detail (mainly because Eric does a fine job explaining the process w/ screenshots) the main gist is…

  • Change the Doc w/ prepopulated comments to ‘Anyone with the link can comment’
  • Go to the shareable link, NOT the URL of the Doc, and copy it.
  • Change the /edit?usp=sharing to /copy?copyComments=true
  • Give this new link to anyone you want to ‘force a copy’ of a document

I created an example activity (linked below) which showcases how this could be utilized in the classroom. The document has the Gettysburg Address with questions posted as comments. Students could then reply to the comments with their answers. The idea would be to manage this through Google Classroom where I create an assignment with nothing attached. The ‘force a copy’ link will be included in the directions of the assignment. When completed the student would attach their copy of the Doc to the assignment, then turn in.

I also feature another one of my favorite tools within this activity, an extension called Talk & Comment. This tool allows a user to embed voice recordings within the comments of the Doc. Plus, a bonus, notice some of the comments have bold text? I learned about that trick from Kim Pollishuke over at Inquire & Inspire. In her May 2015 post, she highlights some text tricks to create bold, italics, strikethrough, or all three.

  • *text*   → text
  • -text-   → text
  • _text_   → text
  • _-*text*-_   → text

Google Doc with Comments, click the image to ‘force a copy w/ comments’

How could you use this technique with your students? I would love to read about your ideas in the comments below.

Building Upon an Idea…

I recently read a blog post by Eric Curts at Control Alt Achieve about how to have mathematical notation in a Google Form. The resource he shared is a free web tool, Online LaTeX Equation Editor, which is similar to EquatIO. This tool provides several options for creating symbols or math notations as images that you can either download, get HTML coding or a URL address.

***UPDATE: About two weeks after publishing this post, EquatIO announced that they are making their full platform free for teachers. Find out more about it here.***

I started to think about how this would be a great example idea for my math department teachers. Below is an image of the Form (included on the Form is a file upload question which prevents the Form from being embedded, click on the image to see the Form). Underneath the Form, I have included a video which highlights all the components I setup for the Form:

  • response validation
  • reference image for a question
  • file upload question
  • quiz features
  • incorrect response feedback w/ link
Click on the image of the Form to view it.

Click on the image of the Form to view it.

Would love to know your thoughts; please leave me a comment below.

Dabbling in a Little Scripting out of Necessity…

This past October was a PD day for my school district. Our afternoon sessions at the middle schools and high schools were designed around an unconference model. I was asked to create the digital board for the middle school and eventually asked to do the same for the high school. Now in a previous post, you may remember, I created an EdCamp session board for Garden State utilizing a script written by my supervisor, Allan Johnson.

This time around we wanted the session Docs to be formatted with some text already in place. Well time was short so we did what anyone would do, open each Doc and copy/paste the formatted text. After doing that for 48 Docs times 2 locations, I knew there had to be a better way.

So it was time to look back at the script which creates all the Docs for the session board…

Now I don’t know how to code or write scripts, but I did some digging around on Google’s Apps Script Reference Resources and after many, many, many (so many attempts that I think my Google Drive hates me for creating all those Docs) I was finally able to add to the original script to create pre-formatted Docs. Below are two embedded items. First, is a ThingLink tagged image of the portion of the script I wrote, tagged to the resources I discovered to create it. The second item is a created Doc (the link to the board has been disabled).

What have you learned/taught yourself lately? I would love to learn with you in the comments below.

A Department Meeting Success!

Ever have a meeting where you feel the group vibe is a… can’t say World Series win, maybe a Pennant Series win? People are leaving inspired with new ideas and come up to verbally share them with you; that’s what I experienced recently.

Let me explain and I’m hoping this doesn’t come off as a ‘tooting his own horn‘ post. In my position as a teacher resource specialist for technology, I not only support a middle school (6th-8th) but I also support the Math (6th-12th) & Science (6th-12th) departments. I do this with one of my counterparts at the high school, Jessica Verrault. We were approached at the beginning of this school year by the Math Supervisor, Andrea Bean, if we would be interested in facilitating a monthly meeting with the eighth and ninth grade teachers focusing on student engagement in our 1:1 chromebook environment.

“Challenge accepted!”

So Jess and I would design and then meet with Andrea to plan out each month’s department meeting. Our first one was the last week in September. We picked the focus on ‘differentiation techniques’ using a variety of tools.

I’m of the philosophy that you need to experience something before learning how to do it. So we designed a couple of activities where the attendees would be in the role of students going through a lesson and then we would backtrack through the lesson explaining how we intentionally set it up. We went through the “lesson” from start to finish, then explained in reverse order why we did what we did.

We began with three different Google Doc activities (pictured below & clickable with “Force a Copy”). We ‘assigned’ them through our department Google Classroom as an assignment where it made a copy of the Google Doc for each student. Embedded in the Google Doc was a Google Drawing of a possible problem solving strategy. 

We followed this activity up by showcasing how to differentiate resources quickly to students. We utilize a tool, GoGuardian, which assists teachers in Chromebook management. One of the features, the ability to give commands, assists teachers in ‘Opening a Tab’ on student devices. So we shared each of these blog posts:

Selected teachers had a tab open up to one of the above posts. They weren’t told they were going to receive different posts. After a quick skimming of the article, we had them in groups discuss. That’s when many of them realized they received different resources.

We wrapped up our “lesson” with a Verso activity. If you aren’t familiar with Verso, take a gander at this previous post from me. We had the teachers respond to,

That was our lesson! We proceeded to work backwards explaining our intentions. First, Verso was used so we could take advantage of the feature to group responses. As the classroom teacher, we would want to see who would be in our groups for tomorrow’s lesson, to determine who would get different activities/resources.

Then we showed the teachers how to push out the command in GoGuardian to open a tab:

We showed how our Google Doc with an embedded Google Drawing could offer a layer of differentiation by including the drawing or not, all depending on the needs/abilities of the student(s). Our main differentiation strategy demonstrated in our lesson was the ability to assign to individual students in Google Classroom instead of assigning to all:

So there it is, the entire department meeting offering strategies facilitated by technology to differentiate for our students in a math classroom. Andrea (Math Supervisor) gave us some great feedback, praise, and even suggested presenting this at an upcoming NJ Math Teachers conference (might be doing that!). The one thing to keep in mind for next time, we should have explained that we would be going through this lesson at a quick pace. Some of the teachers felt anxiety about not completing the Google Doc math problem or completely reading the blog post in the time allotted. We should have stated, “To respect your time, we will be hurrying through this demo lesson so we can then explain and demonstrate how we setup what you are about to experience.”

What are your thoughts about this department meeting description? I would love to read them in the comments below.

New School Year; New Challenge

I was asked by one of my district’s Special Ed Supervisors to assist with the creation of a means for managing the school’s lost & found. We have a life skills room where the students are going to utilize the lost & found items to practice how to wash (dish washer or laundry) then catalog the items for return to the proper owner.

We came up with the idea of using a Google Form where the students would be able to select some basic answers as well as upload an image of the item.

This data would collect on a Google Sheet where I have setup a bunch of formulas (I’ll break this down later in the post) and a couple of tabs.

The Google Sheet would be embedded on a Google Site that parents and students could go to find their missing item(s). There they would also find the email address & procedure for notifying the life skills students.

Screenshot of CMS Lost & Found Website

The initial tab in the Google Sheet (FormResponses1[hidden from view]) had several formulas established & maintained using the Google Sheets Add-on, CopyDown. The following are the formulas established:

  • Item number – automatically created item number established when a form is submitted.
    • =ROW(B1)
  • Item number hyperlinked – pulling the item number generated in the previous column to now hyperlink as a mail-to with formatted subject and body. This was an added challenge asked by one of my colleagues, “Wouldn’t it be neat if…” [Kim Lowden]. It actually required me to learn a new formula, ‘concatenate’, from my supervisor, Allan Johnson.
    • =HYPERLINK(CONCATENATE(“,%0A%0AI%20have%20identified%20an%20item%20listed%20on%20your%20site%20which%20belongs%20to%20us.%0A%0AMy%20child’s%20name:%20%0AHomeroom:%20%0AGrade:%20%0AItem%20number:%20%0A%0ASincerely,%0A&subject=CMS%20Lost%20and%20Found%20Item%20Number:%20”,B2),B2)
  • Google Image ID – when the students submit the image as a file in the Google Form, a URL is generated. This URL will not allow the image to be displayed (formula to come). The ID number will be needed for a later formula.
    • =right(F2,LEN(F2)33)
  • New Image URL – using the Google Image ID number collected from a previous column and combining it with a URL recognized for generating an image.
    • =“” & G2
  • Image – displaying the actual image in the cell of the Sheet (pulling from url in New Image URL column).
    • =IMAGE(H2,4,300,300)

Now the biggest problem that I had was with displaying an image at a particular size. Even though the formula for the image was set to 300×300 pixels, when a new row is inserted with Form data, the cell size overwrites any heights to the default. I needed to create a whole new tab (Lost&Found) in the Sheet and hide the FormResponses1 tab. I formatted the first 1,000 cells to the size I want then utilized a formula (=FormResponses1!C3) to pull in this information for each column I wanted to display on the website.

***Update*** Less than a week after posting, Alice Keeler released a post about a script which would change the row height after a Form submit.

The last tab I created was to help with the organizing of the items for the students. I made an ‘Item # / QR Code’ tab. In this sheet, I pull in the generated item number and the image from the previous tab. I create a column where they could type in the student’s name and homeroom where the item should be returned. Finally, I have a QR Code generated based on the item number which the students can print out and attach to the item. The students can scan it when they need to find the correct item to return. I used the formula (=image(“×300&cht=qr&chl=”&A2)) to auto-generate a QR Code.

Now whenever an item is claimed, they can go into the Sheet and hide the row for that item in the tabs: ‘Lost&Found’ and ‘Item # / QR Code’.

***Update #2*** We are no longer using email as the means to “claim” an item. I removed the hyperlink formula from item numbers because we are using an embedded Google Form below the spreadsheet on the site. This way the students have to check the Sheet of submissions to see who, what, and when. We have data validation setup to make sure submitters are inputting the item number and an email address. I have formMule setup to send a “receipt” to the submitter letting them know that we received their form submission. This has made the process easier to manage.

I would love to know your thoughts on this challenging workflow; please leave me a comment below.