Docs: Bill due date email reminders

[heather]

About

This is an experimental sheet and script you can use to have a Google Sheet email you when a bill is coming up.

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

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. This will navigate to a Google Apps Script project triggers page
8. Click the “Add Trigger” button in the lower right of this page
9. Choose Time-driven for the Event Source, Day timer for the type, and select a time range to finalize the trigger’s settings.
10. 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.

If you’d like to set this up in one of your Tiller Money-powered Google Sheets

1. Right click the Reminders tab and choose “copy to…”
2. Browse or search for your spreadsheet or copy the URL of your Google Sheet into the bottom of the dialog.
3. Click Select.
4. Open your Tiller Money 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.

Troubleshooting

If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this template in the Google Sheets category.

Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.