Batch Convert Numbers Spreadsheets to Another Format

I created this macro in response to a user's thread at Batch convert CVS to XLSX file (see my post there).

I decided to post my macro here too because I thought it may be of any worth to those of you who might need to convert spreadsheets in batch.

The macro is based on an AppleScript script. The script prompts you to select Numbers-compatible files and allows to choose from several legible formats. It puts converted files in the "Documents" folder. I created it targeting Numbers 3.2.2 in OS X Mavericks, in KM 6.4.8, and modified to work with Mojave 10.14.6. It includes small chunks of GUI scripting which is prone to errors when the GUI structure changes between releases of macOS (especially now that Big Sur is available), however I don't expect the part I added to evolve significantly by the current generation of the OS to the point of crashing the macro. If the crashes do occur, return and update me.

Updated 2020-11-18T20:20:00Z

I re-wrote the AppleScript to allow for different versions of macOS with regard to the GUI segment correction for macOS Mojave 10.14.6. I don't run newer iterations of macOS and can't adjust the macro appropriately, so if you run into unintended behaviour your should figure out on your own: it shouldn't pose additional problems since you have the framework along which you work it out.
The macro is now triggered with the hot keys ⌃⌥⇧⌘P by default. You can assign different ones.

IMPORTANT: make sure the group containing this macro is enabled and active otherwise it won't run outside the Macro Editor.

Convert selected spreadsheets into another format.kmmacros (5.3 KB)

The AppleScript script is as follows:

Execute AppleScript:

set NumbersCompatibleDocs to choose file of type {"com.apple.iwork.numbers.numbers", "public.spreadsheet", "public.comma-separated-values-text", "org.openxmlformats.spreadsheetml.sheet"} default location (path to documents folder) with prompt "Select Numbers spreadsheets or spreadsheets of compatible formats:" with multiple selections allowed
property ExportPath : POSIX path of (path to documents folder)
property ExportFormats : {"PDF", "Microsoft Excel", "CSV", "Numbers 09"}
property Ext : {".pdf", ".xlsx", ".xls", ".numbers", ".csv"}
property SystemVersion : (system info)'s system version

try
   tell application "Numbers"
      
      activate
      tell application "System Events" to set frontmost of application process "Numbers" to true
      set CurrentDocuments to open NumbersCompatibleDocs
      set TargetFormat to choose from list ExportFormats with title "Export formats" with prompt "Choose a format:" default items {"CSV"} without multiple selections allowed and empty selection allowed
      
      if result is not false then
         set TargetFormat to TargetFormat's item 1
         repeat with aDoc in CurrentDocuments
            set docName to the name of aDoc
            
            
            
            set BaseName to my GetBaseName(docName)
            -- log "BaseName ⬆︎" & (log BaseName)
            if TargetFormat is "CSV" then
               set DocumentPath to ExportPath & BaseName & ".csv"
               set ExportFile to (POSIX file DocumentPath) of me
               -- log "DocumentPath ⬆︎" & (log DocumentPath)
               export aDoc to ExportFile as CSV
            else if TargetFormat is "PDF" then
               set DocumentPath to ExportPath & BaseName & ".pdf"
               set ExportFile to (POSIX file DocumentPath) of me
               export aDoc to ExportFile as PDF
            else if TargetFormat is "Microsoft Excel" then
               set DocumentPath to ExportPath & BaseName & ".xlsx"
               set ExportFile to (POSIX file DocumentPath) of me
               export aDoc to ExportFile as Microsoft Excel
            else
               set DocumentPath to ExportPath & BaseName & ".numbers"
               set ExportFile to (POSIX file DocumentPath) of me
               export aDoc to ExportFile as Numbers 09
            end if
         end repeat
      else
         return
      end if
      
   end tell
   
   display notification "Export successful"
   
on error errMsg number errNumber
   
   tell application "Numbers"
      activate
      display alert "Export failed with error " & errNumber & return & return & errMsg as warning buttons {"OK"} giving up after 3
   end tell
   return
end try
considering numeric strings
   if SystemVersion > "10.9.5" then
      set AccessibilityHelpDescription to "Change the item grouping (hold down Option to change the sort)"
   else
      set AccessibilityHelpDescription to "Change the item arrangement"
   end if
end considering

tell application "Finder"
   tell folder "Documents" of home
      open
      activate
      sort every document file by creation date
   end tell
   
   set current view of Finder window "Documents" to column view
   delay 1
   tell application "System Events"
      tell application process "Finder"
         set frontmost to true
         tell window 1
            tell toolbar 1
               tell (the first group whose help is AccessibilityHelpDescription)
                  tell menu button 1
                     perform action "AXPress"
                     repeat until menu 1 exists
                        delay 0.1
                     end repeat
                     tell menu 1
                        tell menu item "Date Created" to perform action "AXPress"
                     end tell
                  end tell
               end tell
            end tell
         end tell
      end tell
   end tell
   
end tell

on GetBaseName(FullName)
   set OriginalName to FullName
   set AppleScript's text item delimiters to Ext
   get text item 1 of OriginalName
   return result
end GetBaseName