Search for Web 2.0 Tools

Wednesday, May 29, 2013

Google Form/Spreadsheet Merge using autoCrat script.

Thanks to Andrew Stillman for creating an extremely useful and powerful script that allows for document merging within Google Apps.  Here is a link to a video at youpd that will demonstrate the functionality of "autoCrat."  The video is from the first version that was created.  AutoCrat has evolved to an even more useful tool with the latest release of autoCrat 4.3.  You need to know this because the video is older and some of the use of "autoCrat" has changed a little and the video show how to install the olver version, but that is OK because that is how I learned to install it.  Literally takes seconds.

The Snowflake Unified School District has given me a task to design a simple and shareable resource that would allow parents and students to register online.  Using the "autoCrat" system allows me to put a Google form online that when submitted sends the student's answers to a Google spreadsheet.  This then triggers an event to where it fills in a Google document that resembles our current paper registration forms.  Once the registration form is filled it is stored in one of my Google Drive folders where it can then be printed and handed to the parents to sign the documents.  The greatest parts of this is that forms can now come in with legible handwriting.  The secretaries are excited to give it a try this fall.

Below is a little demonstration as to how our district is going to use "autoCrat."

1.  First create your Google form, asking all of the questions needed for registration.


















2.  Create your Google spreadsheet by clicking on the correct option within the "Responses" tab.












3.  Install "autoCrat" in your new Google spreadsheet.  Go to the "Tools" tab and select the "Script gallery.." option.










4.  Use the search form to search for "autoCrat."





5.  Click on the "Install" button. 









6.  The "Install" button will open up another dialogue box.  Just click the "Autorize" button at the bottom.







7.  Now create a Google document using "<<Tags>>" that will let "autoCrat" know that that is where you want your information to go.









8.  A tip for this is to create your <<tags>> the exact same as your spreadsheet columns.  It will save you steps in the long run.  You don't have to, but it made time go faster for me.  I had about 100 different <<tags>>.

9.  You now need to save this document as a Template.  To do that go to your "Drive" and click on the checkbox next to your document.  Now click the "More" tab and select the "Submit to template gallery" option.  NOTE:  This will take you to another screen and ask for you to describe the template before you can continue.














10.  Go back to your spreadsheet and click on the new "autoCrat" tab and select the "Run initial configuration."









10. Click the "Choose template from Drive" button.  This will open another window with your template in it.  Select the new document that you created.













11.  After your document is selected click the "Save settings" button which will pop open another dialogue box.  Click the "Save" button.








12.  I just leave this setting along and click the "Submit" button.










13.  Now match the Document <<tags>> to your column headers on your spreadsheet.  This is where you could save time, but make sure they match because if they do you won't have to spend time matching them up.  Once you are done matching them all up click the "Save mappings" button.









14.  The "Set merge type" section allows you to determine what you want to do with your document.  You can see that I have set this up to save the merged file to a folder in Drive called "Wireless project."  The document will be saved using the students last name and then the first name.  It is going to be saved as a PDF file that is easy to email and print.  In my spreadsheet I am going to create a link to the document so that I can easily find it from right within my spreadsheet.  I could email the document automatically if I wanted.  Finally I chose the option to merge the file the moment the form is submitted.  Just choose the settings that are right for you.  Click the "Save Settings" button.
















15.  You can not test the merging by clicking the "Run merge now" button.  Note though that until you actually fill out your form and submit it, you wont have any information in a spreadsheet to merge.








16.  You can see that the document is found in my drive called "Weir, Brian.pdf" which were the settings I used.









17.  Opening up the merged document you can also see that the tags have been filled in nicely.









Well good luck for those who give "autoCrat" a try.  I envision using this much more than just creating registration forms.  How about homework assignments that come in prefilled after a student fills out the form or even the ability to create a simple to use referral form for disciplinary action that can be directly emailed to your administrator.  Plenty of potential using the "autoCrat."