Google App Script to help split expenses between two people

expense_splitter.gs

/// Expense Splitter
/// Copyright 2019 George Hahn
/// License: MIT (https://opensource.org/licenses/MIT)
///
/// Google App Script to help split expenses between two people.
///
/// Run monthly. Emails a summary of old expenses and locks down the old sheet.
/// Creates a new expense sheet from the template sheet.
///
/// Expense split running total should be tracked in cell B1 of the template sheet.
/// Cells C1 and D1 should be left blank; they will be filled in after each month.
/// Otherwise, you can design your sheet however you like.
/// 
/// Example setup:
///   Columns:
///     A: amount, B: notes, C: notes, D: payer, E: hidden (calculations)
///
///   Hidden column E: `E4:E` summed to E1; equation for each expense:
///     `=IF(D4 = "Person B", A4, IF(D4 = "Person A", -1 * A4, 0))`
/// 
///   Running total:
///     `=IF(E1 = 0, "Nothing is owed", CONCAT(IF(E1 > 0, "Person A owes Person B $", "Person B owes Person A $"), ABS(E1 / 2)))`
///
///   Setup a time based trigger to call `rollOver` once per month.
///

// Configuration
var timezone = 'GMT-0';
var template_name = 'template';
var email_addresses = ['[email protected]'];

/// Wrapper around Google's sendmail that handles multiple recipients
function send_email_alt(subject, message) {
  for (var idx in email_addresses) {
    var active = email_addresses[idx];
    var cc = email_addresses.filter(function(addr) { return addr != active; }).join(',');
    MailApp.sendEmail({
      to: active,
      cc: cc,
      subject: subject,
      // plaintext body
      body: message,
      name: "Shared Expense Spreadsheet"
    });
  }
}

/// Wrapper around a different G sendmail function that handles multiple recipients internally
// GH: I'm pretty sure `send_email_alt` above would function fine with similar usage, but it
//     isn't explicitly noted in the docs.
function send_email(subject, message) {
  MailApp.sendEmail(
    email_addresses.join(','),
    subject,
    message,
    {
      name: "Shared Expense Spreadsheet"
    }
  );
}

/// Insert a checkbox at the given location
function insertCheckbox(sheet, location, value) {
  if (value === undefined) {
    value = false;
  }

  var enforceCheckbox = SpreadsheetApp.newDataValidation();
  enforceCheckbox.requireCheckbox();
  enforceCheckbox.setAllowInvalid(false);
  enforceCheckbox.build();

  location.setDataValidation(enforceCheckbox);
  location.setValue(value);
}

/// Google apps script entrypoint
///
/// This function is called by a time-based trigger on the first hour of every month.
function rollOver() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var today = new Date();
  var lastmonth = new Date();
  lastmonth.setMonth(today.getMonth() - 1);
  
  var old_month = Utilities.formatDate(lastmonth, timezone, 'MMMM yyyy')
  var new_month = Utilities.formatDate(today, timezone, 'MMMM yyyy')
  
  // Wrangle old sheet
  var old_sheet = doc.getSheetByName(old_month);
  if (old_sheet != null) {
    // Set C1 to "Paid?"
    var c1 = old_sheet.getRange('C1');
    c1.setValue('Paid?');
    
    // Set D1 to false checkbox
    var d1 = old_sheet.getRange('D1');
    insertCheckbox(old_sheet, d1);
    
    // Protect sheet
    var protection = old_sheet.protect();
    protection.setDescription('This month is in the past');
    protection.setWarningOnly(true);
    
    // Unprotect paid checkbox
    protection.setUnprotectedRanges([d1]);
    
    // Email final split
    var split = old_sheet.getRange('B1').getValue();
    send_email(Utilities.formatString('Expense split for %s', old_month),
               Utilities.formatString('Month of %s: %s', old_month, split));
  } else {
    // TODO: Consider emailing this error
    Logger.log("Could not wrangle old sheet: sheet does not exist %s", old_month);
  }
  
  var template_sheet = doc.getSheetByName(template_name);
  if (template_sheet != null) {
    // Create new sheet from template
    doc.setActiveSheet(template_sheet);
    var new_sheet = doc.duplicateActiveSheet();
    
    // TODO: Consider catching errors on this call or verifying that a sheet of this name does not already exist
    new_sheet.setName(new_month);
    new_sheet.activate();
    
    // Protect running total calculation
    var protection = new_sheet.getRange('B1').protect();
    protection.setWarningOnly(true);
    
    // Move sheet all the way to the left
    doc.setActiveSheet(new_sheet);
    doc.moveActiveSheet(1);
  } else {
    // TODO: Consider emailing this error
    Logger.log("Could not create new sheet: template sheet does not exist");
  }
}