I started using KM 3 months ago and I’ve already done a number of amazing things.
However I’m now trying to automate a process and cannot figure it out.
Every day I’m scanning receipts for expenses on my business trips.
Then at the end of each week I have to add the value of each receipt to an excel file.
As OCR doesn’t work reliably I’ve gotten into the habit of adding the amount at the end of the file name.
I have Hazel archiving these receipts for me in a hierarchical structure of folders by year and month.
Example path:
Meals\2018\03\2018-03-29 - meal receipt 23,25
I’m wondering if I can create a macro in KM that runs once per day, drills to the correct subfolder of the current month, and appends the date and value of each receipt into an excel file.
I need it to run every day and append only the day’s receipts ignoring all the previous day’s receipts in that folder.
Thanks Michael for your reply!!
See the requested info below.
As you can see in the attached file I would also like to calculate the week number for each entry. This is also something I couldn't figure out how to do and I could use it in more macros I already have created. 2018-03 - Meals.xlsx.zip (14.6 KB)
See screen
Folder "03" in the image is the current month (March)
Confirmed.
Don't worry about the screen where most of the files are "meal expense". From now on it will always be "meal receipt"
Once the script identifies the correct folder ("03" for March) the data source is the file name.
[Edit 2018/04/01 – as @CJK points out below, I’ve misunderstood @pmatiakis’ task a bit – but I’ll leave this code – as it is useful in its own right. I’ve posted code more germane to the original task below.]
I think this is probably the most efficient AppleScript method to drill down to the latest expense folder and get a list of the names of the files contained therein.
----------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2018/03/30 22:51
# dMod: 2018/03/30 22:59
# Appl: System Events
# Task: Drill down to the last folder in a hierarchy and get a list names of the contained files.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @System_Events, @Drill, @Down, @Last, @Folder, @Hierarchy, @List, @Names, @Contained, @Files
----------------------------------------------------------------
set mealsFolderPath to "~/test_directory/INSYS/Meals"
tell application "System Events"
set rootFolderPath to disk item mealsFolderPath
set yearFolder to last folder of rootFolderPath
set dayFolder to last folder of yearFolder
set fileNameList to name of files of dayFolder whose visible is true
end tell
----------------------------------------------------------------
Change the path in mealsFolderPath to comply with your system structure and then run the script in the Script Editor.app to see the resulting file name list.
NOTE – AppleScripts when run from Keyboard Maestro use a Execute an AppleScript action.
Getting the file names turns out to be really easy to do in the shell as well.
NOTE – Shell scripts are run from an Execute a Shell Script action in Keyboard Maestro, or they can be embedded in AppleScript using a do shell script "<shell-script-text>" statement.
I don’t have time right now to mess with the Excel part of the script, but I’ll try to have a look later.
I hope @ccstone doesn't mind me contributing a small addition to his offerings. I believe this criterion was overlooked:
AppleScript:
set mealsFolderPath to "~/Dropbox/Documents/INSYS/Receipts/Meals"
set queryDate to current date --> Set this to any AppleScript date
set CSVdata to {} --> A list that will contain the extracted data
set the text item delimiters to {tab, "T"}
first text item of (queryDate as «class isot» as string)
set [isoDate, [y, m, d]] to the result's [it, its words]
tell application "System Events" to repeat with filename in ¬
(the name of every file in ¬
folder m in ¬
folder y in ¬
folder mealsFolderPath where ¬
the name starts with the isoDate) as list
get {do shell script "date +%V", ¬
[d, "/", m, "/", y], ¬
"Per diem rate (expenses)", ¬
word -2 of the filename}
set end of CSVdata to the result as text
end repeat
CSVdata --> Each item represents a row to import into Excel
Summary
An AppleScript to extract elements of each filename linked with today's receipts. The script returns a list of data ready to be imported into Microsoft Excel, where each item in the list is a tab-delimited string of four items that represent the four columns of a single row in the spreadsheet: with the week number; the date formatted as dd/mm/yyyy; the string "Per diem rate (expenses)"; and the value of the receipt.
Bash:
mDir=~/Dropbox/Documents/INSYS/Receipts/Meals
d=$(date +'%Y-%m-%d') # Set this to any "YYYY-mm-dd" date string
'ls' "$mDir/${d:0:4}/${d:5:2}/$d"* | egrep -o '\d+,\d+'
Summary
A bash shell script that lists the values of the receipts for the specified date. Currently, it is set to query today's date.
Apologies, I don't have Microsoft Excel in order to write and test a script that would import data.
Hey @pmatiakis, I just updated my AppleScript once I had a look at your spreadsheet. The variable CSVdata will now contain the rest of the data necessary to do a direct import to Excel, as previously, I failed to include the week number and the travel expense type, and the date was in ISO format. This has now been rectified. Each item in the CSVdata list is now a tab-delimited string containing the four values that comprise a single row in your spreadsheet.
Thanks to @CJK's noticing my mistake I was able to revisit this and do everything in the shell, so the data acquisition and manipulation is very fast.
While I'd prefer to use AppleScript to enter the data in the spreadsheet, I don't have time at the moment to figure out the syntax to locate the correct entry point, but I'll look into this later if time and interest allow.
Those are the ones you need to alter to correspond correctly to your system.
Drag the correct files into the to: field for each action.
NOTE – it is important that the first actions does NOT end with a backslash (“/”). Keyboard Maestro should NOT put one there, so this should be a non-issue if you drag-and-drop the files.
The macro:
Acquires the data and transforms it to the correct format for entry into your spreadsheet.
Opens Microsoft Excel.
Opens the correct spreadsheet.
Finds the correct entry point.
Pastes the data.
The spreadsheet is NOT saved, so any mistake is easy to recover from.
The data is LEFT on the Clipboard, so it is readily available if corrections to spreadsheet entry are required.
Yet another reason why I’ve been a devotee of the Satimage.osax AppleScript Extension since 2003.
It made this task almost too simple.
-Chris
----------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2018/03/31 22:27
# dMod: 2018/03/31 23:07
# Appl: AppleScript, Satimage.osax
# Task: Acquire and massage data for entry into an Excel spreadsheet.
# Libs: None
# Osax: Satimage.osax
# Tags: @Applescript, @Script, @Satimage.osax, @Acquire, @Massage, @Data, @Entry, @Excel, @Spreadsheet
----------------------------------------------------------------
set mealsFolder to ((path to home folder as text) & "test_directory:INSYS:Meals:")
set dateToday to current date
set todayFolder to alias (mealsFolder & (strftime dateToday into "%Y:%m:"))
set fileNameList to list files todayFolder starting with (strftime dateToday into "%Y-%m-%d") with names only
repeat with fileName in fileNameList
set contents of fileName to strftime (strptime "2018-03-31" encoding "%Y-%m-%d") into "%U" & tab & (contents of fileName)
end repeat
set fileNameList to join fileNameList using linefeed
set fileNameList to change " - " into "\\t" in fileNameList with regexp without case sensitive
set fileNameList to change " (\\d+,)" into "\\t\\1" in fileNameList with regexp without case sensitive
set fileNameList to change "\\.pdf$" into "" in fileNameList with regexp without case sensitive
----------------------------------------------------------------
Ok now it’s passed that point.
Now the worksheet comes up but when KM hits “Paste” the cursor goes to the end of the worksheet (line 1048576) but nothing is pasted.
You were right.
I increased the pause to 10 seconds and it works.
Of course now that we’re in a new month and there are no entries in the excel file, the last (CMD down arrow) step, causes it to jump to the end of the sheet but I added one extra dummy line to get around that.
Thanks Chris that’s a great tip.
Working with this for a while showed me a flaw in my design.
I’ve scheduled the macro to run every day.
Some times though, I’m adding receipts in the evening, when my mac is not running.
The next day however when the macro runs, it gets the receipts from the current day, but misses the ones entered the night before.
If I change the script to look at the day before then I would miss Friday’s receipts, as my Mac could not be running on Saturday.
The ideal would be for the script to get any receipts that are not already in Excel, but I guess that would require too much work.
Can I ask you to please change the script so it gets all the receipts of the current month?
Then I can run it on the last day of the month and get everything.
Maybe you need to consider changing your workflow logic.
How about processing all receipts that have not been processed?
Do you really need month folders? A Spotlight search can easily limit to a given date range. For example, see MACRO: @SL Mac Spotlight Std Search.
sorry didn’t know.
).