How can I automate a daily email where each supervisor only receives the data specifically for them?

[Kimberly Sitton]

I am trying to write a code where the Supervisors on my spreadsheet can be grouped together. I would like to autogenerate an email where all of the rows for each supervisor are emailed to each supervisor daily.

Ex: I would like Antonio R to only receive the rows where he is listed as a supervisor. I would like John Y to only receive the rows where he is listed as a supervisor. I would like Kim S to only receive the rows where he is listed as a supervisor. I would like this email to be sent to them on a daily basis. Can anyone please help with this?

[Brian Pugh]

We use FormMule to do this sort of thing. If you wish send me an email and I will help you

Brian

Brian Pugh, IT/Educational Technologies Associated Hebrew Schools | Danilack Middle School p: 416.494.7666, | e: bp...@ahschools.com w: www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

On Wed, Mar 23, 2022 at 3:18 PM Kimberly Sitton <sitton...@gmail.com> wrote:

I am trying to write a code where the Supervisors on my spreadsheet can be grouped together. I would like to autogenerate an email where all of the rows for each supervisor are emailed to each supervisor daily.

Ex: I would like Antonio R to only receive the rows where he is listed as a supervisor. I would like John Y to only receive the rows where he is listed as a supervisor. I would like Kim S to only receive the rows where he is listed as a supervisor. I would like this email to be sent to them on a daily basis. Can anyone please help with this?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/16d7c6a9-609d-4fa6-b451-700e93a1d699n%40googlegroups.com.

[Laurie Nason]

Hi Kimberly,

If I was going to do this using scripting, I would do the following:

1. Have a tab where you have various settings - which would include your supervisor list, along with their email addresses and any other info that you need in one place
2. Create a named range for the names of your supervisors from that tab and use that as data validation on the sheet that you have attached the screenshot of in Column I - this way you don't run into problems with people typing the name of the supervisor with WonKyCapsandnospaces which would mess up the next part.
3. Create a new tab where you can select the name of your supervisor from a cell with validation on your named range created above (again to remove typing from the equation!) - this can then pull in the supervisors email into a cell using a vlookup
4. On that tab, use the "Query" function to select only the rows for that supervisor, and include the information that you want to send to the supervisor
5. In a script - use the named range for supervisors, and one by one set the value in the cell in #3 above.
   
6. Then take the values in the query cells and add them into an email and send it to the supervisors email. (i might build in a way to test it before flooding your supervisors email addresses while testing!)
7. Call the function using a trigger set to whatever time you want to send the email.
   

If you want a bit more help with setting this up, let me know.

Laurie

On Wed, Mar 23, 2022 at 10:18 PM Kimberly Sitton <sitton...@gmail.com> wrote:

I am trying to write a code where the Supervisors on my spreadsheet can be grouped together. I would like to autogenerate an email where all of the rows for each supervisor are emailed to each supervisor daily.

Ex: I would like Antonio R to only receive the rows where he is listed as a supervisor. I would like John Y to only receive the rows where he is listed as a supervisor. I would like Kim S to only receive the rows where he is listed as a supervisor. I would like this email to be sent to them on a daily basis. Can anyone please help with this?

[Stephen Barker]

Another option would be to use https://datastudio.google.com/ with more of a self service portal and apply a filter by email [1] and if you or the user really wants a daily email you or the user, can schedule an email delivery[2]. My experience is daily emails end up getting overlooked for some users.

[1] https://support.google.com/datastudio/answer/9713766?hl=en#zippy=%2Cemail-filter-examples

[2]https://support.google.com/datastudio/answer/9263641?hl=en#zippy=%2Cin-this-article

[Laurie Nason]

I like the data studio idea :-)

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/939a7c97-4d40-4e9a-9a43-a195c1216ca3n%40googlegroups.com.

[Kimberly Sitton]

Thank you all! I truly appreciate all of your suggestions!