Expense reports and saving to iCloud

In last night’s post, I pointed out a deficiency in the script:

I wanted the script to save the document, but that caused nothing but grief. First, I wanted to be able to save it to iCloud, but no matter how I tried to get it to save into a subfolder of ~/Library/Mobile Documents, I got an error message, usually about permissions. Saving to my Desktop folder sometimes worked, but sometimes also led to permissions errors. My sense is that this is a sandboxing thing, and that I’ll never figure it out. So I gave up. Saving the document manually isn’t that hard.

This morning, I woke up to a solution from reader Andrew Kemp:

@drdrang Got Numbers to save in iCloud with folder (system attribute “HOME”) & “/Library/Mobile Documents/com~Apple~Numbers/Documents/“
  — Andrew Kemp (@ajwk100) Fri Nov 25 2016 11:19 AM

The system attribute command is simply a way of getting environment variables, so

applescript:
system attribute "HOME"

returns the Unix-style path to the user’s home directory. What Andrew is telling me here is to build a path to the iCloud folder on my Mac use the alternate syntax for saving a Numbers document in AppleScript. In other words, don’t specify the path to the file using AppleScript’s usual colon-delimited system, use the good ol’ Unix slash-delimited system.

I was skeptical, because I knew I’d tried Unix paths before and they failed. But I must have done something wrong in those previous attempts. Maybe I used a tilde, thinking it would expand to the home directory; maybe I tried to escape a space in one of the folder names and messed up. Whatever the problem was, it’s now gone. I’ve been able to run the updated script repeatedly from both of my Macs and it’s always saved the expense report to iCloud, just as I wanted.

Big thanks to Andrew for getting me straightened out.

Here’s the updated script in full:

applescript:
 1:  set today to (do shell script "date \"+%b %-e, %Y\"")
 2:  
 3:  -- get the project number from the user
 4:  display dialog "Project number:" default answer "GB"
 5:  set project to text returned of result
 6:  
 7:  -- get the receipt image files
 8:  set dfolder to ((path to home folder as text) & "Dropbox:") as alias
 9:  set receipts to (choose file with prompt "Select the receipt files" default location dfolder with multiple selections allowed)
10:  
11:  -- open the report template and set the date
12:  tell application "Numbers"
13:    activate
14:    set doc to (make new document with properties {document template:template "Expenses"})
15:    delay 1 -- might need to wait for Numbers to launch
16:    tell doc
17:      tell sheet 1
18:        tell table 3 to set value of cell 1 of column 2 to today
19:      end tell
20:    end tell
21:  end tell
22:  
23:  -- set up variables for placing images and entering values
24:  set x to 750
25:  set y to 50
26:  set i to 2
27:  
28:  -- cycle through all the receipt files
29:  repeat with imageFile in receipts
30:    -- get the file name w/o extension
31:    tell application "Finder"
32:      set filename to name of imageFile
33:      set fileExt to name extension of imageFile
34:      set filename to text 1 thru -((length of fileExt) + 2) of filename
35:    end tell
36:    
37:    -- extract the date, description, and amount from the file name
38:    set oldDelimiters to AppleScript's text item delimiters
39:    set AppleScript's text item delimiters to "+"
40:    set {rdate, desc, amt} to text items of filename
41:    set AppleScript's text item delimiters to oldDelimiters
42:    
43:    -- back to the spreadsheet
44:    tell application "Numbers"
45:      tell doc
46:        tell sheet 1
47:          -- add the image to the spreadsheet
48:          set thisImage to make new image with properties {file:imageFile}
49:          tell thisImage
50:            set position to {x, y}
51:            set height to 400 -- retains aspect ratio
52:          end tell
53:          -- enter the values
54:          tell table 2
55:            set value of cell i of column 1 to rdate
56:            set value of cell i of column 2 to project
57:            set value of cell i of column 3 to desc
58:            set value of cell i of column 4 to amt
59:          end tell
60:          -- update for the next receipt
61:          set x to x + 20
62:          set y to y + 20
63:          set i to i + 1
64:        end tell -- sheet 1
65:      end tell -- doc
66:    end tell -- Numbers
67:    
68:  end repeat
69:  
70:  -- save the report in iCloud
71:  set saveName to (do shell script "date \"+%Y%m%d-\"") & project & ".numbers"
72:  set savePath to (system attribute "HOME") & "/Library/Mobile Documents/com~apple~Numbers/Documents/"
73:  tell application "Numbers" to save doc in (savePath & saveName)

The new stuff is all at the bottom, Lines 70–73. You may be slightly confused by the path to iCloud. First, if you look in Finder, you’ll see that the “Mobile Documents” folder is weird—it doesn’t have a reveal triangle next to it.

Mobile Documents in Finder

If you double-click it, you’ll be taken to iCloud Drive, which has folders associated with various apps.

iCloud Drive folder on Mac

This view, unfortunately, doesn’t give you any sense of where these folders are on your computer. To get that, you have to use the Terminal:

bash:
$ cd ~/Library/Mobile\ Documents/
$ ls
<bunch of stuff>
com~apple~Automator
com~apple~CloudDocs
com~apple~Keynote
com~apple~Numbers
com~apple~Pages
com~apple~Preview
com~apple~QuickTimePlayerX
com~apple~ScriptEditor2
com~apple~TextEdit
com~apple~TextInput
com~apple~finder
com~apple~mail
com~apple~mobilemail
com~apple~shoebox
com~apple~system~spotlight
<etc.>
$ cd com~apple~Numbers
$ ls
Documents
$ cd Documents
<list of Numbers docs in iCloud Drive>

So in Unix terms, the path to your iCloud Drive numbers folder is

~/Library/Mobile Documents/com~apple~Numbers/Documents

The Finder hides the funny-looking stuff from you and makes it appear as if you’re looking directly at some off-site disk.

So now, after running the AppleScript (and giving it a project number of 11111), the results look like this:

Expense report after automatic saving

As you can see in the title bar, the document has been named according to the date and the project number, and it’s in iCloud, easily accessible from my Macs, my iPhone, and my iPad.

One of the many things I have to be thankful for is a cadre of readers like Andrew who fix my mistakes and point me in the right direction.


Automating expense reports

This is a relatively long post about a relatively short script. It synthesizes several small workflows I’ve used over the past few years, and to explain the synthesis all the parts have to be explained, too.

Time was when making an expense report was easy. I had a preprinted form that I filled out by hand, stapled receipts to, and turned in for reimbursement. When those expenses were billable to a client, a line item in the invoice describing all the expensed items was sufficient.

Sometime in the early 00s, this started to change. Certain clients insisted on seeing the original expense report and associated receipts. This was fine with me, but I wasn’t especially pleased with the way they did it. I’d sent off an invoice the old way, without receipts, and then follow up when payment was in arrears. “Oh, we have a policy of not paying until we have receipts for expenses,” I’d hear. This would piss me off because I’d never been told the policy, and it seemed clearly to be a way of slowing down payment. I decided to attach expense reports to every invoice that went out to forestall this delaying tactic.

At about this same time, my invoices switched from being pieces of paper sent in the mail to PDFs delivered via email. This meant scanning the receipts and assembling a final PDF for delivery from the individual PDF files for the invoice, the expense reports, and the receipts.

The iPhone changed how I handled paper receipts, especially when traveling. Apps like Readdle’s Scanner Pro, which rectify and boost the contrast of photographed documents automatically,1 meant I could scan receipts as I got them and didn’t have to worry so much about keeping the paper versions organized. (I still haven’t quite gotten comfortable with throwing them away right after I scan, but I’m sure that’ll come with time.)

Eventually, I evolved to the system I’ve been using for the past few years:

The most time-consuming part is the penultimate step. Reading the scanned receipts and entering the values into the spreadsheet certainly isn’t hard work, but it does require a lot of back and forth between the receipt images and the spreadsheet. This is the task I’ve automated, turning the gathering of the receipt files and the data entry into a single step.

As I said above, to understand how the automation works, you have to understand all the parts. Let’s start with my expense report template. It looks like this:

Numbers expense report template

From top to bottom, these tables are:

Because of the way they happen to be layered in the document, AppleScript sees the reminder table as Table 1, the main table as Table 2, and the date table as Table 3. This will be important when we get to the script.

By the way, because the reminder table has no business being in the final expense report, I delete it before exporting the report to PDF. Because I do this a lot, I made a Keyboard Maestro macro for it.

Delete common expenses macro

Now let’s look at how I set up Scanner Pro. In the settings, I have the default name set this way:

Scanner Pro settings

The name of the scan starts with the date on which it’s made, in yyyy-mm-dd format. Then there’s a place for the description of the expense and the amount. These are separated from the date and from each other by plus signs.

When I scan a receipt, Scanner Pro assigns the default name to it and gives me a chance to edit that name. I go in and change the desc and amt parts.

Receipt with name in Scanner Pro

I then tap the Share button and upload the scan to the Dropbox folder I set up for receipts. If, for some reason, I don’t have a good enough connection for the upload, that’s OK. The scan is saved to my phone, and I can upload it later.

Here’s the automation: When time comes to create an expense report, I run an AppleScript called “Expense Report,” and it does the following:

  1. Asks me for the project number.

    Asking for the project number

  2. Asks me to select all the receipt image files.

    Select image files

  3. Creates a new expense report, entering today’s date.

  4. Goes through each receipt file in turn, adding it off to the side of the main table and entering the date, project, description, and amount to the main table.

    Expense report after importing receipts

The receipt images are staggered to make them easy to drag around, rotate, and resize.

Because there are usually entries, like mileage and tolls, for which there are no receipts, this script doesn’t do everything, but it does the bulk of the tedious work.

Here’s the script itself:

applescript:
 1:  set today to (do shell script "date \"+%b %-e, %Y\"")
 2:  
 3:  -- get the project number from the user
 4:  display dialog "Project number:" default answer "G/B"
 5:  set project to text returned of result
 6:  
 7:  -- get the receipt image files
 8:  set dfolder to ((path to home folder as text) & "Dropbox:") as alias
 9:  set receipts to (choose file with prompt "Select the receipt files" default location dfolder with multiple selections allowed)
10:  
11:  -- open the report template and set the date
12:  tell application "Numbers"
13:        activate
14:        set doc to (make new document with properties {document template:template "Expenses"})
15:        delay 1 -- might need to wait for Numbers to launch
16:        tell doc
17:              tell sheet 1
18:                    tell table 3 to set value of cell 1 of column 2 to today
19:              end tell
20:        end tell
21:  end tell
22:  
23:  -- set up variables for placing images and entering values
24:  set x to 750
25:  set y to 50
26:  set i to 2
27:  
28:  -- cycle through all the receipt files
29:  repeat with imageFile in receipts
30:        -- get the file name w/o extension
31:        tell application "Finder"
32:              set fileName to name of imageFile
33:              set fileExt to name extension of imageFile
34:              set fileName to text 1 thru -((length of fileExt) + 2) of fileName
35:        end tell
36:        
37:        -- extract the date, description, and amount from the file name
38:        set oldDelimiters to AppleScript's text item delimiters
39:        set AppleScript's text item delimiters to "+"
40:        set {rdate, desc, amt} to text items of fileName
41:        set AppleScript's text item delimiters to oldDelimiters
42:        
43:        -- back to the spreadsheet
44:        tell application "Numbers"
45:              tell doc
46:                    tell sheet 1
47:                          -- add the image to the spreadsheet
48:                          set thisImage to make new image with properties {file:imageFile}
49:                          tell thisImage
50:                                set position to {x, y}
51:                                set height to 400 -- retains aspect ratio
52:                          end tell
53:                          -- enter the values
54:                          tell table 2
55:                                set value of cell i of column 1 to rdate
56:                                set value of cell i of column 2 to project
57:                                set value of cell i of column 3 to desc
58:                                set value of cell i of column 4 to amt
59:                          end tell
60:                          -- update for the next receipt
61:                          set x to x + 20
62:                          set y to y + 20
63:                          set i to i + 1
64:                    end tell -- sheet 1
65:              end tell -- doc
66:        end tell -- Numbers
67:        
68:  end repeat

Line 1 uses the date command to get today’s date in a format I like. I know AppleScript has its own date commands (and the spreadsheet itself knows how to format the date), but I feel comfortable with date so I use it.

Lines 3–9 handle the user interaction in a fairly standard way. The default project number is “G/B,” which stands for “general business.” The file picker starts in the main Dropbox folder, so I’ll always be just one folder level away from the receipt files.

Lines 11–21 creates a new Numbers document from the Expenses template and adds today’s date to it. I’m not entirely sure the delay in Line 15 is necessary, or that it’s set to the minimum value, but the script has never failed with a delay of 1 second, and I don’t mind the wait.

Lines 23–26 set the initial values for the x and y coordinates of the receipt images and the row number of the spreadsheet entries.

The rest of the script is a loop that processes each of the receipt files chosen in Lines 7–9. Lines 31–35 get the name of each file and strip off the extension. Lines 37–41 pull out the date, description, and amount from the file name. This is done through the usual “text item delimiters” rigamarole, and you can now see why I decided to separate the date, description and amount with pluses instead of hyphens.

Lines 44–66 return to Numbers to do the rest of the work.

Lines 48–52 put the image off to the right of the main table and resize it. The receipt images typically come in much larger than life sized, so it’s helpful to automatically shrink them down a bit. I was happy to learn that if you just change the height of the image in Numbers, the width changes to keep the aspect ratio constant. That eliminated the need for a few lines of math.

Lines 54–59 fill out a row of the main table, using the values extracted from the receipt file name.

Lines 60–63 update the coordinates for the upper left corner of the next image and update the row number for the next set of data entry.

I wanted the script to save the document, but that caused nothing but grief. First, I wanted to be able to save it to iCloud, but no matter how I tried to get it to save into a subfolder of ~/Library/Mobile Documents, I got an error message, usually about permissions. Saving to my Desktop folder sometimes worked, but sometimes also led to permissions errors. My sense is that this is a sandboxing thing, and that I’ll never figure it out. So I gave up. Saving the document manually isn’t that hard.

One thing that’s nice about this script is that I can run it remotely from my iPad using Screens connected to my office iMac.2 All the receipts are on the iMac because of Dropbox syncing, and I can then save the report to iCloud and switch to Numbers on my iPad to rearrange and resize the receipt images. This gives me a chance to get an annoying but obligatory task done at the end of a trip while I’m on the flight home.

I’ve thought about rewriting the script to act as a Service. That way, instead of selecting the receipt files from a dialog box, I could select them in the Finder first and then run the Service to make the report. I’m not sure that’s any more efficient, but it might be worth trying.

In any event, I now have a system for taking most of the drudgery out of a necessary task. It requires a bit of setup to get the files named right, but it’s really fun to watch the report build itself, especially when I have a dozen or more expenses to enter.

Update 11/25/2016 9:37 AM
An updated version of the script now saves the report to iCloud. Full details here.


  1. And is currently on sale for half off. Only $3. 

  2. Which is also having a 50% off Black Friday sale. Only $10 if you act fast. 


Identity theft for fun, if not profit

Today I engaged in a bit of identity theft, and it worked out really well, much better than being honest would have.

My mom moved out of her house and into a nursing home, and I’ve been getting her various banking and other accounts either shut down or reconfigured with a new address. This seldom goes quickly or easily, even though I can show I have power of attorney for her. Part of the problem is the justifiable and appropriate reluctance of companies to accept what I tell them, part is the usual incompetence one finds in customer service representatives, and part is clearly the policy of some companies to drag their feet in closing accounts that have recurring payments. Mom’s satellite TV provider was especially bad in the latter two areas.

Today’s target was Mom’s landline, and I ran into two bits of luck: the customer service rep from AT&T was very efficient and the account was still in Dad’s name. Mom never got around to changing it in dozen years since he died.

Because the account had a male name attached to it, I could call in and use Dad’s name without raising suspicion. And I was ready for all questions. I had the latest bill in front of me to handle the account details, and I knew his Social Security number and mother’s maiden name for the expected security questions. They didn’t ask for Grandma’s maiden name, but they did ask for the last four digits of the SSN, which I was able to rattle off quickly because I have it stored in a Secure Note in 1Password.1

Apart from the time I spent on hold at the beginning, the call took no more than three minutes—far and away the shortest dealing I’ve had. Crime pays.


  1. This is the Mac-centric portion of the post. And it’s a lifehack, to boot. Depending on your age, keep the SSNs, drivers license numbers, bank and insurance account info, etc., for your spouse, kids, and/or parents in a 1Password Secure Note, and you’ll always have it available when you need it. And you will need it. ↩︎


Automating Maps Favorites

Here’s a bit of automation I’m not particularly proud of, but it got the job done and saved me a lot of time in two ways. It’s the clearest evidence I can muster that you don’t have to be a good—or even halfway decent—programmer to take advantage of automation.

A couple of weeks ago, I needed to drive around an unfamiliar area to do a brief overview of a couple of dozen buildings that I was going to be inspecting in detail the following week. Because this pre-inspection was going to be done on a Sunday, I wanted it go as quickly as possible. I had the addresses of all the buildings, and I knew they were all in an area that covered only a few square miles, but because I didn’t know the area well, I couldn’t figure out the most efficient way to visit all the properties just by looking at the addresses.

The best way to plan my tour would be to have them all laid out on a map. Both Apple Maps and Google Maps make it easy to plot a single address, but they don’t make it easy to several addresses at once. In the past, I’d been able to do that through the Google Maps API, but that was many years ago, and I didn’t feel confident that what I knew back then would still work today.

Both Apple and Google Maps let you save locations, but it’s a manual process that requires several steps for each address. I decided to automate that process with a Keyboard Maestro macro that mimicked the steps I would take doing the job manually. I chose to use Apple Maps, because I prefer its user interface and because I was certain that addresses saved in Maps on my Mac would be available in Maps on my iPhone.

The way to get several addresses saved in Apple Maps is to add them to your Favorites list. Once they’re in your Favorites, they’ll appear automatically in any view on your iPhone that includes their location.

Maps with Favorites

(These are not the addresses I was going to inspect. I just chose a bunch of spots in Berwyn for the purposes of this illustration. Why Berwyn? To amuse any fans of Svengoolie in my readership.)

I started with a list of addresses in BBEdit, one per line. To add an address to Favorites, the macro performs the following steps, just like I would do:

  1. Select the next line in BBEdit.
  2. Copy it.
  3. Switch to Maps.
  4. Paste the address into Maps’s Find field and do the find.
  5. Click the information button in the flag that appears next to the pin.

    Found address in Maps

  6. Click the share button and choose Add to Favorites from the menu.

    Adding to Favorites

Although I can write it as six steps, it works out to many more steps in Keyboard Maestro, partly because things like “Select the next line in BBEdit” involve a few keystrokes, and partly because pauses are necessary at a few spots to allow interface elements to appear. As usual, it took a couple of trial runs to get the pauses right.

Add Address to Maps Favorites

If you think you might use this macro, you can download it.

Once I had the macro working, it was a simple matter to just keep hitting ⌃⌥⌘F again and again until I’d worked my way through the list of addresses and had Favorites fully populated. I then opened Maps on my phone and there they were.

I said at the beginning that this macro saved my time in two ways. First, of course, it saved me time in adding all the addresses to my Favorites list. More important, though, is the time it saved me in routing myself efficiently to all the buildings. That was worth at least two hours of my Sunday.

And as you can see, there’s nothing clever about this macro. It doesn’t involve loops, decisions, functions, or any other computer sciencey stuff. It’s just a straight translation of the manual steps into Keyboard Maestro actions. Anyone can do automation like this, and the Mac would be poorer if this ability disappeared.