One of the key attractions to starting at The Sheffield College was that the staff development team had already begun the journey towards Google. Since August 2015, Chris and Helen have made a truly inspiring and incredibly rapid progression to transform processes from paper to the cloud. Here, Helen shares her journey through Google:

Even before I joined the Staff Development department at The Sheffield College, we had used paper forms for applications. The filing was a nightmare and often built up, (it was never a favourite job of mine so I often left it until I had nothing else to do) and finding relevant information could take a while. We had been looking at a way to make our department paperless for a couple of years; and about two years ago we started off with the forms being made into writeable PDFs. That only worked so far as people still insisted on printing the forms off before submitting them and then the interim manager at the time mentioned Google Docs as a possible way to streamline our processes, but as with all things, we were reluctant to change things too much. This went on for at least a year and then all of a sudden, it happened…

We had a new Assistant Principal, Graeme Hathaway, start at the College who advocated the use of Google Docs as a way to work collaboratively. I will admit to being sceptical; even when our interim manager initially broached the subject of using Google, the very idea of more than one person being in the same document at the same time just didn’t sit right with me. But then I went on the training offered by the Assistant Principal and my eyes were opened to the possibilities both professionally and personally.

At a meeting with another department, I was asked to talk about the training session I had attended as at the time, the College were looking to convert to Google. I explained it as best as I could, but some people present could not get their heads around the fact that you did not have to save, it did it automatically and like me, they couldn’t believe more than one person could be in the same document, amending it at the same time. Luckily for me, my instructor in all things Google happened to be invited to the meeting and he arrived just as I was trying to explain (badly I might add) what he had taught me and he took over, providing a ‘live’ example as evidence.

Food for thought indeed.

When time allowed, I began to play around in Google, trying to recreate what I was taught. First, it was kept simple; the occasional letter, a form to be completed, that sort of thing. I even shared minutes I had taken at a meeting using Google Docs; but for me, the real turning point happened six months after I did the initial training; our new temporary Manager was the same person who trained me on Google Docs. With his (very patient) guidance, we slowly created a new form through Google to help simplify the running of our department. Not an easy task I can tell you. People were resisting and it took a while for staff to understand that this was how we did things now.

Now, in Staff Development we have several different forms for each type of application so we needed to create a single form that would cover all questions asked by all our previous forms. This was not an easy task as several questions were duplicated across the paper-based forms so we had to decide what to keep and what to bin. After playing around with the form for a while, (read several days) I was able to make it so depending on what option the applicant chose, it only showed them the relevant sections of the form. The only problem was that it created a response spreadsheet that seemed several miles long.

Now, we had all the information in one place, however whenever there was an enquiry about an application, it could take us just as long to find what we’re looking for as it did if we still used paper. By the time our new Manager was in post, we were almost bald with tearing our hair out. Not a good look for us at all. It was agreed that the system needed streamlining. Having all responses linked to one spreadsheet was great, but in order to keep the applications separate, we needed to separate the forms so that’s just what our new Manager did.

New forms were created across the board: five different application forms, line manager’s authorisation form and of course, the training evaluation form. These are now all linked to one master sheet where we can find what we’re looking for with ease. The new forms were put on the intranet and an email  went out to all staff, informing them of the change; links to the page were added to our Google plus community and also added to our email signatures and slowly but surely, staff embraced the use of the new forms. Due to the success of using forms for applications and evaluations; we looked at other ways we could use Google Docs, Sheets and Forms.

A big part of what our department does is organise training events for staff, culminating in the Development Day at the end of the summer term and in the past we have had issues with people trying to book on something that is full and we were looking for a way to combat this when out of the blue, Kieran, a colleague of ours sent me a tutorial video for an add on for Google forms called ‘Choice Eliminator’ so with some curiosity, I plugged my headphones into my PC and turned up the volume. Without realising it, while watching this five and a half minute video, I had found something that we could potentially use for our Development Day events.

Thank you Kieran Briggs.

Image available from here

So, here’s how we’ve done things in the past; on the run up to the Development Day, we’d compile a list of training with a brief description and put it on a spreadsheet, this would then be sent out to all staff who could peruse the list and choose what they wanted to attend. Staff would then ring or email us to request a place on their chosen session and if places were available, we would add their name to the attendance list. We would then email them back confirming their place (or not if the session was full).

If they got their chosen session, then the average time spent on each person is approximately ten minutes. However if their choice was full, we would spend more time on that one person. We could often speak to the same person several times a day and I would often get at least one person asking me ‘Well what do you think is appropriate for me to go on?’ Bluntly put, I wouldn’t have a clue, and I would tell them that, politely of course. Or my personal favourite, ‘put me on whatever is available.’ We would often use this response to populate the less than popular events.

At least using the Google form with choice eliminator, the member of staff completing the form will know if there are places available as once a session is fully booked, it is removed from the list. No more long winded phone calls or emails back and forth. I am actually getting excited about this year’s Development day now, especially if this works!

Image available from here

But wait a moment…

While watching the tutorial video about Choice Eliminator, another add-on was mentioned that made my ears prick up; an add-on that can automatically send an email as part of a mail merge to the person who submitted the form (or anyone else you choose). The gears set off going in my mind at the possibilities of using this in conjunction with the choice eliminator for the development day booking system. And just what is this wonderful add-on I hear you cry? Autocrat. Believe me when I tell you to speak clearly when telling people the name of the add-on; it’s true what they say, people hear what they want to hear.

So I Googled Autocrat looking for tutorial videos, found the one for Autocrat and watched it; then I watched it again, and then again. I can remember thinking, if this worked, it would save us a lot of time overall, and not just for Development days. As I said earlier, in previous years we could spend an awfully long time responding to emails, booking people on sessions, it was time consuming and it could be a mindless task. So I tested it out. I set up a mock form with random questions and used Choice Eliminator. That part was simple enough. Then it was time to implement Autocrat. I watched the video (again) and made sure I didn’t miss anything.

The document to merge was written, the merge folder was created and then the merge itself was set up. I watched the response sheet with eager eyes as I completed and submitted the form. I saw the response appear, followed closely by the merge links and then the email arrived. I sat at my desk, stunned. It had actually worked. I couldn’t believe what my eyes were telling me, so I did the only thing I could; I did it again, and again, and again. Each time I got an email confirming what the first email had; Autocrat had worked and like Choice Eliminator, it did exactly what it was supposed to.

Armed with this ‘new idea’ and emails I mentioned it to Chris my colleague. I had to explain it carefully and Chris had a lot of questions about it but ultimately, we both agreed it was something we could use if it worked. Then I mentioned it to Hannah, our Manager, who, like me, saw the potential in using these add-ons. It was agreed that we would ‘pilot’ the new booking system for a planned training day and so far it has been a success. I was able to set up the Autocrat again just as indicated on the tutorial video.

Image available from here

The problem was, with setting up the add-on as instructed; everyone who booked a place would receive an email inviting them to view the document which unfortunately meant I got an email requesting access. This was not what I wanted; I wanted these add-ons to save me time and allow me to focus on other projects. So I went back into the add-on and changed it so instead of a ‘view only Google doc’ it would send a PDF attachment instead. From that point on, people would get their confirmation of booking (yay!) and I would not get bombarded with additional requests. (double yay!)

Now, being new to the ways of using add-ons, I was looking at other ways we could use what we’ve got and how we could expand on it. One afternoon as I was leaving for the day, Hannah gave me a challenge; to send one response to the applicant and a second to a designated other person (i.e. their line manager), simultaneously. I accepted the challenge and went home to think about how to do it. I was cruising along the M1, Metallica blaring out of the stereo when the idea came to me. I mean really, it was so simple why hadn’t I thought of it before? It was definitely one of those head-slap moments. (Please remember I am totally new to this ‘using add-ons’ lark).

When I arrived at work the following morning I set up the second merge using the same format from the original, but changing the wording slightly. Several tests later and again, it had worked. I immediately emailed my findings to Hannah who wasn’t in that day and had to wait for when she was next in for her opinion. It was a thumbs up all round. The ideas on how we could apply this in future were going around in my head but ultimately, the idea of using it as a booking system for the development days was looking more and more promising.

The only bug we have at the moment is once an application form is submitted, we have to email a link to the applicant’s line manager. This is where I thought autocrat could come in handy again. The problem is we can’t use it on a spreadsheet with multiple tabs; you end up getting multiple emails as autocrat runs the merge across the entire spreadsheet and not just the single tab.

Before we could think about splitting the response sheets for the forms, I was asked to find a way to import data from one tab on a spreadsheet to another. Again, please remember I am still getting to grips with Google and how to use it effectively. So I looked online again but I got easily confused with the different functions needed and when I tried to implement it, I kept getting error messages. So I did the only thing I could, I asked my colleagues. Dave patiently took me through what I needed to do to be able to import data from one tab to another. I got it pretty much straight away, but the challenge came when I needed to import data from one sheet to another.

Image available from here

I knew the general formula as explained by Dave, and after looking at Google, I knew I had to add the ‘=IMPORTRANGE’ function with spreadsheet key and for the most part it worked; until I hit ‘enter.’ I spent an entire afternoon getting frustrated so I gave up for the day. This unfortunately continued the next day until I spoke to another colleague Simon. He had a look and he worked out that firstly I had a bracket in the wrong place, and then he told me to remove part of the formula as this would make it request access to the spreadsheet whereby I could add the rest of the formula. When I hit ‘enter’ and it worked, I was like a giddy school girl. I could not stop grinning and clapping my hands and when I added the formula to another cell and it worked again, I clapped my hands with a ‘yay!’

It’s the simple things that make me happy and this was no exception. I now had a way of tracking data from several different spreadsheets which in turn meant we could use Autocrat for all the future Staff Development applications. Once the correct documents were created, tests were run and everything seemed to go off just as it should. We just need to implement the new system and hope that my bright idea will be worth all this effort.

I am surprised with how easily I have embraced the Google lifestyle. Less than a year ago, everything was done on paper; all applications, all evaluations but now, almost everything we do is on Google…and I love it! All applications populate one spreadsheet which makes tracking the process a doddle.