Google Sheets – Google Apps Script – One to One Meeting Tracker

I was presented with a word document last week with a table full of names of staff and dates of their one to one meetings (who uses spreadsheets for managing this sort of thing ;)), ….”Can you make it so that an email gets sent to the supervisors when it is time for them to hold another one to one meeting with their staff?”

“Yes, of course.”

First job was to grab the data and convert it into a manageable list. Ended up with 8 columns: Name, Supervisor, Job Role, Department, Date of Meeting, Completed, Notes/Comments, date for Next Meeting. The notes were to allow for entries of reasons why the meeting could not take place, e.g. maternity leave or long term sick. There was all sorts of rubbish written in the table that wasn’t a date, people who had left and started during the year, which all had to be cleared out. Eventually got a neat set of data to play with.

I also made use of another spreadsheet we already had up and running, which provided a good base for all the staff names, job roles, departments and supervisors, making it much easier to run all the queries and lookups, and to make arrayformulas from :)

The one to one meetings have to happen for this group of staff (@ 100) every 6 weeks or so, in order to meet regulations, so there are several entries for each staff member with dates throughout the year. So the first job in hand was to find the right formula to pull out the date of the last meeting for each member of staff. QUERY came to the rescue, along with the max() and group by features.

This on its own, though very clever, was not enough. We also needed to know about the dates that were “OK”, i.e. the ones that had been completed within the last six weeks, and also, but less of a problem the future dates, and the ones that needed to be don  in the next week or so. A filter formula was used for these three, using a between dates condition.

Then to make the main list more accurate we needed to remove any duplicate entries of staff names. This was done using a formula from Yogi on the Google Product Forums, so credit where credit is due. In essence it takes both lists, does a true/false on them and the arrayformula generates two columns of the output list and the items taken out. Very clever. I finally had a working list I could use, and three reports on the spreadsheet for ready reference.

Now for the emailing part, which required some google apps script, and a few more queries on the spreadsheet. First up a query that pulled in the staff and supervisor names in two columns. Then a unique formula column for supervisors, then a transposed query that put the names of all the staff across a row. I also added an email column and used vlookup to match them up with the supervisors.

 

This all made the script more straightforward, I had to get the values of the supervisors, email addresses, and staff names into an array (which was an array of arrays!). Remove all the empty elements with a .filter(String).Then iterate through each row assigning the supervisor name and email address to variables. The tricky part was with the remaining part of the inner array (row).I spliced out the the first two elements as above, then added a “<br>” in front of each staff name element (for the html email later), then did a .toString() to convert it all to a string. This produced something like: <br>Dave,<br>Nick,<br>Sue,<br>Jane, which should present a nice columned list of names in the email. Here is the resultant script in all its glory:

 

Leave a Reply

Your email address will not be published. Required fields are marked *