Brenden Smith - I Made R Text For Me

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:

Import the data from Google Sheets.
Create the text of the message.
Send the text message.

Getting the Data

First, we can call the libraries we will need.

Code

library(googlesheets4)
library(dplyr)

The package googlesheets4 made this project incredibly easy.

You can connect to your Google account using gs4_auth.

Code

gs4_auth(email = "your email here")

Then, you can import your sheet.

Code

data <- read_sheet("your_url_here")

To demonstrate what my data looked like, I will create a demo data set to work with in this example.

Code

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

Code

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

Creating the Message

Because my real data has all of the month’s data in it, we need to filter to get the right amounts to send in our text. We can use Sys.Date to get the current date, and lubridate::month to make the date a numeric value representing the month.

Then we can do the same for our tables to filter by. Remember I will have this script executing at the first of every month to report what the costs were for the previous month.

Code

# 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)

Now that we have the amounts we need, we can construct a message.

Code

 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

Sending the Message

Next, we text.

The method I am using is only for iMessage devices, and is really only possible from a Mac. This method is taken from a very helpful Stackoverflow post. There is probably a workaround for other devices and sending through SMS, but that is not the focus of this post.

We can send our text with an Apple Script. Here is a function to do just that. This is slightly adapted from the Stackoverflow answer and allows for separate texts to multiple recipients.

Code

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\''))
  }
  
}

Then we can simply plug in the rest. But be warned: running this command will send a text! Be careful when testing this out.

Code

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:

Code

#!/usr/local/bin/Rscript

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

Code

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”.

Code

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.