Skip to content

Instantly share code, notes, and snippets.

@jasonrdsouza
Last active March 3, 2021 14:23
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save jasonrdsouza/2f7dd951cd83b6f6a206830d3c657d9b to your computer and use it in GitHub Desktop.
Google AppsScript script to automate splitting expenses and sending a monthly email requesting payment
/*
* Script to automate sending the monthly debt emails.
*/
var EMAIL_RECEPIENT = "example@gmail.com"
var COLUMNS = {
"date": 0,
"paid": 1,
"total": 2,
"amount_owed": 3,
"mortgage": 4,
"condo_fee": 5,
"daycare": 6,
"electricity": 7,
"gas": 8,
"water_sewer": 9,
"internet": 10,
"groceries": 11,
"car_gas": 12,
"miscellaneous": 13,
"notes": 14
};
var EMAIL_BODY_TEMPLATE = "Total Amount Owed: %.2f\n\n" +
"Breakdown:\n" +
"- Mortgage: %.2f\n" +
"- Condo Fee: %.2f\n" +
"- Daycare: %.2f\n" +
"- Electric: %.2f\n" +
"- Gas: %.2f\n" +
"- Water/ Sewer: %.2f\n" +
"- Internet: %.2f\n" +
"- Groceries: %.2f\n" +
"- Car Gas: %.2f\n" +
"- Miscellaneous:\n" +
" - Amount: %.2f\n" +
" - Notes: %s\n\n" +
""
function sendDebtEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var currentRow = sheet.getLastRow(); // expects that the last filled out row is the one that we want to email about
var r = sheet.getRange
var range = sheet.getRange(currentRow, 1, 1, 15)
var values = range.getValues()[0]
var debtDate = values[COLUMNS["date"]]
var amountOwed = values[COLUMNS["amount_owed"]]
Logger.log("Sending email for date: %s for $%s.", debtDate, amountOwed)
var emailSubject = Utilities.formatString("%s Debt", Utilities.formatDate(debtDate, "GMT", "yyyy MMMM"))
var emailBody = Utilities.formatString(EMAIL_BODY_TEMPLATE, amountOwed,
values[COLUMNS["mortgage"]],
values[COLUMNS["condo_fee"]],
values[COLUMNS["daycare"]],
values[COLUMNS["electricity"]],
values[COLUMNS["gas"]],
values[COLUMNS["water_sewer"]],
values[COLUMNS["internet"]],
values[COLUMNS["groceries"]],
values[COLUMNS["car_gas"]],
values[COLUMNS["miscellaneous"]],
values[COLUMNS["notes"]])
MailApp.sendEmail(EMAIL_RECEPIENT, emailSubject, emailBody)
}
// Add a menu item for sending the debt email
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Send Debt Email', 'sendDebtEmail')
.addToUi();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment