Expense reports and saving to iCloud
November 25, 2016 at 9:36 AM by Dr. Drang
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
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:
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.
If you double-click it, you’ll be taken to iCloud Drive, which has folders associated with various apps.
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:
$ 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/DocumentsThe 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:
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.