Scanning a list of files and adding part of a name to an Excel file or Google sheet

Hello everyone,

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.

I look forward to your replies.

Thanks a lot in advance!


1 Like

Yes, this can be done, but we need much more info to be helpful:

  1. Attach zip file of example Excel file, with some data complete to show what is expected.
  2. Screenshot, or path to the parent folder of the folder collection. Need details for what this means:
    • "correct subfolder of the current month"
    • Naming convention for each monthly sub-folder.
  3. Confirm that this naming convention for your files holds for all use cases:
    • "Meals\2018\03\2018-03-29 - meal receipt 23,25"
    • Is the data source the name of a file or folder?
    • I think a screenshot of your folders/files properly expanded would be the best way to clearly communicate your setup.
1 Like

Thanks Michael for your reply!!
See the requested info below.

  1. 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 - (14.6 KB)

  2. See screen

  • Folder "03" in the image is the current month (March)
  1. 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.

Hey @pmatiakis

[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 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.

yDirName=$('ls' -1 "$mDir" | tail -n -1)
dDirName=$('ls' -1 "$mDir/$yDirName" | tail -n -1)
'ls' -1 "$mDir/$yDirName/$dDirName"

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.


1 Like

I hope @ccstone doesn't mind me contributing a small addition to his offerings. I believe this criterion was overlooked:


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

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.


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+'

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.

1 Like

Wow you guys are amazing!

I’ll try to implement this and get back to you.
I don’t know anything about Apple script or shell script but I’ll try my best.

Thanks a lot!!!

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.

Hey @CJK,

Not at all. Thanks for noticing my omission.


Hey @pmatiakis,

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.

@pmatiakis – see the orange actions?

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:

  1. Acquires the data and transforms it to the correct format for entry into your spreadsheet.
  2. Opens Microsoft Excel.
  3. Opens the correct spreadsheet.
  4. Finds the correct entry point.
  5. 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.


Extract Meal Data for Excel Spreadsheet v1.01.kmmacros (13 KB)

Hey Guys,

Yet another reason why I’ve been a devotee of the Satimage.osax AppleScript Extension since 2003.

It made this task almost too simple.  :sunglasses:


# 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


Thanks again!

@ccstone I downloaded and changed your macro like you said.

However when I run it the following error comes up.


Just for confirmation I set the path to


Any ideas?

Hey @pmatiakis,

You’re misusing the tilde-based path form.


Should be:




~/ is an abbreviation for your Home folder and makes using paths more portable between different computers, boot drives, etc.


I’ve updated my macro in post #12 to v1.01 – 2018/04/01 02:03 CST


:man_facepalming:sorry didn’t know.

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.

Hey @pmatiakis,

Now you do.  :sunglasses:

The problem sounds like a timing issue, and those can take some tweaking.

Try increasing the pause from 2 seconds to 10 or more.

Also try running the macro with Excel already running.

What version of Excel are you using?


Hey Chris,

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.

I can’t thank all of you enough!!!


Hey @pmatiakis,


You could run this AppleScript and save the result to a variable:

tell application "Microsoft Excel"
   return active cell's value
end tell

If the result is “Week” then you can change the keystrokes as desired.


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.

Thanks again


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.

1 Like

Thanks Michael for your suggestion.
This seems like a really useful Macro (adopted :wink:).

However what you’re suggesting is to manually search for and enter all information to the Excel file right?
Maybe I misunderstood.