Technology Blog

Automated Google Appointment Calendar Follow-Up Emails

Do you use Google Appointment Slots and wish you could have an automated thank you message with a survey link included each day? Well, you can!

A couple years ago at #ISTE, one of the EdTech Coaches mentioned using the Google Appointment Calendar feature in Google Apps for Ed. It's really easy to setup and gives you some decent customization options. You create the time-periods and then the appointment length. If you want more details on this, checkout the Google Product Forum for it. This system has worked great for me as a coach. My teachers can click a link, book me when they want me to be there, and I come to them for whatever they need.

I wanted to get a good way to track the effectiveness of these meetings, so I created a Google Form that asked my teachers to rate their satisfaction with the support they received. I sat down each day at the end of the day and sent the emails one at a time. Tedious and frustrating.

After searching for what seemed like an obvious option (automated follow-up emails), I found nothing that accomplished this goal, so I set about creating my own.

Using a combination of Google Sheets and VBS Scripts, I made a solution that works, and now, I am sharing it with you.

Following the steps below will create an automated email sent each day to anyone who booked an appointment with you. It will also check the submitted responses of the feedback form, and if they don't complete the survey within 3 days, sends them a reminder.

  1. Make a copy of this Google Sheet (will prompt to make a copy automatically): - Calendar Emailer
  2. Create a Google Form that asks the questions you want to ask. If you want an example of what I'm using, checkout this one. Also, make sure this form is set to collect email responses.
  3. Create the responses Google Sheet by clicking the green square in the top right corner from Google Forms Responses tab.
  4. In the - Calendar Emailer Sheet, paste the link for the Google Form (the send link you would give for someone to take the form) in cells B9 and B15.
  5. Paste the URL for the Responses sheet for the form in cell B17
  6. Adjust the other fields of this first page so that the email includes your name, the message you want sent, etc. DO NOT merge cells, add cells, delete cells, or move any cells around!!
  7. Go to the "Form Responses" sheet and click on cell A1, then click "Authorize"
  8. Click on Tools -> Script Editor
  9. Click the "Triggers" icon 
  10. Create a trigger for "import_calendar" as Daily and roughly at the end of your workday.
  11. Create a trigger for "sendEmails" as Daily and an hour after the previous trigger.
  12. Should look like this when it is setup:
  13. Authorize the app for the requested permissions.

If you have any issues at all with this, please let me know!! Also, if these steps are too cut and dry for you, feel free to use this video walkthrough I made.

Future of EdTech Coaches

Last night I participated in the #EdTechChat on Twitter, in which the topic was Ed Tech Coaches. As a full-time Digital Instructional Coach, the topic obviously intrigued me.

Given the content of the chats, I thought it would be a good idea to talk about it a little on here. Huge thanks to Susan Bearden for moderating and asking the questions.

My role has changed significantly in the last two years of this process. When I started out, it was entirely about running in a thousand directions at once, trying to get a one-to-one program off the ground. In any given day, I was fixing dead access points, troubleshooting student account issues, explaining where the power button was, training students to do tech support, handing out 4000 laptops, one-on-one training with teachers, training teacher groups, recording voice-over presentations to send out, sending and answering hundreds of emails a day on any or all of these. And then the next day I would do it again. This made perfect sense, because it was essential to be everywhere and make sure my staff got whatever support it was they needed.

This year though, as we are finishing year two, my model has completely changed. I now work on an appointment basis, where teachers request for me to come to them for whatever they need. I can then spend an entire period walking them through their issues and what they need. It personalizes the experience for all our teachers, and makes my day significantly more predictable.

My first response to this was that twitter chats like #edtechchat and #edchat are a great way to interact and build a PLN. Last night was my first time participating in #edtechchat, and I was surprised by how many people were Instructional Technology Coaches or Technology Integration Coaches, etc. (we really need a more universal title, but then again, this is education, so why would we do that?).

I, of course, also felt compelled to include that podcasts are a great way to build ideas. I recommended @mrnesi House of #EdTech to the group. As a heads up, episode 3 of my podcast will also be very EdTech centric.

Overwhelmingly, the answer to this question seemed to be trust. I said basically that we need admin to get out of our way as tech coaches and let us do our thing. Sometimes I am amazed that they put us in these positions, presumably because we are experts in technology instruction, and then we are questioned at every step. Give us the space and trust it took to hire us to actually let us do our job.

The other thing I suggested is that it would be nice if administrators had a little more teeth in enforcing expectations, especially with heavy resistors. I will say, this could easily be a double-edged sword, but it would be nice if those who completely, and stubbornly, refuse to even try technology integration had some kind of administrative follow-up.

This is one space where I feel like I'm doing a pretty good job. I have created a Google Appointment Calendar, with available slots by period. All my teachers have to do is go to the link, and click the period they want me. They add their room number and what they want to talk to me about and I come to them at that time. I then built a Google Sheet and VBScript combo that imports the calendar each day, and after each appointment, emails them a follow-up email to say "Thanks, please take this survey and tell me how I did." It even goes further, and since the feedback survey is a Google Form, it imports those results and checks those against those that have been sent the survey. If they haven't filled out the survey in 4 days, it sends a follow-up email reminding them. In the future, I will probably share a tutorial on how to make this happen for yourself too.

As change agents. I like to believe that having an Ed Tech coach gives teachers the confidence to try something new, knowing they have the support to make it work. I'm not 100% sure I accomplish this goal every time, but a boy can dream, no?

This one has been on my mind for a while. What I honestly believe is that we should be actively working ourselves out of a job. Early on, we can make it possible to try new things and teach tech. As a one-to-one initiative reaches maturity though, we need to stop focusing on tools, and instead focus on instruction. For me, this means transitioning into a CRT role, in which instructional support and PD always assumes everyone is using and knows how to use technology. In order to fully immerse our students, this is the essential next step.

ISTE2016 Summary - Problem-Based Learning Extravaganza!

ISTE2016 is finally here, and I couldn't be more excited! In the beautiful city of Denver, some of the worlds greatest teachers have come together to talk pedagogy and technology. It doesn't get any more mind blowing than this.

Ok, geek out session over (for now). The following are my impressions and some paraphrased quotes (probably not word-for-word, but the point of the quote hasn't changed).

My first session of the show is Problem-Based Learning Extravaganza!, a panel presentation on PBL. The panel is moderated by Adam Bellow and consists of Kelly Hines, Katrina Keene, and Nicholas Provenzano (The Nerdy Teacher). If I'm being honest, I'm mostly here to listen to Nicholas speak, because I've followed him for years on twitter, listened to his podcast, and find his knowledge and presentation of it eye opening.

The session opened with a discussion of what PBL is, and ultimately their description was that it is a way of pushing students to solve problems, and build something, to show their understanding of concepts. Provenzano pointed out that it takes time to build the trust with your students to make this possible, adding that it isn't just that you have to trust the students, but the students have to trust you that you know what you're doing.

After Provenzano explained his first attempt at PBL and how he got 30 of the same project back from kids and he was so excited, he said that now he looks back and is so sad by his own excitement, to the point that he "felt like I needed to write apology letters to all those kids." Adam Bellow brought up a quote from a book (didn't catch the author), he said "If you give a project and get 30 of the same thing back, you didn't give a project, you gave a recipe." The idea is that you need to give open possibilities for kids to actually solve problems, not give them a step-by-step guide. All students think differently, and won't all solve problems the same way.

Katrina Keene spoke about her passion for connected toys and coding in her class. She was an early adopter with Sphero and used them as a long-term project with the robotic balls and having students learn to code them to make their way through obstacles. One of the things I really liked about her discussion was that she essentially showed them the toys and said "These are robots that you can control with coding, but I'm not going to tell you about that, go out and research it." From there, she would build on the concepts one at a time. Once she felt they understood the coding, she said go find things to run the Sphero through, and figure out how to do it. Then she added additional tasks to build upon complexity over time.

Kelly Hines: "We often talk about how engagement leads to achievement, but I'm a firm believer that engagement leads to attendance, which is what leads to achievement. You should be that class that the last day, after awards when they really don't need to be there, but you still have 18 of your 20 students there."

Nicholas Provenzano, in response to a question from someone about to try PBL for the first time, and how she should start: "The biggest thing for me is to tell your students 'I've never done this before, and I'm probably going to screw this up.' That admission that you're not perfect will excite your kids. They aren't used to that from their teachers."

Kelly Hines: "Project Based doesn't have to mean Production Based. It doesn't have to be over weeks or months or require a trip to Lowes. It's about that subtle shift that can just mean student choice or learning through trying. That can be done in weeks or in one day."

Katrina Keene, in response to question about whether students create the project rubric as a class or each individually. "This year in my class, I did everything. Sometimes I would have them do it as a whole group, other times I would have them each do it individually. Using Google Classroom ,or whatever LMS you use, I just group them up and it allows me to let them work individually."

Nicholas Provenzano, in response to a question from someone about teachers within a PLC having difficulty trying PBL because others in the PLC aren't on board. "What I would suggest is a little magic word called 'pilot', or if we want to get techy, 'beta.' You can go to admin and say 'I really want to try to pilot a more project based curriculum with this course.' Admin loves pilots, they like to brag about 'I have a teacher piloting this, or that.' And, because of that other word that we all love 'data' we need lots of it. So you can run a pilot for 3 years to collect data, and with mobility of administration in most places, by the time the three years is up, new admin are there and you can just say 'Oh this is the way we do it here. It's just what we do.' I've been running a pilot in my class for like 6 years now."

Thank you so much to all the presenters. This was an AMAZING session to start my week at #ISTE with! I'm so pumped to be here now.

Organizing Administration Using OneNote Staff Notebook*

At my school, we have launched into OneNote as a school platform with both feet. At this point, just 9 weeks into our one-to-one program, we have roughly 80% of our teachers using Classroom Notebooks for their day-to-day activities, and 97% of our staff using the devices every period of every day.

We believe that the biggest reason for this success is the use of Staff Notebook at the administrative level. As an admin team, starting from the principal down, we believed that the only way to get our teachers to use Classroom Notebook was to model proper use and notebook setup. To do that, we started thinking about the systems we previously had in place to deliver and receive information from our teachers. Our school heavily depended on Sharepoint, and the tools there were extremely useful to us. Looking at the way our workflow went with Sharepoint, and considering the power of Staff Notebooks, we broke our system into a total of 3 primary administrative notebooks.

School-Wide Staff Notebook - Team WARRIORS

Our primary purpose for Sharepoint was for distribution of documents, forms, and lists to the staff. This included things like Pre-Planning Schedules, Professional Development Calendars, School Maps, etc. To accomplish this goal, we developed a school-wide staff notebook, called Team WARRIORS (our school mascot). We removed the individual teacher tabs and just use the Content Library, Collaboration Space, and Welcome area. Here is a look at some of the key sections:

The Welcome Page for our school-wide Staff Notebook.
The Welcome Page for our school-wide Staff Notebook.

Going through the old Sharepoint site, we realized that Staff Notebook gives us the ability to organize much better than Sharepoint. In a Staff Notebook, we have lots of organization options. By breaking our content into the various sections within the Content Library, information is much easier to find for our teachers. Here is an example of the Discipline section:

As we entered further into the school year, we found that Team WARRIOR became the perfect place to store tutorials and how-to's for the technology that we use in our classrooms. There are some major advantages to this over Sharepoint. For one, it is more visually appealing. Instead of seeing lists of documents the teacher can download, then view, they can directly click on the page and see it immediately. It also allows us to make more dynamic content. If a software application gets an update, and the walkthrough I made previously isn't exactly right any more, I make a new version and replace it on the OneNote. While this can be done on Sharepoint as well, the advantage here is that the new version automatically replaces the old one. When teachers download a document off of Sharepoint, they get their own personal copy of that document. OneNote automatically downloads the most recent version, ensuring that all teachers have the most recent information.

Team Warrior - Technology Tab
The Technology Section of Team WARRIORS

This Staff Notebook has been hugely beneficial to our staff for finding needed documents, that in the past, were difficult to find on our Sharepoint site. Even more importantly, it forces our staff to actually use OneNote, and helps them make connections between what a Class Notebook can do and how they might actually use it in their classroom.

Administration Staff Notebbok - Leadership Team

The Leadership Team Staff Notebook was created by our principal, and the staff added were the Assistant Principals, Deans, and Support Personnel who make-up the leadership team. This Staff Notebook helps the principal disseminate information to the team such as the weekly newsletters from our Deputy Superintendent, agendas for our weekly leadership team meetings, and the supervision schedule and maps. In addition, it maintains the classroom teacher observation schedule, ensuring that all administrators know who they should be performing classroom observations on each week. It also allows our principal to monitor that the observations are complete.

The best use of the Leadership Team notebook is the use of the Collaboration Space for the Leadership Team Meeting Agendas. Here, our principal supplies the agenda and everyone in the meeting can follow along as he goes. In addition, we can make notes for the rest of the team to see, and comments can be added with questions or more details as we go through the meeting. This process has prevented interrupting others from talking, or stopping the flow of the meeting, to get clarification.

Evaluating Administrator Notebooks - Team AP

The most useful, and beneficial, staff notebooks are the ones setup by each of the evaluating administrators at our school. These notebooks add all of that administrator's direct reports (primarily broken into the different subject area departments). Within each of these are the specific documents that administrator wants shared to those teachers and support staff that they oversee. In each of the teachers' own sections, they submit their syllabus at the beginning of the year, their weekly lesson plans, and their quarterly grade verification.

In addition, the Collaboration Space of this notebook has been beneficial for departments, and grade levels within departments, to collaborate. The Reading Department, for example, has taken to using this for all of their documents. Pay particular attention to the navigation panel and organization structure of the screenshot below.


There are two major advantages to our use of Staff Notebook to run our school. The first is efficiency. This system is so much simpler to use than a Sharepoint site. It allows each administrator to directly control how their system is setup, and each administrator only sees their direct reports instead of everyone. Teachers also have a much easier time submitting plans to their administrator and accessing necessary information through the Staff Notebooks than they did on Sharepoint. The implementation of this system has saved our administrative team and teachers countless hours in the 9 weeks since we implemented it.

The other major advantage, and in my eyes the bigger of the two, is that it gives our teachers the chance to use OneNote. More accurately maybe, it forces our teachers to use OneNote, so that when they begin to use it in their classroom, they know how to use it already. As classroom use of OneNote, in the form of a Class Notebook, is an expectation of all of our teachers, this is essential. OneNote is an extremely powerful tool, and the Class Notebook creator makes it an amazing tool for instruction. However, its greatest strength is also its greatest weakness. OneNote is loaded with features and capabilities, but because of that, it appears daunting. The sheer number of navigation panels and toolbars can be intimidating even for powerusers. However, once you start to use it, once you get a conceptual vision of the functions of the various components, it is not actually complicated to use. Forcing our staff to use Staff Notebook showed them that it isn't actually as complicated as it appears.

If you have any questions about our implementation, feel free to comment below.

The content of this post expresses the views of myself, and only myself. I in no way intend to represent the opinions or perspectives of the school, district, or state in which I work.

ISTE2015 Wrap-Up

I'm home, and mostly recovered, from ISTE. Feels like a good time to take a minute and write about my thoughts on the conference. The simple summary is that I really enjoyed the conference and think it was totally worth while. That isn't to say that the conference was perfect by any stretch, or even what I really expected.

I went to ISTE feeling like I would leave with some new knowledge, some new idea that would transform my teaching. That didn't happen. One of the biggest reasons for that is the sessions themselves were misleading. Many of the sessions I attending had really fascinating titles like "Create a tech startup at your school" or "Managing 1-to-1 as a Principal or Technology Specialist." I went to these with so much hope that I would walk away a better teacher. Instead, I walked away from the tech startup one with ideas for how to make kids sell school surplus on ebay and how to pick the right device for a 1-to-1 school. I was talking to a fellow teacher at ISTE and I made the comment "It's like session title link bait." Maybe the people running these sessions had grandiose plans when they named their session and just din't deliver (which I definitely feel is true for the managing 1-to-1 session). Maybe it was intentionally misleading, or the host didn't actually understand the terms they were using. Either way, I feel that the majority of the sessions were almost completely useless.

What I did walk away from ISTE with is a list of people that know so much more than me. I met with people that I have talked to on twitter before (like Sherry Gick who is awesome, and I totally fanboyed over), and that was super cool. But what else I found were people who were already doing the crazy things I was thinking about doing,and doing them much better than I had planned. Especially in the Ignite sessions, I saw multiple teaches who have student-run tech support, and idea that I am already in process of implementing at our school. While their 5 minute talk didn't give me every detail, it did show me that they are brilliant, and it gave me their twitter accounts so that I can call on them when I need help.

The biggest thing I got out of ISTE though was the team building. I went to this event with my principal and 4 other teachers on the Digital Curriculum Teacher Leaders team. These people, without exception, are awesome. We had so much fun hanging out after the conference each day, and in different meetings we all attended together. I have worried on occasion about how I was going to be able to manage such a massive campus (over 4000 students and over 200 staff) during a 1-to-1 transition year. I no longer have any concerns about that. I know that my school is setup for success because of the team we have built around this transition.

My biggest complaint, by far, was the venue for the 3rd round of Ignite talks at ISTE. I mentioned this on my twitter account, and included this video:


They put this round of Ignite talks on the stage at the back of the show floor. This means that there were people talking loudly all along, not nearly enough seating (I was on the floor in the back), and at one point, an announcement was made over the PA system while someone was presenting. Ignite talks are hard enough, auto forwarding slides every 15 seconds, definitive timing, and generally less experienced presenters. This venue was simply not fair for the presenters. I learned a ton from the 2nd round of Ignite talks, but got completely cheated on the 3rd round. It was just too hard to hear them. Huge mistake by ISTE.

Still, overall ISTE was a huge success for me. I look forward to making it back next year and meeting more of the people I've met. I actually really want to present next year at ISTE2016...

Leave a Reply