So there may be ideas out for this, but the latest update from Autocrat takes this to a new level.  In my PLC/Voxer groups, a friend (@)  had asked about my recent experiment with Autocrat (see previous post ) and how it went.  He wanted to know if it could be used in his workflow when tracking behaviour.  I thought it must be possible and could test my skills if I could find a solution, now I am no expert and constantly learning, but thought it would be a good challenge.  I set to work.

I created the basic form, template for report, and started using the autocrat script.  Now what @SchleiderJustin wanted was the ability to scan a QR code, student to fill the details in on the form, and then email different people including himself, parents and others, without him having to do too much typing and take away from teaching time to complete the form.  Now I could see it could be straightforward to include one email address, but more than one? I searched the web and found out how to leave information in the boxes using pre-filled responses, see below.

Screen Shot 2014-10-04 at 22.37.37 Screen Shot 2014-10-04 at 22.40.22

So now when someone goes to the form, the email address are already in.  This would allow, once the form was sent, the autocrat script could send out to the relevant staff.  I told @Justin and was excited to see it in action, which would discuss during a GHO. But still wanted to take this another step forward using the script update.

I set about with a new form design, but wanted to streamline even more.  I wanted for the user of the form to be able to select the childs name, then use a formula to look up the childs parents email once submitted.  Also wanted to have the option of emailing the different people using the script. So time to read and watch videos on youtube. Obviously all fields can be changed to suit schools/lessons etc.

Screen Shot 2014-10-04 at 22.49.50 Screen Shot 2014-10-04 at 22.49.57 Screen Shot 2014-10-04 at 22.50.03 Screen Shot 2014-10-04 at 22.50.08 Screen Shot 2014-10-04 at 22.50.13

Once the form was submitted, I needed to go back to my college days and remember how to use key terms such as ‘IF’ statements and ‘VLookup’ to find the data I needed.  For example when the students name was entered I wanted it to lookup the parents email and input it in the correct column then I could use the autocrat script to send a doc template out to relevant email addresses.

Screen Shot 2014-10-04 at 22.51.19

Screen Shot 2014-10-04 at 22.50.59 Screen Shot 2014-10-04 at 22.51.05

I found that once the form was submitted the formula would just move down the sheet, which meant it did not work.  A bit of research helped me find the CopyDown script.  This enables any formula in Row 2 to be added to new rows once new forms had been submitted. BINGO!! Now the Form could select the student, and have the parents email, and decide to email the teacher or Admin, perfect! Now to run Autocrat.  I assembled a basic Template that would be emailed to the relevant parties and put the correct tags in to aid the merge. Again if you do not know how to use Autocrat, check my previous post.

Screen Shot 2014-10-04 at 23.13.29

Now the Autocrat script was set up, the new update allows for the script to be able to run when new form submissions are noted.  I submitted the new form, the formulas copied down using the CopyDown script, perfect, then BOOM, the Autocrat script did not work!! GRRRRR! So what now? I started looking for a solution as this should be possible.  Two scripts working together in tandem to increase workflow has worked before why not now?

So after looking on Google I found a post on Google+ from Jared Carrano who works for the makers of the script.  I noticed there was not just me having problems with the script, there seemed to be a bug.  After a few emails between Jared Carrano & Andrew Stillman,  the makers were busy trying to fix this…..I waited for the fix.

Less than 24 hours the fix is here and now it is all working! Amazing, this will help Me, Justin and others out there to work smarter.