Application Scripting is Weird

There’s a tendency in the Apple world to paint AppleScript as some uniquely weird inconsistent language. I’m usually amused by that, because then the person doing that will use shell as an example of a consistent language. Which is highly amusing.

But here’s the the thing: the core language, the core AppleScript syntax is really quite consistent. It’s when you get into scripting applications that things get weird, because app devs are not consistent in how they implement things.

So let’s take a look at it via Excel, which has the advantages of being scriptable in wildly different languages on a different platform. We’re going to do a fairly simple set of steps:

1. Open an excel file
2. Set a range of columns to be formatted as a table
3. sort that table by the first column ascending

AppleScript

Here’s how we do this in AppleScript:

set theExcelFile to "pathtofile" as POSIX file
tell application "Microsoft Excel"
     activate
     open theExcelFile
     set theWorkbook to the active workbook
     set theWorksheet to active sheet of theWorkbook
     set theRange to get entire column of range ("A:H")
     set theList to make new list object at theWorksheet with properties {range object:theRange}
     set theSort to sort object of theList
     set theSortRange to sortrange of theSort
     sort theSortRange key1 (column "$A:$A" of theSortRange) order1 sort ascending
end tell

I mean, if you know Excel, and how “Format as Table” actually creates a list object, and sorts are weird within a table/list object, and you script Excel a LOT, this makes sense. You:

1. Create the path to the file
2. Tell Excel to start/activate
3. Tell Excel open the file
4. Create a reference to the active workbook
5. Create a reference to the active (work) sheet of the active workbook reference
6. Create a range of columns
7. Make a new list object (format as table) in the active sheet for the range you just created and create a reference to that list object
8. Create a reference to the built in sort object of the list object
9. Create a reference to the sortrange of that sort object reference
10. sort the sortrange reference by the first column of the sortrange in ascending order

Okay, so what about say, PowerShell on windows? That has to be way less application-specific right? Surely it’s not that weird…

Powershell

$fileToOpen = "fullpathtofile" 
$excelObject = New-Object -ComObject Excel.Application 
$excelFileObject = $excelObject.Workbooks.Open($fileToOpen)
$excelFileWorksheet = $excelFileObject.ActiveSheet 
$excelFileWorksheetRange = $excelFileWorksheet.Range("A1","H1").entireColumn 
$excelFileTableObject = $excelFileWorksheet.ListObjects.add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange,$excelFileWorksheetRange,$null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes)
$excelFileTableObject.Sort.SortFields.add($excelFileTableObject.Range.Columns.item(1),0,1)
$excelFileTableObject.Sort.Apply() (actually perform the sort)
$excelObject.Visible = $true 

Obviously this is totally different, because here we:

1. Create the path to the file
2. Tell Excel to start/activate
3. Tell Excel to open the file
4. Create a reference to the active workbook
5. Create a reference to the active (work) sheet of the active workbook reference
6. Create a range of columns
7. Make a new list object (format as table) in the active sheet for the range you just created and create a reference to that list object
8. Create a sort object made up of the list object specifying the column to search on and how
9. Apply the sort object to the list object
10. Actually make the excel file visible

Oh yeah, that’s totally different and that syntax is just as bog-standard PowerShell as can be, unlike that Excel AppleScript which has nothing to do with core AppleScript. 🙄

If you were to do the same thing in Numbers, you’d see a similar syntax, because applications have specific needs that a core language for an OS does not. Any language that can be extended to fit the needs of a specific application is going to get weird based on the needs, features, and naming conventions of the application. For example, “Table” in Excel can cover a lot of very different things. “Table” in Numbers covers basically one thing, it’s not like Numbers has Pivot Tables. So doing table operations in Numbers is similar, but not identical to format as table in Excel.

Any language supporting application scripting is going to get weird as more applications use it. It’s the unavoidable nature of the beast.

Advertisement