A simple shared TODO list with daily email summaries in Google Apps

Last time I gave an example of email notification workflows using Google Apps. This time we’ll extend the email sending capability of Google Apps with another real-life example: a shared Google docs TODO list.

Suppose you and your team use a simple shared Google spreadsheet to help manage tasks that need to be performed:

todolist

Team members add and remove tasks to the shared list throughout the day as they complete their work. At the beginning of each day, it would be nice to have a summary email in everyone’s inbox of what they need to work on each day. This would save each person the time of looking through the list of tasks for items that belong to them. Luckily, this is pretty easy to create with a little Google Apps Scripting.

function sendTodoReminderEmails() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // get all the data in spreadsheet
 var range = sheet.getDataRange();
 var values = range.getValues();

 var todo = {};
 
 // Column A is task, column B is person's email, assumes header row in table
 for (var row = 1; row < values.length; row++) {
   var task = values[row][0];
   var email = values[row][1];
   
   if (!todo.hasOwnProperty(email)) todo[email] = [];
   todo[email].push(task);
   
 }
 
 //send out summary emails
 for (var email in todo) {
   var emailBody = 'Hey! Here are your current tasks:\n\n';
   for (var task in todo[email]) {
     emailBody += todo[email][task] + "\n"
   }
   MailApp.sendEmail(email, 'Your daily todo list', emailBody);
 }

}


Add the above code by clicking "Tools", "Script Editor" in your Google Spreadsheet. Then, click "Resources", "Current Project's Triggers", to set up this function to run daily at 6am:

dailyemailtodotrigger

Now, every day in your inbox you'll get emails like this:

todoemailinbox

Cool!

How to set up Email notification workflow in Google Apps Spreadsheets

I often use Google Apps Spreadsheets for collaboration on a document with others. I created a simple custom function that will email me any time someone opens (or alternatively, edits) the spreadsheet, so I know when I should go in to check on changes made by others. My function emails me the name of the spreadsheet, the email address of the user (when possible), and a convenient link to spreadsheet as well. Here’s how:

1. From your Google Spreadsheet, click Tools, script editor.

2. Add the following code, changing the email address (YOUREMAIL@gmail.com) to the address that will receive the notifications:

function sendEmail() {

     //------------------------------------------------------------
     // set the email address you'd like to be notified at below
     var emailAddress = 'YOUREMAIL@gmail.com';
     //------------------------------------------------------------
     
     var editingUser = Session.getActiveUser().getEmail();

     var emailSubject = 'Hey! ('+editingUser+') edited the spreadsheet ' + 
                         SpreadsheetApp.getActiveSpreadsheet().getName();

     var emailBody = 'Link: ' + SpreadsheetApp.getActiveSpreadsheet().getUrl();

     MailApp.sendEmail(emailAddress, emailSubject, emailBody);
}

3. Click Save and give the script a name.
collab_email
4. Test the email function by clicking Run, sendEmail. It will ask you to authorize this function, as it will be sending emails as you. Click Continue, then Accept.
collab_auth_required

collab_requestforpermission
5. Now you need to create a trigger to bind the function to an event (for example, someone opening the spreadsheet). Click Resources, current project triggers.

6. Set up a new trigger. The “Event” should be “From Spreadsheet”, “On Open”. If you like, you can set up an email notification if the trigger fails. Since we tested the trigger function above, this isn’t really necessary.
set up trigger
7. Done! Now close down the spreadsheet, open it up again, and you should receive an email.

Enjoy!

PS: there are a couple caveats with this approach:

1. Google Apps only gives you access to the user who opens the Spreadsheet if you are a Google Apps for Business user and the user is in the same organization as you. If you give access to the spreadsheet to other gmail / Google users or anonymous users, for security purposes it will not be able to tell you who is opening or editing the document.

2. The method above uses installed triggers. Google Apps also has simple triggers, but these are not allowed to do things that require authorization, like send emails, and are therefore not suitable.