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:

  1. Maximize the window
  2. Select all columns and fit them to its width
  3. Apply filters to all columns
  4. 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.

Record Resource Usage of Single Process

Introduction

On Linux & Mac, we can use an inbuilt top command line tool to monitor the resource usage of a single process in real time.

# On Linux, for a given pid
$ top -p 1234

# On Mac, for a given pid
$ top -pid 1234

In this article, we will see how to record and plot resource usage of a single process using top and a Python package called psrecord1.

Record Resource Usage

In some cases, we need to record the resource usage of a process to use it later. For example, we can use this data to find out the peak resource usage of a process. For this, we can use top to log resource usage into a text file.

# On Linux, for a given pid
$ top -p 1234 -b -d 1 > top.log

# On Mac, for a given pid
$ top -l 0 -s 1 -pid 32515 | awk 'NR%13==0; fflush(stdout)' > top.log

Once we have the log file, we can view the raw data or we can plot the resource usage by using tools like gnuplot or matplotlib.

Instead of using top command, we can use psrecord to record the resource usage of a process. psrecord is a Python package that can be installed all using pip.

$ python -m pip install psrecord

Once installed, we can use psrecord to record the resource usage of a process.

# record resource usage of a process with pid 1234
$ psrecord 1234 --log top.log

# start and record resource usage of a process
$ psrecord python script.py --plot graph.png

We can view the raw data in the log file.

# view raw data
$ head top.log
$ head a.txt
# Elapsed time   CPU (%)     Real (MB)   Virtual (MB)
       0.000        0.000        5.000   399461.438
       0.000       93.700        5.000   399461.438
       0.000       96.300        5.000   399461.438
       0.000       91.900        5.000   399461.438

Here is the generated graph.

single-proc-resource

Conclusion

In this article, we have seen how to record and plot resource usage of a single process using top(inbuilt tool), psrecord(3rd party package).

Reducing System Load With ChatGPT

Problem Statement

I am using M1 Macbook Air for Python development purposes. Since M1 uses ARM architecture, many Python packages don't have wheels for ARM64/aarch64. confluent-kafka-python is one of them.

I had to run AMD64 docker container to use confluent-kafka-python. Since it is a cross-architecture container, its CPU usage is too high and performance was too slow.

Solution

To reduce system load, I decided to build aarch64 wheels for confluent-kafka-python. I looked at open issues on GitHub and asked maintainers how to build aarch64 wheels. There was no response1 from them.

As a workaround, I asked ChatGPT2 on how to build confluent-kafka-python aarch64 wheels in a docker container.

chatgpt-reduce-system-load

This initial suggestion didn't work as confluent-kafka-python depends on librdkafka which is a C library. I had to build librdkafka from source for aarch64 and then build confluent-kafka-python from source.

To build librdkafka from the source, I again asked ChatGPT. After making minor changes to the snippet suggested by ChatGPT, I was able to build librdkafka from the source for aarch64.

Here is the final snippet:

FROM ubuntu:22.04

ARG DEBIAN_FRONTEND=noninteractive

RUN apt update && apt install -y \
  wget git curl g++ make postgresql-client \
  nano less shared-mime-info openjdk-17-jre-headless \
  libpq-dev vim tzdata python3 python3-dev

RUN apt install -y python3-pip
RUN python3 -m pip install setuptools

WORKDIR /
RUN git clone https://github.com/confluentinc/confluent-kafka-python
WORKDIR confluent-kafka-python

COPY . /app
WORKDIR /app
RUN ./configure --arch=aarch64 --prefix=/usr
RUN make
RUN make install

WORKDIR /confluent-kafka-python
RUN python3 setup.py install

Conclusion

By running native containers, I was able to reduce the system load by ~50%. With ChatGPT, it is easy to build/tweak programs in languages & environments that we are not familiar with.

Automator Quick Action for KDiff3 in Finder

The need for quick action

kdiff31 is a diff & merge tool that compares multiple files/directories and shows the difference line by line and character by character as shown below.

mac-finder-kdiff3

In Windows, when we select multiple files/directories and right click on them, it will show the option to compare selected items with kdiff3.

mac-finder-kdiff3-windows

However, in Macbook, it doesn't show this option. In this tutorial, let us see how we can create the same quick action in the right-click menu when we right-click on the files/directories.

Creating Quick Action

Let us open Automator2, create new file and select Quick Action.

mac-finder-automator

On the left side select Utilities and then select Run Shell Script.

For Workflow receives current, select files or folders and then select in Finder.

mac-finder-quick-action

Then select pass input as agruments and in the script section let us add the following command.

/path/to/kdiff3 $1 $2

After adding the command, save this Quick Action.

Now if we relaunch Finder app and then select multiple directories, and right click we can see Compare with KDiff3 in quick actions.

mac-finder-kdiff3

Conclusion

Even though we can use the command line to compare the files/directories, it is always good to have a quick action in the right-click menu.