Send personalized emails with the touch of a button–Google Sheets

Use example:

You want to send emails to inform parents how their children scored on an assignment. Students who failed or did not turn the assignment will need to makeup the assignment.

What do I need?

• Google Account (for Google Sheets)
• Microsoft Power Automate account
• Roster of students along with parent names and contacts

Step 1: Create a new Google Sheet with a unique name

Across the A row, add a name for each data point you plan to use.

• Try to think of each category you’ll want to use. You want to be thorough without cluttering your sheet and creating confusion or a more difficult work flow for yourself.
• Once you run your flow for the first time, a PowerApps ID will be appended to your sheet. You don’t need to worry about creating the IDs yourself.

Step 2: Fill in your information

• If you want the ability to test your automation, add a fake name or two linked to your emails. You can run these to see how your automation is working.
• I recommend student first and last names separately.
• List parent names as you intend to address them in your email
• Add a column which allows you to choose whether or not to send an email.
  • First, select all the rows containing students in your contact decision column. Here, mine is called “Contact?” since it tells my automation if it should send a message to those parents.

In the top menu bar click on “Data” then, in the drop down menu, click on “Data validation”.

In the data validation box, select “Checkbox” as the criteria then check “Use custom cell values.” For checked, set the value to “TRUE” and for unchecked, set the value to “FALSE.”

Finally, add your last few columns which you will use for whatever you wish to contact parents about. I use the same sheet for two different notification processes so I have three columns: one for message body, one for an assignment grade, and a third for the grade on the assignment.

You’re done! This is all you need on your sheet. If there’s anything else you want to add for future utility, feel free.

Note: Use the first two rows for demo students if you want to test how what you’ve created will look and work to parents.

Step 3: Start creating the automation in Microsoft Power Automate

When you log into your Power Automate account, it will look like the image above.

Use the toolbar on the left-hand side of the page to navigate to the create page.

We’ll start from a blank flow in this demonstration. Think about how you want to trigger your flow and choose the start accordingly. For this case you have three options. You can change the trigger method after your flow is complete, but it’s easiest to start with the right method. Here’s a quick overview of the choices:

1. Automated flow: When an action is complete, your flow begins. These actions can include, but are not limited to, the receipt of an email, the completion of a form, or a message on Teams.
2. Instant cloud flow: Push a button on the Power Automate app on your phone or the run flow page of the Power Automate web page on your computer. This method does not require any extra configuration.
3. Scheduled cloud flow: This trigger can be configured to run at a specified time on a day, every day, every week day, etc.

I am going to select scheduled cloud flow for my example.

Step 4: Build out the rest of the flow

Fill in the title and how you wish to schedule your flow.

Configure the Flow as you wish for it to be scheduled. Here, I want my Flow to run at 5:15 every weekday so I changed repeat every to week instead of day and clicked on Saturday and Sunday to turn them grey/ inactive.

Once you’ve set your trigger, you’ll have a blank slate to build the rest of your flow. Here, we’re going to select new step then type sheets into the search bar.

Select “Get rows.”

For file, click the folder and select your Google Sheet. If your account is not already connected to Google, you’ll have see a pop-up asking you to grant permissions to Microsoft Power Automate to read and write on your Google Sheets.

Unless you changed the name of your sheet, the worksheet will be sheet 1.

Next, add a control step and select “Apply to each.” This will allow the flow to loop through once per row.

For the output from the previous step, use dynamic content to select “Records value.” Since the previous step got all the rows from the Google Sheet, this will allow the following steps to loop through and run once per row.

Next, add another control step called Condition Control. This step allows the easy creation of a conditional argument. If the argument returns True, the left leg is run. If the condition is not met, the right leg of the condition runs or the process stops if the “If no” leg is left blank.

My checkbox column was labeled “Contact?” so I’m going to set my conditional argument to if “Contact is equal to TRUE” run the next step.

Create a new step in the green “If yes” leg of your automation. Type “mail” into the search bar and select “Send and email notification (V3)” which allows Microsoft Power Automate to send parent emails for you.

If your school uses Outlook, and you would prefer the emails to come from your personal account, to be sent with options, or to be sent with read receipts, you can swap this step out for the choice which best suits your needs.

Finally, it is up to you to use dynamic content to fill in the email to suit your needs.

This is your chance to get creative and use the email in whatever way best suits your needs.

I use something like this to send test scores from time to time at the press of a button, but another version of this Flow framework I run is automated on a schedule and sends quick updates to parents about their child’s performance in school.