By Jason Snell

July 18, 2018 4:55 PM PT

Last updated July 27, 2020

Chart party: Using automation to eliminate image drudgery

Note: This story has not been updated since 2020.

Every three months I generate new charts resulting from the release of Apple’s financial results, including total products shipped, revenues, profits, and much more. I like how the charts, which are built in Numbers, look—but not the process of getting them out of Numbers and up on this site.

So in advance of the next quarterly results release¹, I decided to see if I could find a way to somehow automate my chart process, which up until now has involved me manually taking screen shots of every single chart as I pan around the Numbers window. (Then I have an Automator action that resizes those screenshots and uploads them.)

I asked some of the smarter user-automation people I know—Sal Soghoian, Rosemary Orchard, and Dr. Drang—and ended up stumbling my way to a solution, at least on the Mac. (I’m still searching for one on iOS, but I rarely am traveling when the numbers come out.)

Here’s how my solution—saved as an Automator app, though I could’ve probably done it entirely in AppleScript if I had wanted to put in a little more work—does its job:

First, adapting a script from one of Sal’s Mac OS Automation sites, I export my entire Numbers document to PDF. Then I use Automator’s built-in “Render PDF Pages as Images” command to output my numbers file to 300-dpi PNG images.

Originally my Numbers file was a single sheet, but as a part of this project I decided to move the charts to a second sheet, leaving the raw table of numbers alone on a sheet. This means that when the two above steps run, I’m left with two large PNG images—one with all my charts, and another one showing the table text (which I ignore).

Now for the next trick, which is sectioning up a 12 MB, 12,000-by-14,000 pixel PNG file into a bunch of little images resized perfectly for the Six Colors web template. I considered a bunch of approaches, but ended up settling on ImageMagick, a set of command-line image tools that I was able to install via Homebrew by typing brew install imagemagick.

Imagemagick includes a command called convert, which does an awful lot of things, including allowing you to arbitrarily crop a file at a certain point to a specific size. So to carve a single chart out of my giant source file, I might issue a command like this:

  convert applefinancials.png -crop 2637x1612+341+2144 chart1.png

To explain, that command is cropping the file applefinancials.png to a box that’s 2637 pixels wide and 1612 pixels tall, from a point that’s 341 pixels from the left and 2144 pixels from the top of the original image, and saving it all to a new file called chart1.png.

Regarding the very specific file size of 2637 x 1612: The charts in my Numbers file weren’t entirely consistent in their size and placement, so as a part of this project I created a colored rectangle in Numbers and then resized every single chart in my file until they all fit underneath it with no content sticking out. That colored rectangle, when exported to that giant PNG, was exactly 2637 pixels wide and 1612 pixels high.

Once I had proven that Imagemagick was capable of cropping my image, I needed to chop up two dozen charts. To speed things along, I aligned my charts in Numbers on a grid, and wrote a simple pair of repeat loops in AppleScript that step through all four columns of charts one row at a time.²

With that done, I had two more steps I needed to take: resizing and uploading of my images, tasks I currently perform with a different Automator action. For resizing, I was able to just tack on an extra resize attribute to when running convert³:

  convert applefinancials.png -crop 2637x1612+341+2144 chart1.png -resize 1360x833

For uploading, I copied out the code from my previous Automator action and re-used it, inserting a quick FTP upload for every file as it was coming off the assembly line. (I use the command-line tool scp to do this; this post from Dr. Drang is where I learned how to do it.)

After a couple of test runs to clear out a few mistakes, the script has proven good to go—and I’ll put it into practice on July 31. I’m pretty sure that over the quarters it will end up saving me time, but more importantly, it will immediately free up my time in those precious moments just as Apple’s numbers have been released, allowing me to post my chart images faster and then move on to more pressing matters, like covering Tim Cook’s call with analysts and figuring out What It All Means for the future of Apple’s business.

Update

Thanks to advice from Matthew Cassinelli and Federico Viticci, I’ve got this same workflow running in Shortcuts in iOS 12 (download via Sharecuts):

• From within Numbers, export as a PDF (one page per sheet) to the Shortcuts folder in iCloud Drive. Then, from within Shortcuts:

• Get File (the PDF I just saved)

• Make PDF, Include Single Page, Page #2 (all the charts are on the second sheet of my Numbers spreadsheet)

• Convert Image, Format PNG

• Save File, destination path of a PNG on iCloud Drive

• List (all of the starting points for my four columns of charts)

• Repeat with Each

• List (all of the starting points for my eight rows of charts)

• Repeat with Each

• Get File (the PNG I saved earlier)

• Crop Image, Position Custom, X Coordinate “Repeat Item” (my column list), Y Coordinate “Repeat Item 2” (my row list), width 2637, height 1612

• Resize Image, Width 1360, Height Auto

• Save File, Destination Path a charts subfolder with a file named using the current date and the row and column indices.

• End Repeat

• End Repeat

That gives me a stack of chart images in iCloud Drive which I can drag and drop into an FTP client for uploading. I’d like to automate that process too, but the deprecation of Transmit for iOS has left a hole…

If you appreciate articles like this one, support us by becoming a Six Colors subscriber. Subscribers get access to an exclusive podcast, members-only stories, and a special community.