Email A Daily Budget Sheet Summary Using Google Scripts

Tiller can already send you a daily email of your latest transactions and account balances, but what if you want to get one of your sheets, like your budget dashboard, emailed to you or your spouse every day or once a week? Google Sheets together with the power of Google Scripts provides a way to do that. In this post, we'll show you how to set this up for the Tiller Budget Dashboard, with no programming knowledge required.

The high level steps are:

  1. Add our script that fetches your Budget sheet and emails it.

  2. Set up how often and when to send the email.

daily-budget-email.png

At the bottom of this post we also explore a few ways you can customize the script if you’d like to nerd out and email yourself a different sheet from one of your Tiller finance spreadsheets at regular intervals.

Setting up the Google Script

1. First, you create a new Google Script. In your spreadsheet, click on Tools then Script Editor… which will open a separate window.

Note: When the script editor tool opens, you might see some existing scripts. Just ignore them.

image8.gif

2. Create a new script by clicking on File , then New, then Script file.

image2.gif

3. You will then be asked to name the script. Call it Email.gs and click OK.

image1.gif

4. A new script file will appear in the large text box on the right side. Delete the default script which says:

function myFunction() {
}

Now, your script should be blank. All scripts used by your spreadsheet are listed on the left navigation area of this page.

5. Copy and paste the script below into the script editor on the right. Don't worry, you don't need to understand the code. You can also download a .txt version of the script here.


function sendEmail() {

// ====================================================================================
// This script is customized to work with Tiller's Budget Dashboard Sheet Version 1.0.0
// No changes are required below.
// 
// The script can be customized for other sheets by adjusting the settings below.
// Fill in this top section with your custom information
// ====================================================================================

// Only change the values INSIDE the " " (double-quotes)
// And make sure the lines end with a ;

// By default, the script will send an email to the owner of the sheet (your Tiller email address). 
// But you can send it to a custom email or multiple custom emails. If you want to send it to yourself and someone else you’ll need to explicitly add both of those email addresses, separated by a comma, to the customEmail setting below. 
//
//      Default setting
//      var customEmail = "none";
// 
//      Single email example
//      var customEmail = "test@test.com";
//
//      Multiple email example - each email address must be separated by commas
//      var customEmail = "first@test.com,second@test.com";

  var customEmail = "none";


// By default, the script uses the sheet named Budget
// But you can enter the name of any sheet you want to email
// Make sure to match the name exactly and with the same upper and lower case letters
//      Default setting
//      var sheetName = "Budget";

  var sheetName = "Budget";
 
// By default, the script uses the email subject "Tiller Budget Dashboard Email"
// But you can use any subject line you want
//      Default setting
//      var subject = "Tiller Budget Dashboard Email";

  var subject = "Tiller Budget Dashboard Email";

// You can set the alignment method for the cells in the email
// The default Budget sheet uses the custom alignment
// 
//    var alignment = "left"; puts all the columns aligned left
//    var alignment = "right"; puts all the columns aligned right
//    var alignment = "custom"; let's you set the set the alignment for each column

  var alignment = "custom";
  
// If you set alignment to "custom", set a value [left or right or skip] for each column in your sheet separated by a comma.
// The skip settings lets you skip a column so it won't display
// Make sure each column has a value or else it won't work
// This is the default setting for the Budget sheet
//     var customAlign = ["skip","left","skip","right","right","right","skip","skip","right"];

  var customAlign = ["skip","left","skip","right","right","right","skip","skip","right"];
  
// The email can display call rows and columns OR you can set the number of rows and columns manually
// To display all rows and column, use
//    var rowDisplay = "all";
//    var columnDisplay = "all"
//
// To manually set the number of rows or columns, enter the number you want
// Don't use the letter for the column. Count the number of columns instead.
// If entering a number, you don't need quotes around it.
// The default settings for the Budget email are:
//  var rowDisplay = "all";
//  var columnDisplay = 9;

  var rowDisplay = "all";
  var columnDisplay = 9;
  
// This setting is used for the default Budget template email
// By default it is set to true
// If you are using this for a different sheet, set it to false
// Quotes are not needed around the word true or false
// Default
//     var customBudgetSheet = true;

  var customBudgetSheet = true;
  

// ================================================================
// This ends the configuration section
// DO NOT make any changes below unless you know what you are doing
// ================================================================

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);

  var range = sheet.getDataRange();
  var values = range.getDisplayValues();
  
  // this gets an array of font weights, normal or bold, for each cell
  var weights = range.getFontWeights();
  
  // this is used for the budget template to determine if a row is blank
  // it checks column B for a category name
  var blankRowCount = 0;
   
  if (rowDisplay == 'all')
      { rowDisplay = range.getLastRow(); }
      
  if (columnDisplay == 'all')
      { columnDisplay = range.getLastColumn(); }
  
  var message = '';
  
  // only display the custom message for the default budget tempalte
  if (customBudgetSheet)
    { 
  // this custom message is for the budget template
  var customBudgetMessage = "<b>" + values[6][11] + "</b> needs Categorizing<br />\n" +
             "<b>" + values[8][10] + "</b> are waiting for me to categorize.<br />\n" +
             "Net Cash Flow: <b>" + values[14][11] + "</b><br />\n" + 
             values[16][10] +  " " + values[17][10] +"<br />\n" + 
             values[18][10] + "<br />\n" + 
             "Expense Budget Remaining: <b>" + values[23][11] + "</b><br />\n" +
             values[25][10] + "<br />\n" + 
             values[26][10] + "<br />\n"; 
    
    message = message + customBudgetMessage;
    }
    
  // this starts the table for the cells
  message = message + "<table rules='all' style='border-color: #666;' cellpadding='5'>\n";
  
  for (var i = 0; i < rowDisplay; i++ ) 
  {  
    // Test if: the category in column B , values[i][1] is blank AND
    //          there was a blank row in the prior row
    //          and this is greater than row 7 because earlier rows have 2 empty lines
    //          and this is the default Budget sheet
    // If all that is true, don't display any row
    // This solves the problem of empty blank rows on the bottom of the email
    
    if (values[i][1] == '' && blankRowCount == 1 && i > 8 && customBudgetSheet)
    {
     // don't display row
    }
    else
    { // display the row 
      message = message + "<tr>\n";
    
      for (var j = 0; j < columnDisplay; j++ ) 
      {
      
        if (customAlign[j] != 'skip')
        {
          message = message + "<td style='text-align: ";
        
          if (alignment == 'custom')
          {
             message = message + customAlign[j];            
          }
          else
          {
             message = message + alignment;
          }
             
          message = message + "; font-weight: " + weights[i][j] + ";'>" + values[i][j]   + "</td>\n";
        }  // ends if no skip
       } // ends the for loop for each column
      
      message = message + "</tr>\n"; 
      
      // test of no category for the budget template
      if (customBudgetSheet)
      {
        if (values[i][1] == '') 
           {blankRowCount = 1;} 
        else 
           {blankRowCount = 0;}
      }
    }
  }
  message = message + "</table>\n";
  // use the users email or the custom Email
    if (customEmail == 'none')
     {var email = Session.getActiveUser().getEmail();}
    else
     {var email = customEmail;}
  
  // send the email
  MailApp.sendEmail({
     to: email,
     subject: subject,
     htmlBody: message});    
}


6. Finally, you’ll need to save the script. Open the File menu then click Save.

image5.gif

7. Next, we’ll test it out. Click on Run, then sendEmail.

image9.gif

8. The first time you run the script, you’ll need to give your Google account permission to do it. The "Authorization required" window will appear. Choose "Review Permissions." Google will ask which account you want to use. Choose the Google account you use with Tiller.  Then, click the ALLOW button so the script is authorized to send email.

image7.gif

9. Check the inbox of the email address use with Tiller. By default, the script will automatically send to the email address that you choose in the Google permissions screen. If you don't see it come in after a moment, check your SPAM folder.

Note: If you’d like to have this sheet sent to yourself and someone else each day (or whatever interval you choose) you’ll need to configure the customEmail settings in the Google Script itself at line 26. See the notes at the bottom of this post about customizing your Google Script.

Setting Up Google Script To Send The Email Automatically Every Day

Next, we’ll set up the email to have it sent automatically each day, week, month or whatever interval you choose. This is done by setting a time-based trigger that defines when you want to receive the email.

1. Click on the clock icon above the script. That will open up a "Current Project's Triggers" window. Then click the link that says, "No triggers set up. Click here to add one now."

image4.gif

2. Next, you’ll select how often you want to get the email.  

  1. Under the "Run" dropdown, select sendEmail to run your customized script.

  2. Under Events, keep the Time-driven setting.

  3. Instead of "Hour timer", select Day timer for the script to run every day.

  4. Then pick the hour when you want to email to be sent, 7am to 8am in this example.

That’s all folks! Now you’ll get your Tiller Budget sheet sent to you automatically every morning so you can review progress on your budget and quickly see how many new transactions need to be categorized. If you want to get the email at a different time or frequency, adjust the Trigger settings.

As always, if you have any questions, feedback, or need help getting this set up reach out to support@tillerhq.com or use the chat window in the lower right corner of this page.

Customizing the Google Script for Your Daily Automatic Email Summary

What if you want to send a different sheet from one of your Tiller spreadsheets to your inbox every day? Perhaps you want to also send this daily email to your spouse or business partner. You can do this by making some changes to the Google Script.

The following settings can be adjusted inside the script. More detailed information about how to use these settings is available in the comments at the top section of the script where these configurations are adjusted.

Comment lines start with two slashes, like this: //

  • customEmail: Set a custom email to address. If you want to send the email to yourself and someone else you’ll need to explicitly add both of these email addresses in this setting.
  • sheetName: Set which sheet to email. This is case sensitive.
  • subject: Change the subject line in the email.
  • alignment: Align cells left, right or use a custom alignment per column.
  • customAlign: Choose whether to align each column to the left, right or to skip the column.
  • rowDisplay: Set how many rows to display.
  • columnDisplay: Set how many columns to display.
  • customBudgetSheet: Set whether you are using the default Budget template or a different sheet.

More information about Google Scripts

  • If you want to learn more technical information about using Google's Script service for Spreadsheets, read more here.
  • If you want to see how you can send a sheet as a pdf or csv attachment, check out this tutorial.