I use Excel for a ton of stuff, both personal and work. As an example, on the work side I use an Excel workbook to track our apps' hidden prefs—which are set using a long ugly Terminal command1Something like this: defaults write com.manytricks.Moom "PMWindowFadingDuration" -float 0.
These hidden prefs can be used to invoke features we're testing, or to revert a behavior we've changed at some point, etc. For example, Moom has hidden prefs to use the full screen grid without clicking in the big box and to disable the fade in/out of the keyboard controller.
We don't publish all of these, as we're not necessarily ready for them to be put to use by everyone (otherwise, they'd be visible prefs). But there are cases when a user has a specific need for a setting, or when troubleshooting, that these hidden prefs can be very useful. As such, I often have to send someone a defaults write command.
Read on to see how I use Excel's formatting features—plus the ever-valuable Keyboard Maestro—to disguise some of this workbook's formula results, yet still easily copy them for sending to a user.
My spreadsheet is set up in a columnar manner, showing the name of the pref, its settings, and a note about what it does:
To send that hidden Resolutionator pref to a user, I'd need to copy the bit in the "Path" line at the top of the worksheet, then replace "command" bit with "PMKeyboardMenu: Static Selection", -type with -boolean, and value with NO. That's a pain, so I wanted Excel to do the work for me.
To accomplish this, I added a column (which I called Copy It) and inserted a simple string formula that builds the text string for me, combining the various bits as needed. But because these strings can be quite long, I didn't want to show the output of the formula, as it would require a super-wide column. (Setting a narrow column width would mean looking at a vertical column showing just defaults..., which looks really ugly.)
To get around these two ugly options, I chose to disguise the output of the formula, so every cell in the "Copy It" column appears like this:
Regardless of a cell's formula, it will only display [c]. The key to this is using Excel's custom cell formatting, which works on both numbers and strings. Select any cell, then select Format > Cells and click on Custom in the Category box. The structure of a custom cell format is as follows:
positive number format; negative number format; zero value format; text format
As an example, look at this simple worksheet, set to display formulas:
As you can see, column C is set to simply copy what's in column B. But when I disable formula view, that's not what I see:
This is due to custom formatting. The numbers have this custom format:
"+";"-";"--"
Using the key from above, the first item is what's shown for positive numbers, then negative, zero, and text—which I've left out, as this format was just for numbers. And here's the format for the text formula:
;;;"dog cow"
This example skips all three of the number formats (three semicolons with nothing in between), and then inserts the text dog cow. No matter what string formula is in a cell, it will return dog cow with this format applied.
To disguise my complicated defaults text formula output, I simply set the format of the cells to ;;;[c]. (Note that you can use custom cell formatting to play some wicked practical jokes—something like "2.99";"-3.72";".0001";"Nope", perhaps. Not that I've ever been a victim of such a prank, nor pranked anyone else myself, of course.)
With the results of my formula hidden, I couldn't simply copy the results with ⌘C, as that would just copy [c]. Using an Excel feature and Keyboard Maestro, though, I've made it basically just as easy. (I could also have done this with a macro in Excel, but I find it easier to work in Keyboard Maestro.)
In Excel, if you click in the formula bar (to edit a formula), then press ⌘=, Excel will calculate the formula's result and display it in the formula bar. Even better, it selects the entire result, so it's ready to copy. If you then press Escape, the edit is canceled and the cell's formula is left unchanged.
Enter Keyboard Maestro: I just needed a macro that would click in the formula bar, type ⌘= then ⌘C, and then press the Escape key. Here's how that macro looks:
I've assigned it to Control-C (easy to remember, easy to type), and it's pretty straightforward. The only minor thing to note is that the macro that clicks in the formula bar says "Click…" instead of "Move and click…". The default behavior for this action is to move the mouse to the location where you want to click; to make Keyboard Maestro remember the mouse location and restore it after the click, click on the gear icon to the right of the action and then select Restore Mouse Position from the pop-up menu.
This works perfectly—I can now easily copy any of the defaults write commands with a simple keyboard shortcut. Excel and Keyboard Maestro make for a very powerful—and useful—pair!