Introduction

Up front, I have problems with procrastination. The last few years in graduate school have made me prioritize certain things MUCH better. However, in my personal life, I still struggle. I continue to put off things that I would rather do later. A prime example of this is my role as utility-bill-payer in my current living situation.

It is my duty to pay our house’s gas, internet, trash, and electric/water bills every month. I am reliable enough for this (especially with the convenience of automatic payments). My struggle is the monthly task of rounding up these payments, calculating the totals owed by my respective roommates, and the herculean task of texting them the breakdown for the month. Lately, I have been contemplating fun, useful projects to work on using the beauty of programming languages and I realized this is the perfect thing to do.

The plan? Make this process as easy as possible for me. With a little trial and error, I’ve done it.

This project consists of:

• A spreadsheet to store the monthly breakdown of utility payments (unfortunately, this still has to be done by hand).

• An R script to pull this spreadsheet in and send a custom text message that uses the most recent month’s total and roommate share amount (along with a breakdown of each bill amount).

• A method to automatically run this process without any reliability on my behalf (insert cron jobs).

The Spreadsheet

This was a task I already had mostly completed. At the beginning of this year, I created a very basic spreadsheet in Google Sheets to track my utility bills. One table consists of the data in a tidy format, including columns for the month, bill type, amount, and date paid. A second table creates a sum from the first table giving the date to notify (the first of the month after the month in which the bill was paid), the total amount, and the amount owed by the roommate (one-third split on the total).

Because each bill comes from a different place and my email isn’t easily accessible to a programming language, I still have to update this spreadsheet by hand whenever I am notified of a payment. And for my purposes, I was ok with this!

R Script

The script for this project has three main tasks:

1. Import the data from Google Sheets.

2. Create the text of the message.

3. Send the text message.

library(googlesheets4)
library(dplyr)

gs4_auth(email = "your email here")

data <- read_sheet("your_url_here")

month <- c("Feb", "Feb", "Feb", "Mar", "Mar", "Mar")

bill_type <- c("Gas", "Water", "Internet", "Gas", "Water", "Internet")

amount <- c(50, 62, 25, 55, 70, 25)

due_date <- c("2/1/2024", "2/4/2024", "2/5/2024", 
              "3/1/2024", "3/5/2024", "3/6/2024")

table1 <- data.frame(month, bill_type, amount, due_date) |> 
  mutate(due_date = lubridate::mdy(due_date))

table1

  month bill_type amount   due_date
1   Feb       Gas     50 2024-02-01
2   Feb     Water     62 2024-02-04
3   Feb  Internet     25 2024-02-05
4   Mar       Gas     55 2024-03-01
5   Mar     Water     70 2024-03-05
6   Mar  Internet     25 2024-03-06

table2.1<- table1 |> 
  summarise(.by = month,
            total = sum(amount),
            third = total/3) 

notify <- c("3/1/2024", "4/1/2024")

table2.2 <- data.frame(notify) |> 
  mutate(notify = lubridate::mdy(notify))

table2 <- cbind(table2.1, table2.2)
table2

  month total    third     notify
1   Feb   137 45.66667 2024-03-01
2   Mar   150 50.00000 2024-04-01

# current_month <- lubridate::month(Sys.Date())
current_month <- 4 # for our example

filtered_sum <- table2 |> 
  mutate(notify = lubridate::month(notify)) |> 
  filter(notify == current_month)

monthly_total <- filtered_sum$total

third <- filtered_sum$third

breakdown <- table1 |> 
  mutate(month_num = lubridate::month(due_date)) 

breakdown_amounts <- breakdown |> 
  filter(month_num == (current_month - 1)) |> 
  select(bill_type, amount)

 message <- paste("The total of our utility bill for the month of", 
                  filtered_sum$month, "is:", monthly_total, 
                  "\nA third of this total is:", round(third, 2), 
                   "\n\nHere is a breakdown of the bill:",
                   paste0("\n", breakdown_amounts[1,1], ": ",
                     breakdown_amounts[1,2], "\n",
                     breakdown_amounts[2,1], ": ",
                     breakdown_amounts[2,2], "\n",
                     breakdown_amounts[3,1], ": ",
                     breakdown_amounts[3,2]
                   )
 )

cat(message)

The total of our utility bill for the month of Mar is: 150 
A third of this total is: 50 

Here is a breakdown of the bill: 
Gas: 55
Water: 70
Internet: 25

send_text <- function(message, buddy){
  
  for(i in buddy){
    system(paste('osascript -e \'tell application "Messages"\' -e \'send "', message, '" to buddy', i,  'of (service 1 whose service type is iMessage)\' -e \'end tell\''))
  }
  
}

buddies <- c("\"phonenumber1\"", "\"phonenumber2\"")

send_text(message, buddies)

The Cron Job

Now that the hard part is over, we can simply create a cron job to run this script directly from the command line on the first of every month.

Important: make sure the following is at the top of your saved R script:

#!/usr/local/bin/Rscript

To open your cron tab, in the terminal type the following command:

export VISUAL=nano; crontab -e

As an added tip, you can save this command as an alias in your .zshrc file to make it easier to quickly access.

Once the cron tab is open, you have to set up the job and specify how often you want it to run. The following runs on the first of every month at 9 AM. The command navigates to my home directory, and then executes my R script which I have named “utes_notif.R”.

0 9 1 * * cd ~; ./Desktop/R/utility_notification/utes_notif.R

Other Considerations

Lastly, I want to note a small consideration when setting up an automated process like this. The cron job will run in this form only if the computer is awake during the scheduled time.

To work around this, you can utilize the battery options in your Mac’s system preferences. I have scheduled my computer to wake up every day at 9 AM for about five minutes. This was the easiest work around for me. However, I am certain there are other options to achieve the same goal. One option may be to use alternative scheduling tools like cronwake or anacron.