A simple Numbers workflow
June 17, 2014 at 2:23 PM by Dr. Drang
Here’s a simple AppleScript I should’ve written a while ago. It saves me a few steps when creating and filling out a new expense report.
As I’ve mentioned before, I have a simple two-table Numbers template for my expense reports. The table in the top right has just two cells for the date of the report and its label; the table that takes up most of the sheet has the usual cells for the expenses themselves.
Once upon a time, I had an alias for the template file in my Dock. I could click on it and Numbers would launch (assuming Numbers wasn’t already running—usually a safe bet with me) and open a new document formatted according to the template. At some point—possibly with Mavericks, possibly with the more recent versions of Numbers—that stopped working. Clicking the template file on the Dock would launch Numbers, but it wouldn’t create a new file from the template.
So for the past several months, I’ve been making expense reports this way:
- Launch Numbers.
- Tell it I want a new file.
- Scroll down through the list of templates to select my expense report template.
- Click in the date field to enter today’s date.
- Click in the main table to start entering expenses.
While doing this today I realized how silly it was to keep repeating those steps, so I wrote this AppleScript, which I call “Open Expense Template”:
applescript:
1: set today to current date
2: tell application "Numbers"
3: make new document with properties {document template:template "Expenses"}
4: -- delay 2 -- might have to wait for Numbers to launch
5: tell document 1
6: tell sheet 1
7: tell table 1 to set value of cell 1 of column 2 to today
8: tell table 2 to set selection range to range "A2:A2"
9: end tell
10: end tell
11: activate
12: end tell
It does all five steps in one go. I have it saved in a folder that LaunchBar indexes, so I can run it by typing “oet” or just “oe” in LaunchBar. When the script finishes, the expense report file looks just like the screenshot above. The date of the report is entered and the top left cell of the expense table is selected, ready to be filled.
You might be wondering about a few things:
- Why don’t I use a formula like
=TODAY()to enter the date of the report? Because that formula updates the cell every time the spreadsheet is opened, and I want static text. Maybe there’s a function that maintains the current date, but I don’t know of it, and I’m not sure I’d use it if I did. Static text is easy to change if I end up not submitting the report for a day or two. - Why is there a commented
delaycall in Line 4? I’ve never understood how AppleScript controls its steps. Sometimes it waits for a command to finish before moving on, sometimes it doesn’t. Thedelayline was a bit of defensive programming I threw in there, anticipating that I’d need the script to wait for Numbers to launch. In my testing, the line hasn’t been needed, but I’m keeping it there anyway, commented out for now, in case I need the delay when I run the script on my MacBook Air instead of my iMac. - Isn’t it odd to put the
activateline at the end instead of the beginning? Yes, but testing showed that that’s where I needed it to be. I first wrote the script withactivateas Line 3, but I found that Numbers wasn’t the frontmost application when the script finished. I have no idea why and am not interested in trying to find out. Forget it, Jake. It’s AppleScript.
