Mastering HammerSpoon - Excel Automation
Introduction
Recently, I have been using Excel a lot. When opening a new Excel file, I have to do the following:
- Maximize the window
- Select all columns and fit them to its width
- Apply filters to all columns
- Freeze the first row
When opening and closing multiple Excel files, this becomes a tedious task. So, I decided to automate this and came across Hammerspoon.
HammerSpoon
Hammerspoon1 is a powerful automation tool for macOS. It allows you to write Lua scripts to automate various tasks and make our keybindings.
First, let's install Hammerspoon using Homebrew.
$ brew install hammerspoon
We can write our automation script in ~/.hammerspoon/init.lua
file. Let us see how we can automate the above tasks.
Automating Excel
-- excel function excel(appObject) local win = hs.window.focusedWindow() if (not win) then return end win:maximize() appObject:selectMenuItem({"Edit", "Select all"}) appObject:selectMenuItem({"Format", "Column", "Autofit Selection"}) appObject:selectMenuItem({"Data", "Auto-filter"}) end) function applicationWatcher(appName, eventType, appObject) local w = hs.application.watcher if (eventType == w.activated or eventType == w.launched) then if (appName == "Microsoft Excel") then excel(appObject) end end end
This script will watch for application events and when Excel is launched or activated, it will call the excel
function.
The excel
function will maximize the window, select all columns and fit them to it's width, apply filters to all columns.
Free top row
option is not available in the standard menu. So, I have added it to the quick access toolbar and click it via mouse event.
Conclusion
Hammerspoon is a powerful tool for various automation tasks. In addition to that it can replace a lot of utility apps like CheatSheet, BlueSnooze2, Rectangle, ShiftIT3, HotKey etc. I have replaced most of the utility apps with Hammerspoon, and it is working great. I will be writing about it in detail in the upcoming posts.
Need further help with this? I am available for hire.