Generating an emailed roundup of outstanding actions

I recently was asked to generate an emailed reminder for actions following meetings.  This solution was put into place to do this, but rather than send out an email reminder per action, this combines all outstanding actions for each user and sends them a single email, with a link to update each item.

The end result looks something like this:

This was done in part by using a useful formula in Google I had been unaware of until recently, "REPT"  Combined with CONCATENATE, this can pull through matching items from another range of cells.

e.g. in my demo sheet  the formula =ArrayFormula(concatenate(rept(Outstanding!L:L&" ",Outstanding!C:C=A3)))
brings in all items from column L in one sheet, where Column C in that sheet matches cell A3.

In the demo sheet, this formula brings through some concatenated HTML table code which wraps the data for each line.

Progress stars correspond to a Google form Linear Scale question  (1 to 5) with a simple formula to select the appropriate image.

So that only those with actions get sent emails, I used a query to just bring in items with less than 5 stars (complete) and then a pivot query on those items.

A copy of a demo sheet including scripts can be found here

For the scripts -  I just set up the getEditResponseUrls script to run on Form submit (change the Form URL in the script to yours before doing this)  and the sendweeklyemail script to run on a timer.

Another tweak that would need to be made would be to amend the form to put in the list of names for the actions, replacing the ones in there, and putting in the matching names and email addresses in the Vlookup tab in the Google Sheet.


The demo sheet is also set up to display easily in an Awesome Table as per the example below

Hope this is of interest, any questions please let me know