Managing Daily Priorities Using Automated Emails from Google Sheets

A personal need driven productivity hack

Inspiration

The concept of planning weekly priorities and distributing them across the days of the week, from the book -  ‘The 7 Habits of Highly Effective People’  by Stephen R. Covey

Pain Point

Ineffective ‘To Do’ lists written on paper or saved digitally that are never revisited

Planning Tool

Google Sheets. Problem faced again - I must open up Google Sheets to begin

Potential Solution

Receiving emails about daily priorities

Integrating Potential Solution with the Planning Tool used

Automated emails from Google Sheets

Existing Solution

Google sheets add-on ‘Add reminders’

Drawbacks

Emails not sent automatically. Not flexible for customisation.

What I needed?

A simple & reliable system customised the way I want

How it worked out for me?

I found that it gave me a sense of direction each day. Did it solve the problem I faced? Definitely! I no longer needed to find the motivation to open my list first since the list came to me. Did it help me attain the utopian dream of perfect productivity everyday? No! I believe it’s a continuous problem of bettering ourselves.

“Well begun is half done - Aristotle”

Putting it into action

Step 1: Creating the Google Sheet

Create a Google Sheet with dates and your weekly priorities distributed over the days of the week. In any cell (A1 in this case), use the =TODAY() to get the current date.

Note: Make sure that the dates you enter are in the same format as that returned by the TODAY()

Tip: To enter a new line within the same cell, use Alt+Enter

Step 2: Add the script

From the Tools menu, select Script editor.

The script is given below.

function myAlerts() {
// this runs based on daily trigger
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Schedule"); 
  var range = sheet.getDataRange();
  var values = range.getDisplayValues();
  var lastRow = range.getLastRow();
  var curDate = values[0][0];
  var anyMatches = false;
  var message = "Hi Keerthi, <br />\n Good morning! Here are your priorities for today. <br />\n";
  var sheetUrl = ss.getUrl();
  var email = Session.getActiveUser().getEmail();  
  for (var i = 1; i < lastRow; i++)  
  {  
   // if today matches the alert date, send an alert
   if (values[i][0].toString() == curDate.toString())
   {  
     // add a message for this row if date matches
     var s = values[i][1].split("\n");//for formatting
     for(j=0; j<s.length; j++)
     {
       message = message + [j+1] + ". " + s[j] + "<br />\n"; 
       //formats the message in a numbered list    
     }
     // if there is a match, set anyMatches to true so and email gets
     //sent
     anyMatches = true;  
   }  
  }  // ends for loop
  // footer for message
  message = message +  "<br />\nThis reminder was generated by this spreadsheet: <br />\n" + sheetUrl;
 if (anyMatches)
 { // send an email   
      MailApp.sendEmail({
      to: email,
      subject: 'Your Priorities for Today',
      htmlBody: message});
  } 
 }

Step 3: Testing the script

In order to test the script, change one of the dates to the current date and Run the code in the script editor. Allow any permissions that are requested by the app. Check your inbox to see if the email turned out the way you wanted. Play around with the formatting to suit you.

Step 4: Setting the trigger

To tackle the problem reported in the add-ons about emails not being sent, set a trigger for the script. Click ‘Current project’s triggers’ in your script editor.

Add Trigger. You can set a time driven trigger. Here I’ve set a Day timer that will trigger the script between 8am to 9am.

Click Save and this will solve the issue.

---