CAUGHT BEING GOOD
Back to School Project
Here is a complete tutorial for this project. I have tried to include all the steps that make this work.
UPDATE USING DOCUMENT STUDIO
Since I first published this project, I have found another way to do this without using autocrat. There is a new add-on called Document Studio, which I find to be better programmed, less buggy, faster, and easier to use than either Autocrat or Form Publisher. The concept is exactly the same so the instructions here will work just as well. One would just install and use the Document Studio add-on to sheets instead of Autocrat. There is a cost to document studio but it seems reasonable.
There is one thing I still use autocrat for which Document Studio does not do. Document Studio will create a one-off of each form submission to share with the recipient. Autocrat will allow you to create a slideshow with all the form submissions on a single slide deck. The way this works is that I have both a Document Studio job, which processes each submission as it is submitted, and an Autocrat job that I run manually when I want a slide show of a single slide deck of all submissions. This is useful in a school where the principal can create the slideshow of all the great things in the school each week, month, etc. These can then be displayed on the school reader board, assembly etc.
The charts on the left I added to demonstrate how you can add a visual graphic to your website on the number of times that a form has been submitted. To do this I added a column to the form response sheet, and in row A of that column is Forms Filled and in row 2 of that column I put this formula: =counta(A2:A). This counts the number of form submissions. Then I just made a speedometer chart of the two rows of data, which updates each time a form is submitted. The second chart is also from the sheet using a simple scorecard chart of the same data.
As you get ready to start the new school year, here is a project I have just completed that will help you work with teachers and students. Students like to be rewarded for doing great things, and as I heard from one principal +Daniel Founder, teachers appreciate recognition for work well done. I had started this a day ago and with the feedback that I have gotten have now modified it to include a student component, and a teacher component in the same form. The goal is to make it simple and easy to catch your students and teachers at their best, and be able to record it instantly with a picture and post it back to the teacher, student, or parent with very little effort.
This document is a complete set of guidelines to create your own version of this.
This setup can use the Form Ranger Form add-on to autofill the lists in the form from the data you submit. It uses Autocrat sheet add-on to create the certificate including the picture that you upload with your phone. Theoretically if you had all the student names spelled correctly, and all the parent email in a separate sheet, this could be automated to email the parent the certificate when you submit. That would require a VLOOKUP function that would autofill the parent email based on the student name. Right now you would have to email the completed certificate when you get back to your desk.
Here is a full video tutorial of this in its latest iteration. I have added a few more features in this version so that it could be used at a district level, by all staff and admin. This is a bit long so drag your way to the parts you need to see.
The key to getting the image to display on the doc is as follows:
Create your form
Submit once
Create the spreadsheet
Use crop sheet add on to remove all extra rows and columns
Add back in one more column
paste this formula in the top row of this column:
={"Uploaded"; ARRAYFORMULA(Concat("https://drive.google.com/uc?export=view&id=", REGEXEXTRACT(E2:E,"id=([^&]+)")))}
where you put in the the column on your form where the image is, in this case it is column E. What this does is replaces the URL that the image upload creates and changes it to this URL which works with autocrat. The default upload URL does not work. (https://drive.google.com/open?id=XXXXXX)
This then is the column from which autocrat pulls the image. The ARRAYFORMULA (and the removal of all extra rows) allows the formula to be copied down on every new form submit.
PDF Version
This uses a Slides Template instead. This will have the benefit of allowing for more flexibility in how it looks. Autocrat works by creating a single slide document.
You can play around with the format of your template.
Below is the sheet that you can open and make a copy so you can see how the formulas are setup.
The lists tab has the formulas I use to have form ranger auto-populate the multiple choice questions in the form to keep form input time to a minimum. This step would not be needed if you just want the description to be a paragraph type, and you dictate the content to your phone on the fly. Originally I was thinking of some standard things that kids might be doing and wanted to simplify the input. However, if you wanted to dictate, you could also get rid of the location field, and just dictate a different description for each upload. All kinds of ways to ITERATE this!
PDF Version
This uses a Slides Template instead. This will have the benefit of allowing for more flexibility in how it looks. Autocrat works by creating a single slide document. It is mailed to the email address as a PDF.
George Castanza Citizenship.pdfSlides
I have autocrat doing two jobs. This one is not set to run on form submit. I will manually run this to create a slide show of all the awards that have happened in a time frame. This will create a slide deck that can be used for an assembly or put on the school web page with all the slides automatically created in one slide show.
NEAT!!!
In this newest iteration, I have setup the form (go ahead and try it) to allow for either a Student or a Teacher Award. In this case Autocrat has two jobs setup, one for students, one for teachers. During the setup for autocrat, in step 7 setup the merge condition for each of the two jobs. The two jobs have two differing templates.
Here are two screen shots from the Autocrat setup. The first shows how I have two different jobs. I am thinking of adding a third job type and a third section in the form so I can just create a great pdf of some of my flower pictures.
This second screen shot shows how to make the autocrat script run the correct job for each type, (by the way you could have other types in here, I am thinking) so that it uses the correct template.
