Bunch, AppleScript and Excel

I work in a professional services job, and most of my time is billable to client projects. Our web-based time tracking software is a little bit inconvenient to use, so I track my time in an Excel spreadsheet throughout the day and enter it in my timesheet at the end of the day. (And since Excel is doing the math, I know I have an accurate accounting of my time, free of the inevitable calculation errors I would make.) The spreadsheet looks like this:

I keep one spreadsheet for each week in a folder structure like this: ~/Documents/Time/<Year>/<Month>.

The nature of time tracking, at least in my case, is that it’s necessary but tedious work with little opportunity for automation. However, I saw a couple of obvious opportunities for automation in this system: creating the weekly spreadsheet and opening it to the correct worksheet every day. I use Bunch to open my usual apps and perform some other setup tasks when I log in to my MacBook in the morning, so I just added this to the same Bunch file.

Step 1: Create the weekly file if it doesn’t exist

I keep a template in ~/Documents/Time/Time.xlsx, so each week I just need to copy that file to the appropriate directory and give it the correct name. This is easily accomplished with a shell script that I named time.sh.

#!/bin/zsh

TIME_DIR="$HOME/Documents/Time"
EXCEL_TEMPLATE="$TIME_DIR/Time.xlsx" # Template for the weekly Excel file

# Get the year and month of the most recent Monday (the Monday of the current week).
# This will be the directory where the current week's file is stored.
YEAR_MONTH=$(date -v -Mon "+%Y/%m")

# Year and month subdirectory, e.g., $HOME/Documents/Time/2022/01
MONTH_DIR="$TIME_DIR/$YEAR_MONTH"

# Get the month and day of the most recent Monday (the Monday of the current week)
# and construct the file name of the weekly file.
WEEKLY_FILE=$(date -v -Mon "+Week of %m-%d.xlsx")

if [ ! -d "$MONTH_DIR" ]; then
   # Create $MONTH_DIR if it doesn't exist.
   mkdir -p "$MONTH_DIR"

   # If $MONTH_DIR doesn't exist, we know the file for this week doesn't 
exist,
   # so we need to create it by copying $EXCEL_TEMPLATE.
   cp "$EXCEL_TEMPLATE" "$MONTH_DIR/$WEEKLY_FILE"
else
   # $MONTH_DIR exists. # Create $WEEKLY_FILE if it doesn't exist.
   if [ ! -f "$MONTH_DIR/$WEEKLY_FILE" ]; then
     cp "$EXCEL_TEMPLATE" "$MONTH_DIR/$WEEKLY_FILE"
   fi
fi

# Run AppleScript script to open the file to today's worksheet.
/usr/bin/osascript "$HOME/Documents/Bunches/OpenTimeSheet.scpt" "$MONTH_DIR/$WEEKLY_FILE"

exit

The line YEAR_MONTH=$(date -v -Mon "+%Y/%m") gets the year and month of the most recent Monday in the format YYYY/MM. The $MONTH_DIR variable is then set to the current month’s directory, for example ~/Documents/Time/2022/02. (It’s really the month of the most recent Monday, which could be the previous month when a week crosses a month boundary.) Then the $WEEKLY_FILE variable is set to the string Week of MM-DD.xlsx, where MM and DD are the month and day of the most recent Monday.

Next the script checks for $MONTH_DIR and creates it if it doesn’t exist. If $MONTH_DIR does exist, the script checks for $WEEKLY_FILE and creates it if it doesn’t exist.

Step 2: Open the file to today’s worksheet

As the final step, the shell script runs an AppleScript script that opens the file and activates today’s worksheet:

/usr/bin/osascript "$HOME/Documents/Bunches/OpenTimeSheet.scpt" "$MONTH_DIR/$WEEKLY_FILE"

This is the AppleScript script:

on run argv
	set inputPath to item 1 of argv
	set filePath to POSIX file inputPath
	set currentDate to (current date)
	set today to (weekday of currentDate) as string
	
	if (today  "Saturday") and (today  "Sunday") then
		tell application "Microsoft Excel"
			open filePath
			set ws to worksheet today
			activate object ws
		end tell
	end if
end run

This script takes the path to this week’s spreadsheet as input. The shell passes the path to AppleScript ($MONTH_DIR/$WEEKLY_FILE). AppleScript gets the path (line 2) and converts it to a POSIX file object (line 3). (The input is the path as a string, which AppleScript can’t work with unless it’s converted to a file object.) Next it gets the weekday of the current date (lines 4 and 5). Then it tells Excel to open the file (line 9) and activate the worksheet with the same name as the current day (lines 10 and 11). (Since the file doesn’t normally contain worksheets for Saturday and Sunday, I added the check in line 7 just in case I log in to my work laptop on the weekend.)

All of this could’ve been done in AppleScript, but the shell is much more efficient at working with files and folders, so I decided to let the shell handle everything except opening the Excel file to the correct worksheet (which only AppleScript can do).

Step 3: Automate it with Bunch

I have a bunch called main.bunch that performs my daily setup, so I added this to that file:

# Open weekly time file in Excel.
$ time.sh

This just runs time.sh every time the bunch runs.