Google AppsScript script to automate splitting expenses and sending a monthly email requesting payment

expenses_appscript.js

/*
 * Script to automate sending the monthly debt emails.
 */

var EMAIL_RECEPIENT = "[email protected]"

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();
}