Saturday, March 29, 2014

Getting Your Google Drive Spreadsheet To Send Daily Emails

Another quick post today.  I've been playing around with Google Drive spreadsheets and I like what I see.  The scripts are the thing that interests me the most.  Sometimes it's easier to handle data in the javascript like environment than it is to use spreadsheet functions.

One of the cool little tricks I came across while playing, is getting your spreadsheet to email you each day with data from it.  This could come in handy if you have a group of people collaboratively working on a sheet and you want to get a daily report of some metric from it.  The way to do this is to add a small little script that is run everyday.  This is done by setting a timed trigger.  In my case the notifier function is run everyday and only sends an email if it's a weekday.

function notifier() {
  var currentDate = new Date();
  var dayOfWeek = currentDate.getDay();

  // is it a weekday?
  if ( (dayOfWeek >=1)&&(dayOfWeek <=5) ){
    sendEmails(currentDate);
  }
}

function sendEmails(currentDate) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(5, 8, 1, 1);
  var daily = Number(dataRange.getValues()).toFixed(2);

  dataRange = sheet.getRange(2, 10, 1, 1);
  var total = Number(dataRange.getValues()).toFixed(0);
 
  var emailAddress = "email@email.com";
  var subject = ("Report for " + currentDate.toLocaleDateString());
  var message = "Daily " + daily +" \nTotal " + total;
  MailApp.sendEmail(emailAddress, subject, message);
}

Setting a timed trigger is simple as well.  Just have a look at the triggers under the resources menu.

Drop Down Boxes
Configuring a timed trigger

I think this is pretty nifty.  I'm starting to like all this cloud stuff.  You could also use a form to get data from people and then send emails to addresses they enter.  For example, if people were to RSVP to a party via a form leaving their email address, you could then respond to the people coming with any further updates.

4 comments:

  1. What if I want the whole sheet not just cells?

    ReplyDelete
  2. What if I want the whole sheet not just cells?

    ReplyDelete
  3. The page https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail%28String,String,String%29 has a demonstration of how to send an email with attachments. I'd use that as a starting point and progress form there.

    ReplyDelete
  4. This Add-on makes this task even easier. It can email a Google Spreadsheet as PDF/XLS/CSV attachment. You can also send recurring emails to be sent hourly/daily/weekly. https://chrome.google.com/webstore/detail/spreadsheet-mailer/nfefgbkeihioeamkeoeecjdaepfnoole

    ReplyDelete