Last active
March 3, 2021 14:23
Star
You must be signed in to star a gist
Google AppsScript script to automate splitting expenses and sending a monthly email requesting payment
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
* 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