What do you get when you combine the SBU Syllabus Template with a Google Form with some Google Apps Script? A syllabus generator that fills a template with your form responses, creates a Google Doc and emails you a PDF of your responses! The syllabus generated through this form creates the first page of the syllabus ( from the “Semester and Year” to “Attendance and Make Up Policy” and also includes the stock DSS, Academic Integrity and Critical Incident Management blocks of text at the bottom. The form will also email me a copy of the PDF, and it will be helpful to me for troubleshooting and development purposes.
Want to try it and help me test it? Check it out here: https://docs.google.com/a/stonybrook.edu/forms/d/1NNLR9FXVJoXTW_s-WLiQohPHSlAZ0xr8dKhd8AEn9Us/viewform?usp=send_form
How was it created? A very helpful blog post started the process. First, create a Google Doc in the format of the final product (i.e. the SBU Syllabus Template ). In the places in the template where text should be added through the form, the prefix key was added along with a variable name.
Then, a Google form was created that asked for the data that would be placed into the template variable fields.
The form populates a Google Spreadsheet of response data. It is in the response data spreadsheet that the Google Apps Script is added. The script can be accessed by going to TOOLS – SCRIPT EDITOR.
When the script editor loads, you must first clear out any existing code and replace it with the script code. The script is available on my GitHub Repository. The image below is slightly different than the code available on GitHub. The image below shows how I am pointing to my personal template and the code will email me. When the script is enabled on your own spreadsheet, the owner’s account will be used to send the email message.
In addition to creating the script, you also need to set a script trigger. This is done through the script editor under the RESOURCES menu. Click on RESOURCES – ALL YOUR TRIGGERS.
Add a new Trigger to run onFormSubmit with the event From Spreadsheet and On form submit. From the image above, you can see that I also have a trigger set for onFormSubmit, From Spreadsheet, On change. This trigger will go off on any change to the Responses spreadsheet, which could be done without a form submission. You don’t need the On change trigger for your own efforts.
As changes are made to the script, press the floppy disk icon to save the code. You can try to run the script in the editor window, however it will most likely generate an error. That’s ok–you should be able to test it now in your Google Form. Fill out the Google Form completely and then submit the form. The form contents will populate the spreadsheet and the script will be triggered. The Google Doc template will be copied using the naming convention supplied in the script and the form data will be applied to the appropriate places in the template. The Google Doc will be saved, converted to PDF and emailed to the person who submitted the form and the email addresses specified in the script.
If everything works properly, a PDF should show up in your inbox. If something went wrong, Google Apps script will send an email with the error message.
Google has documented the Apps Scripting rather well, so if you run into any issues developing your own script you can find many examples to guide you further.
Google Apps Script is so powerful-great use!
Check out the similar Autocrat (now an add-on but used to be in the Script Gallery): http://cloudlab.newvisions.org/add-ons/autocrat.
Thanks, Julie! That seems to do most of the work for you, which makes things even easier.
Mark, This is great. Thanks. I haven’t delved too deeply into the script editor as yet, but seeing this has given me new motivation. I can think of lots of uses within my department. Thanks for sharing.
Thank you for your comments, Rich! I hope it works out for you and your department.