Sheets

Become a Google Sheets Automator!

For this project, you will use Google Sheets add-ons to automate the creation of Google Docs, Calendar events and email.

Autocrat

Create a Google Form where you ask for users to ask a question about a Google App (Users should ask the question, but you should provide context for what you want them to ask you about, for example "What question do you have about the mail merge feature of the Google Sheets add-on Yet Another Mail Merge?".
Require email collection.
Change the theme to match your question.
Export the responses to a new Google Sheet.
Install the Autocrat add-on for Google Sheets.
Create a new job so that Autocrat creates a PDF of a Google Doc of the answer to the question you asked to anyone who submits the form.
Send an email to the person with the question and answer the person put as well.
Put your address in the CC line so that you know when autocrat is sending an email.
Test your form by filling it out and viewing the results.

Calendar Event Automator

Go to Google Calendar and create a new calendar named "Event Schedule".
Using the same Google Form you created in the previous task, create a new section named "Schedule Help".
Give the section a proper description (e.g. "Need more help? Pick a date and time below to schedule a meeting for extra help.").
Add questions that ask users to select a date and time. (You must use a date question for the date and a time question for the time or this won't work).
Then add a question asking what the user would like more help with. This should be a short answer or paragraph response question so that they can write it in.
Go to the response sheet and get the Calendar Event Automator add-on from the Google Sheets Add-ons store.
Open Calendar Event Automator and select the Event Schedule calendar from the list.
Select the sheet name where your form responses are being recorded.
Title your event "Help Session for <<Email>>". Be sure that you use the exact name of the column where emails are being recorded between the << >>. These are calling the column name and if they are misspelled or calling the wrong column, your event may not be created.
Select the proper column for the start date and start time.
You may select the same columns for end date/time or create 2 more questions in the form to have users fill out end date and times. The event will be created even if the start date and end date are the same and the start time and end time are the same.
Under Optional event details, in the description field, type in "Requesting help regarding "<< [title of question you asked in help section] >>". Again, make sure the words between the << >> are exactly the same as the column heading for the question.
Save your event.
Then fill out the form to test it.
Once you've tested it, send an email to your instructor with a link to the form and a link to the Event Schedule calendar. Make sure your calendar is public so your instructor can view it.

Yet Another Mail Merge

Get the Yet Another Mail Merge add-on from the Google Sheets Add-ons store.
Then log into Gmail and draft an email that reminds the form respondents when their preferred schedule dates are. Use the columns on your sheet to remind the respondent of what they requested help with and when they wanted to schedule a meeting. Be sure to reference these columns using the {{ }} symbols (different from autocrat which uses << >> to reference columns). Do NOT send this email, it has to stay a draft for Yet Another Mail Merge to use it.
Then go back to your Google sheet and start a mail merge using Yet Another Mail Merge.
Type in your name for the Sender Name and browse to find the draft email and select it as the Email Template.
Send an email to yourself (since you tested it in the previous part of this project, your email should already have been imported). You can send 50 emails a day with the free account of Yet Another Mail Merge.

Blog Post

Create a new blog post for Google Sheets.
Embed the Google Form you created for Autocrat.
Embed the Response sheet.
Embed the Google doc template you created for Autocrat.
Embed the PDF that was created by Autocrat on the form trigger.
Embed the Google Calendar you created for Calendar Event Automator.
Take a screenshot of the draft email you saved for Yet Another Mail Merge.
Write a brief reflection on how you can see these tools being used in your personal or professional life.

Then comment below with the link to your blog post AND copy and paste your reflection into the comment.