Hi - looking for some guidance! New here - assume I know nothing!
This is the issue that I am trying to solve for: I have a google sheet with overdue compliance trainings. I need to filter this by 2nd level manager, and create new individual spreadsheets per manager.
Then, I need to automatically create an email to each second level manager (email address is in the original google sheet), with the appropriate spreadsheet attached, as well as custom text in the body (which of their managers have how many reports with overdue training, and how many courses total).
If this is too complex, or this is simply not possible - please do let me know! Thanks in advance for your help and support!
Answer
Compliance Spreadsheet Parsing and email creation
- Beginner
Best answer by SamB
Ah I see,
If their manager is listed, then I think you could achieve what you’re after with the following set of triggers and actions:
- Trigger: Every Week (Schedule by Zapier) - set up to run every week on a certain day and time. That said, it could also be set to run be every day or every month depending on how frequently you need the Zap to run. See Schedule Zaps to run at specific intervals for more details.
- Action: Create a loop from text (Looping by Zapier) - creates loops from a list of the different manager names. Each of the following actions would be run for each manager. See Create a loop from text for more details.
- Action: Lookup Spreadsheet Rows (Advanced) (Google Sheets) - searches for the individual manager name and returns multiple rows for each direct report with that manager.
- Action: Create Spreadsheet (Google Sheets) - this would create a new spreadsheet using the name of the relevant manager.
- Action: Create Multiple Spreadsheet Rows (Google Sheets) - this adds the data from the spreadsheet rows that were found by the Lookup Spreadsheet Rows action to the new spreadsheet that was created. To select the spreadsheet that was created, in the Spreadsheet field you’d need to use the Custom value option and select the ID field from the previous action step.
- Action: Utilities (Formatter by Zapier) - Use the Lookup Table transform to create a list of the manager’s names and their corresponding email addresses. See Use a Formatter lookup table step to learn more.
- Action: Send Email (Gmail) - sends an email to the relevant manager’s email address (taken from the lookup table) with a copy of the spreadsheet attached. See: Send files in Zaps to learn more about how to send files between apps.
Hope that helps to get you pointed in the right direction. If you run into any issues in setting that up or have any questions just let me know! 🙂
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.
+11- Community Manager
Hi there
Hmm, filtering/sorting the actual spreadsheet itself can cause issues with the Zap. See: Spreadsheet updates and maintenance for details.
But perhaps you could use a Google Sheets Lookup Spreadsheet Rows (Advanced) search action to find all the rows where there’s a 2nd Level manager and it’s marked as overdue? 🤔
That said, how do you know when a compliance training is overdue? Is there a due date in the row that could be referenced? I’m wondering if you could potentially create a “due date” event in an app like Google Calendar when the row is first added. Then have a Zap that triggers when the due date event starts, then searches for the corresponding row in the spreadsheet to see if it’s been completed and if it hasn’t then you could create the spreadsheet and send an email to the person with it attached.
Could that sort of approach work for your needs here?
- Beginner
Hi there
Hmm, filtering/sorting the actual spreadsheet itself can cause issues with the Zap. See: Spreadsheet updates and maintenance for details.
But perhaps you could use a Google Sheets Lookup Spreadsheet Rows (Advanced) search action to find all the rows where there’s a 2nd Level manager and it’s marked as overdue? 🤔
That said, how do you know when a compliance training is overdue? Is there a due date in the row that could be referenced? I’m wondering if you could potentially create a “due date” event in an app like Google Calendar when the row is first added. Then have a Zap that triggers when the due date event starts, then searches for the corresponding row in the spreadsheet to see if it’s been completed and if it hasn’t then you could create the spreadsheet and send an email to the person with it attached.
Could that sort of approach work for your needs here?
Thanks for the thoughtful response.
The table contains only overdue compliance courses - so no need to distinguish there.
I think the problem that I am finding is how to create several spreadsheets from the master spreadsheet, each with only the direct reports of that manager.
+11- Community Manager
- Answer
Ah I see,
If their manager is listed, then I think you could achieve what you’re after with the following set of triggers and actions:
- Trigger: Every Week (Schedule by Zapier) - set up to run every week on a certain day and time. That said, it could also be set to run be every day or every month depending on how frequently you need the Zap to run. See Schedule Zaps to run at specific intervals for more details.
- Action: Create a loop from text (Looping by Zapier) - creates loops from a list of the different manager names. Each of the following actions would be run for each manager. See Create a loop from text for more details.
- Action: Lookup Spreadsheet Rows (Advanced) (Google Sheets) - searches for the individual manager name and returns multiple rows for each direct report with that manager.
- Action: Create Spreadsheet (Google Sheets) - this would create a new spreadsheet using the name of the relevant manager.
- Action: Create Multiple Spreadsheet Rows (Google Sheets) - this adds the data from the spreadsheet rows that were found by the Lookup Spreadsheet Rows action to the new spreadsheet that was created. To select the spreadsheet that was created, in the Spreadsheet field you’d need to use the Custom value option and select the ID field from the previous action step.
- Action: Utilities (Formatter by Zapier) - Use the Lookup Table transform to create a list of the manager’s names and their corresponding email addresses. See Use a Formatter lookup table step to learn more.
- Action: Send Email (Gmail) - sends an email to the relevant manager’s email address (taken from the lookup table) with a copy of the spreadsheet attached. See: Send files in Zaps to learn more about how to send files between apps.
Hope that helps to get you pointed in the right direction. If you run into any issues in setting that up or have any questions just let me know! 🙂
- Beginner
This is great! Still having some trouble configuring the individual steps, but this feels like a complete solution - thank you so much!
+11- Community Manager
Yay! That’s great news,
Happy to help with any issues in getting the individual steps set up—just send over some screenshots of the Zap setup, showing where you’re getting stuck and we’ll go from there! Just make sure to blur or remove all personal information (names, emails, addresses etc.) from screenshots before sharing - you can use a tool like Zappy for that.
Looking forward to hearing from you!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.