How to Send an Automatic Email Reminder from a Google Spreadsheet

If you’re like most people your bill due dates are likely scattered throughout the month. Beyond your mortgage, electricity, and internet bills you may have loan repayment or credit card due dates. These can be challenging to manage amidst life distractions and increasingly packed schedules.

We have just the solution: A Google Sheet that allows you to set up bill pay automatic email reminders. You’ll get an email reminder of an upcoming bill on a customizable schedule. Heck, you could use this thing to send you a reminder on just about anything. Having it in a Google Sheet allows you to see it all in one place and you don’t need to worry about adding it to your Google Calendar each month.

Check out the animation at the bottom for a quick visual to go along with this how-to post. 

bill-reminders.png

Creating Your Reminders Sheet

  1. Open the Reminders sheet template here.
  2. Open the file menu.
  3. Click “make a copy.”
  4. Give it a title and choose where it should be stored on your Google Drive.
  5. Click OK.

Setting Up Your Monthly Reminders

Now that you have the Reminders sheet you can easily configure it to meet your needs. It allows you to set the bill name, bill due date, and the day of the month you want an email reminder.

To customize your reminders:

  1. Enter the name of the bill in Column A. This name will be used in the email.
  2. Enter the day the bill is due in Column B. This will also be used in the email to remind you of the due date.
  3. Select the “Reminder Day” of the month in column C. This is the day of the month you want to receive the email reminder. So for a bill that’s due on the 15th of the month and you want an reminder 2 days ahead of that you’d choose 13 for the Reminder Day. If you want the bill reminder to be sent on the last day of the month, use "Last". (Not every month has 31 days, so if you select Last, the sheet will correctly calculate the last day of the month whether it's the 28th, 29th, 30th, or 31st.
  4. Optionally, enter another email address to which you’d like reminders sent. This is great if you want to have reminders sent to your business partner, admin assistant, or spouse.

Important notes on using this sheet

Don’t modify any data in the “Next Reminder Day” in Column D. This is an automatically calculated date. If you overwrite the auto calculated dates here, you won't get any emails.

Reminder emails will automatically be sent to the sheet owner's email address on the day of the month indicated in column C. If you have multiple reminders configured to send emails on the same day of the month you’ll get a single email with details about all upcoming bills that are due where that day is the configured “Reminder day”.

Authorizing the Emails to Send On Your Behalf and Setting Up the Time Trigger

google-authorization.png

In order for all this to work you’ll need to authorize the script that’s working in the background to send you the reminder emails and set up a trigger to have the script check whether any reminders should be sent each day.

  1. Open the Tools Menu and select "Script Editor…"
  2. In the script editor, click the triangle (between the clock and bug icons) to run the script.
  3. The first time the script runs, Google requires that you Authorize it. An "Authorization required" window will pop up asking for your permission. Select "Review Permissions."
  4. Google will ask which account you want to authorize. Select the account you used to create a copy of the original reminders sheet.
  5. Click the Allow button so the script can view and manage your spreadsheets and send email as you.
  6. Click the "Trigger" icon. It looks like a clock.
  7. On the "Current project's triggers window, click on the "No triggers set up. Click here to add one now." link
  8. Choose Time-driven, Day timer, and select a time range to finalize the trigger’s settings.
  9. Click Save.

Testing the Reminders

If you’d like to test that it all works, temporarily set the “Reminder Day” (Column C) for one of your bills to the current day of the month on the Reminders sheet. Click the “run” button (looks like a play button) in the script editor to test it out (same button as in step 2 above). You should get an email pretty quickly. If you don't, check your SPAM folder and review the steps above. When you have confirmed it works, reset the date for the bill reminder you changed to test.

Now you’ve got your reminders all set up. You will automatically receive a reminder email for each bill that’s due based on the schedule you set. You can add, remove or edit the spreadsheet at any time and the email reminders will still continue to send.

Bill-reminders.gif

If you'd like to set this up in one of your Tiller spreadsheets:

  1. Right click the Reminders tab and choose "copy to..." 
  2. Browse or search for your Tiller spreadsheet or copy the URL of your Google Sheet into the bottom of the dialog.
  3. Click Select.
  4. Open your Tiller spreadsheet.
  5. Rename the new Reminders sheet (tab along the bottom) from Copy of Reminders to "Reminders" - double click the tab name to edit the name.
  6. Open the Tools menu at the top of the Google Sheet and choose "Script Editor" 
  7. Choose File > New > New Script File
  8. Name it myAlerts
  9. Paste in the contents of this script file and save it, then follow the steps in the Authorize The Emails... section above.