english4blogging

Nives' endeavours as an English Teacher

AutoCrat on Google Sheets

Leave a comment

Creating a Certificate using AutoCrat add-on in Google Sheets

How to use Autocrat in Google Sheets using Google Forms

The use of AutoCrat to create automated mail-merge templates (eg. a certificate) within Google Drive is a great tool for Teachers.

Mail Merge templates can be used for:

  • Creating individual Certificates and having them emailed out to participants
  • Updating participants for a roster / schedule or agenda
  • Sending out test results
  • Sending out promotional letters / emails to many people

The important thing is that the Source Data Fields are created and matched when creating the Autocrat job.

To make all of this work you will need the “Source Data” which can come from a simple table in Google Sheets, or it can also be obtained using Google Forms where participants respond to a survey and the data is sent to the Google sheet automatically.

This will become your ‘source data’ and will be used to create your merged documents. Remember you can create more than one template for the same data.

The creation of a recent job allowed the collation of screen shots and the consequent creation of a Google Slides presentation which will be the basis for making a video Tutorial as well. The Google Slides will be shared publicly on SlideShare and the Video Tutorial will be added to the YouTube video tutorials playlist in my channel.

The whole procedure of taking screenshots in a step-by-step procedure in AutoCrat was also made possible with the online collaboration of my brother Marco Torresi. Working from Australia online connecting to me in Italy. That is one of the amazing things about Google Drive – the possibility to share the editing of the same documents simultaneously.

Sometimes even the use of a Screen share program assists in shortening the distance and making online collaboration even more fulfilling professionally. In our case we frequently use Skype. Obviously at the basis of all online live collaboration is the availability of a good or excellent internet connection – without which we would be delving into the dark ages!

Let’s take a look at the slides…please note that following the slides are the notes for the slides in relation to the procedure for using and setting up a new job in AutoCrat. 

Notes for the Slides to understand the process:

Slide 5
In this example I will be using AUTOCRAT to create a THANK YOU Note that will be automatically sent to the respondents of a specific Google form created, in this case, for an Online Live Webinar. Please note that Step 1 and 2 are interchangeable!
Slide 6
NB* Remember to add the AUTOCRAT app to Google sheets before starting!
Slide 7
Firstly I am going to create a Google document with the source data merge fields – here I have chosen Your Name and Timestamp.
Slide 8
Obviously the information for my source data merge will be gleaned from the information requested in the Google form.
In my form I am requesting an email for auto sending BUT in some cases you may want to collate and print the Certificate to hand out by hand, for example at the end of a conference.
Slide 9
The reason I am using a Google form is that the Google Form automatically creates a Google Sheets Document which is the basis I need for AutoCrat to work.
Slide 10
Once you have installed AutoCrat from the GET ADD-ONS tab then it will show up in your Add-ons tab. Select AUTOCRAT then click on OPEN.
Slide 11
At this point there are NO merge jobs so I will create a NEW JOB
Slide 12
Next give the Merge Job a name then click on next…
Slide 13
At this point the TEMPLATE will be the GOOGLE Document created at the beginning of this slide presentation.
Slide 14
Autocrat will ask me to select the file from my Google drive once selected it will show up in the step 2 of Autocrat.
Slide 15
At this point I will need to assign the Source data (from Google Sheets) into the document template.
Slide 16
At any time you can open the sidebar on the AutoCrat window and see the ‘source data fields’ available in your Google Sheets created from Google Form.
Slide 17
At this point AUTOCRAT needs to know how you want to save the document, that is the FILENAME you want to assign to each individual file…
1: I have selected the name of the Survey + Name of the respondent + Email of the respondent … You can choose what ever filename you want to match your Source Data criteria.
2: Then select the TYPE of document you want to ‘print’ in other words send out to the respondents (in my case it will occur automatically – but this is not necessary). You can choose Google doc or PDF … I selected pdf.
3: Multiple documents – means that each source data merge will actually produce one document for each respondent. I chose MULTIPLE as I want to automatically send each respondent their own individual document.
However if you want to collate all the documents in one SINGLE document – then you will get one document with X amount of pages (as many as respondents or recipients as are in the source data). This could be handy when you do not have the respondent’s email such as for students in your classroom.
Slide 18
Select the folder you want to save all the documents from YOUR OWN GOOGLE DRIVE folders or create new folder in Google Drive.
Slide 19
I will skip this step in AUTOCRAT – as it is not needed for the current exercise.
Slide 20
I will skip this step in AUTOCRAT – as it is not needed for the current exercise.
Slide 21
In this step 8 of AUTOCRAT you will need to tell the system what to do with the docs and whether to send the emails…I have chosen YES for my example.
Slide 22
Continuing in Step 8 of Autocrat, further down you can customize the email message (arrow 6) and send BCC or CC to the main collaborators (arrow 5) – in this case myself and Tom. Remember to add the source data field <> (arrow 4) where the respondent’s email will appear.
Slide 23
AUTOCRAT message is telling me to create TRIGGERS – that is to let Autocrat know when to carry out the job.
Slide 24
In this case the trigger is the Google form – after making the selection click on SAVE.
Slide 25
The job is now being saved.
Slide 26
AutoCrat has saved the job and now we can look at the icons that have appeared next to my job description and what they mean…
Slide 27
Each icon tells you what it represents when you hover over it with your mouse…The RED TAG is letting me know that I have an ‘unmapped tag’ in the job. One of my source data fields has not been assigned…clicking on the RED Tag will take me to the missing source data and ask me to assign the merge field. Once that is done – the AUTOCRAT job is complete and I can run the job.
Slide 28
… Now all we need is for the form to do it’s job!
Note the green arrows – The Help Guide is a great tool and the Manage Triggers link will allow you to change the triggers whenever you want.
Slide 29
Send out the form link and wait for the Respondents to reply…
Slide 30
Once the respondents have completed the survey on the Google Form – the trigger previously set will ensure that as soon as the respondent closes the Google form after filling in the required MERGE FIELDS then the Document will be EMAILED immediately.
At the same time GOOGLE DRIVE will collate a copy of all the emailed documents in the previously selected folder.
That’s it all the hard work is done automatically using AUTOCRAT in Google Sheets.

Please remember that if you find my instructions a little too confusing then do make reference to the AUTOCRAT pdf available within the Add-on or please leave a comment below and I will endeavour to assist. 

Advertisements

Author: Nives Torresi

Nives Torresi is a Teacher of English as a foreign language; English Drama; Business English and English Exam Preparation (Cambridge FCE). Nives also translates from Italian to English (with a specialization in Business Manuals, Historical archives and Medical papers), works as Interpreter and Mediator for Business negotiations. Currently also teaches online via interactive learning platform for Business English courses and Language tutoring. Participates actively in online Teacher Training or development courses such as M4T Moodle for Teachers, Teaching with Technology, Learn to Blend and Flip with Technology, Action Research Projects to improve Instruction and Learning. Moderator in the Moodle courses for M4T EVO 15; M4T EVO 16 - TEFL2YL EVO 16 Recent online live webinar Presentations include: - The "One Foolish Monkey" series on History of Blogging with co-Teacher Tom Hodgers - CO15 - Connecting Online live Conference - "Understanding Pair Teaching in Micro Teaching Online" with Tom Hodgers. - Webinars for IATEFL YLT SIG: 2015 - "Play, Learn and Grow Together" an After school English language learning project. 2016 - "Play, Learn and Grow Together" a Pre-school English language learning project. - MM7 Moodle Mooc 7 by Integrating Technology - M4T moodle for teachers - Live Webinar presentation "Teaching and Learning Along the Way" November 16, 2015 - EFL Talks - Answers webinar February 20, 2016 "Using a Board Game in the EFL classroom" - EFL Talks - TERMS "PODCASTS" - MM9 co-Presenter with Thomas Hodgers "Teaching Online with Moodle" - MM9 Panelist on Digital Badges Discussion - HeartELT project - contributing author letter "W" - EVO Moderator Training 2014; 2015; 2016 - TEFL2YL EVO 2017 Session moderator and presenter "Board Games in the EFL Classroom" CO 17 Connecting Online live Conference Presenter with Tom Hodgers "Teamwork Online" - 2016-2017 Business English Conversation Course - a 36 hour course for industrial Engineering firm.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s