Applescript - Droplet - Works with - Numbers not Excel

I am trying to run a script as a droplet, which is working fine when the file is in Numbers format.

Problems occur when I drop an excel file into my droplet. Even though the script opens the file in numbers, as i want it to, it keeps crashing with the message: “Can’t get document test.xls of application numbers”

It runs fine when I use a numbers doc. Is there a way around this? I’d be happy if there was a way to save the file as a numbers file and then continued with the rest of the script.

-Edit: First error occurs the first time I tell sheet 1

My script begins like this:

on open these_items
	tell application "Finder" to reveal these_items
	set this_path to the quoted form of the POSIX path of these_items
	tell application "Numbers"
		activate
		open these_items
			
		tell application "Numbers"
			activate
			repeat 10 times
				try
					set myDoc to name of document 1
					exit repeat
				end try
				tell me to delay 0.2
			end repeat
			tell document myDoc
				name of sheets
				
				tell sheet 1 to tell table 1
					set theColumns to {"J"}
					repeat with i from (count of theColumns) to 1 by -1
						remove column (item i of theColumns)
					end repeat
				end tell


As far as I know, you are facing a bug. It seems that AppleScript is reluctant to drive documents imported by Numbers.

Here is a workaround.

set these_items to choose file "choose Excel file(s)" of type {"org.openxmlformats.spreadsheetml.sheet"} with multiple selections allowed
set anItem to item 1 of these_items

tell application "Numbers"
	activate
	open anItem
end tell


tell application "System Events" to tell process "Numbers"
	set frontmost to true
	set {nbLoop, pass} to {200, 0}
	repeat nbLoop times
		try
			set pass to pass + 1
			tell me to delay 0.1
			log "pass = " & pass
			set wName to title of window 1
			if wName ≠ "" then exit repeat
		end try
	end repeat
	if pass = nbLoop then error "Oops, the doc didn't open" number 12340
	
end tell # System Events .
# Now the frontmost document is reachable

tell application "Numbers"
	set myDoc to name of document 1
	tell document myDoc
		name of sheets
		
		tell sheet 1 to tell table 1
			set theColumns to {"J"}
			repeat with i from (count of theColumns) to 1 by -1
				remove column (item i of theColumns)
			end repeat
		end tell # sheet 1
	end tell # document myDoc
end tell

#=====#=====#=====#=====#=====#=====

Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) mercredi 2 novembre 2016 19:10:27

As you know, I’m curious and pig headed so I made new attempts.

Good surprise, I don’t know why but this time I gopt a more logical behavior.

set these_items to choose file "choose Excel file(s)" of type {"org.openxmlformats.spreadsheetml.sheet"} with multiple selections allowed

set anItem to item 1 of these_items

tell application "Numbers"
	activate
	open anItem
end tell

tell application "Numbers"
	repeat 200 times
		try
			delay 0.2
			set myDoc to name of document 1
			if myDoc ≠ "" then exit repeat # EDITED
		end try
	end repeat
	tell document myDoc
		name of sheets
		
		tell sheet 1 to tell table 1
			set theColumns to {"J"}
			repeat with i from (count of theColumns) to 1 by -1
				remove column (item i of theColumns)
			end repeat
		end tell # sheet 1
	end tell # document myDoc
end tell

The name of the document was extracted at first pass in the loop.
I don’t know what is responsible of the change.
I just left my mac for quite an hour for dinner.

Oops, I posted the script with a wrong instruction.
The corrected one is flagged as # EDITED

Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) mercredi 2 novembre 2016 20:50:18

Hi Yvan,

Thanks for your reply - however, in both instances, I’m seeing the following error:

Can’t get document “test.xlsx” of application “Numbers”.

just in case this makes any difference, I have updated numbers to the latest public release, 4.0.5

I’m puzzled because here it works running 4.5 too.

May you send your test file to
koenig yvan sfr fr

Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) jeudi 3 novembre 2016 13:47:07

There was a problem of permissions with the folder storing the files.

Here is the final script.

use AppleScript version "2.4" # Requires at least 10.10
use scripting additions
use framework "Foundation"


# 2016/11/04 - Use date stamp as file name
# 2016/11/04 - Store the new files in a subfolder named "Adam_reports" in the folder containing the original
# 2016/11/05 - some cleaning

# One instruction used here to select files to treat
# In real life I assume that the files would be delivered thru an other scheme 
# submitting Numbers files as well as Excel ones

on run
	set these_items to choose file "choose Excel or Numbers file(s)" of type {"org.openxmlformats.spreadsheetml.sheet", "com.microsoft.excel.xls", "com.apple.iwork.numbers.sffnumbers", "com.apple.iwork.Numbers.Numbers"} with multiple selections allowed
	my germaine(these_items)
end run

on open sel
	# sel is the list of items dropped onto the script saved as an application
	set these_items to {}
	tell application "System Events"
		repeat with anItem in sel
			if type identifier of anItem is in {"org.openxmlformats.spreadsheetml.sheet", "com.microsoft.excel.xls", "com.apple.iwork.numbers.sffnumbers", "com.apple.iwork.Numbers.Numbers"} then
				set end of these_items to contents of anItem
			end if
		end repeat
	end tell
	my germaine(these_items)
end open


on germaine(these_items)
	# Grabs the OS version once for future repeated use
	set OSversion to text 1 thru 5 of system version of (system info)
	
	set nbLoop to 50 # I never saw the loop using more than 6 pass
	
	repeat with anItem in these_items
		
		set cheminPosixDuFichierSource to POSIX path of anItem
		set {posixName, posixContainer} to (my getNameAndContainer:cheminPosixDuFichierSource)
		# Build a new name derived from the current dateTime : yyyyMMdd_hhmmss_Report_Adam.numbers
		set HfsName to my dateStamp()
		# This time we store the files in the subfolder "Adam_reports" in the hosting one
		set subFolder to "Adam_reports"
		copy posixContainer to originalPosixContainer
		set posixContainer to (my buildFullPath:subFolder inFolder:posixContainer)
		(my createFolder:posixContainer)
		set cheminPosixDuFichierNumbers to (my buildFullPath:HfsName inFolder:posixContainer)
		# coerce the paths into a format preferred by Numbers
		set furlFichierNumbers to cheminPosixDuFichierNumbers as «class furl»
		set furlSource to anItem as «class furl»
		
		tell application "Numbers"
			activate
			open furlSource # opens the original file
		end tell
		
		tell application "System Events" to tell process "Numbers"
			set frontmost to true
			
			set pass to 0
			repeat nbLoop times
				delay 0.1
				set pass to pass + 1
				try
					if name of window 1 ≠ "" then exit repeat
				end try
			end repeat # nbLoop times
			
			if pass = nbLoop then error "Numbers failed to open the doc !" number 12341
			
			# Now we are sure that the original doc is open
			
			tell window 1
				set origName to its name # will use it later to close the window
				keystroke "s" using {command down, shift down} # issue the duplicate command
			end tell
			
			# Now we have a new doc that we will save with the new name in the final location
			
			tell window 1
				keystroke "s" using {command down} # issue the save command
				set pass to 0
				repeat nbLoop times
					delay 0.01
					set pass to pass + 1
					try
						if exists sheet 1 then exit repeat
					end try
				end repeat # nbLoop times
				if pass = nbLoop then error "Numbers failed to open the Save sheet !" number 12342
				
				tell sheet 1
					# I disabled these two instructions which are useful only to create the code
					--class of UI elements --> {button, button, button, group, UI element, text field, static text, static text, text field}
					--title of buttons --> {"Enregistrer", "Nouveau dossier", "Annuler"}
					set value of text field 1 to HfsName
					keystroke "g" using {command down, shift down}
					set pass to 0
					repeat nbLoop times
						delay 0.01
						set pass to pass + 1
						try
							if exists sheet 1 then exit repeat
						end try
					end repeat # nbLoop times
					if pass = nbLoop then error "Numbers failed to open the Go To sheet !" number 12343
					
					tell sheet 1
						# I disabled these two instructions which are useful only to create the code
						--class of UI elements --> {static text, combo box, button, button}
						--title of buttons --> {"Aller", "Annuler"}
						# CAUTION, El Capitan and Sierra braught changes.
						if OSversion is in {"10.11", "10.12"} then
							set theTarget to combo box 1
						else
							set theTarget to text field 1
						end if # OSversion is in {"10.11", "10.12"}
						-- value of theTarget # I used it during tests
						set value of theTarget to posixContainer
						delay 0.01
						click button 1 # "Aller" (in French)
					end tell # sheet 1 (the Go To one)
					delay 0.01
					click button 1 # "Enregistrer" (in French)
				end tell # sheet 1 (the Save one)
			end tell # Window 1
		end tell # System Events & process
		
		delay 0.1
		tell application "Numbers"
			# Close the original document. I play safe with the try / end try instructions
			try
				close document origName without saving
			end try
			delay 0.1
			# Open the Numbers file which was created just before.
			open furlFichierNumbers
			set myDoc to name of document 1
			# here you may put your code.
			tell document myDoc
				tell sheet 1 to tell table 1
					count rows --> 1084
					count columns --> 80
				end tell # sheet .
			end tell # document .
			close document myDoc with saving
		end tell # Numbers
	end repeat
end germaine

#=====

on getNameAndContainer:posixPath
	local theURL, posixContainer, posixName
	log "Entre dans getNameAndContainer"
	set theURL to current application's |NSURL|'s fileURLWithPath:posixPath
	set posixContainer to theURL's URLByDeletingLastPathComponent()
	set posixName to theURL's lastPathComponent()
	# ATTENTION, Sierra ne met pas le / final
	# return two Posix objects
	return {posixName as text, posixContainer's |path|() as text}
end getNameAndContainer:

#=====

on buildFullPath:proposedName inFolder:cheminPosixDuFichierSource
	local theFolderURL, proposedName, theDestURL
	log "Entre dans buildFullPath"
	set theFolderURL to current application's |NSURL|'s fileURLWithPath:cheminPosixDuFichierSource
	if class of proposedName is text then set proposedName to current application's NSString's stringWithString:proposedName
	set proposedName to proposedName's stringByReplacingOccurrencesOfString:"/" withString:":"
	set theDestURL to theFolderURL's URLByAppendingPathComponent:proposedName
	
	return theDestURL's |path| as text
end buildFullPath:inFolder:

#=====

-- Creates a new folder. There is no error if the folder already exists, and it will also create intermediate folders if required
on createFolder:posixPath # appelé par une instruction
	local theDestURL, theFileManager, theResult, theError
	set theDestURL to current application's |NSURL|'s fileURLWithPath:posixPath
	set theFileManager to current application's NSFileManager's |defaultManager|()
	set {theResult, theError} to theFileManager's createDirectoryAtURL:theDestURL withIntermediateDirectories:true attributes:(missing value) |error|:(reference) # EDITED: replaced specifier by reference
	if not (theResult as boolean) then error (theError's |localizedDescription|() as text)
	return theDestURL's |path| as text
end createFolder:

#=====

on dateStamp()
	tell (current date) to return (((its year) * 10000 + (its month) * 100 + (its day)) as text) & "_" & text 2 thru -1 of ((1000000 + (its hours) * 10000 + (its minutes) * 100 + (its seconds)) as text) & "_Report_Adam.numbers"
end dateStamp

#=====

In fact it’s not really final because I’m facing a wall under Sierra 10.12.1
The open handler fails.

I tested it with a code borrowed from AppleScript Language Guide

on open names
	# sel is the list of items dropped onto the script saved as an application
	
	set pathNamesString to "" -- Start with empty text string.
	repeat with i in names
		-- In this loop, you can perform operations on each dropped item.
		-- For now, just get the name and append a return character.
		set iPath to (i as text)
		set pathNamesString to pathNamesString & iPath & return
	end repeat
	-- Store list in open document, to verify what was dropped.
	tell application "TextEdit"
		set text of front document to pathNamesString
	end tell
	return
end open

I got an awful result.
If I drop four files of the same type on the droplet, I get a list of three pathnames.
If I drop a jpeg file, an xls one, a numbers one and an xlsx one I get only the path of the two Excel files.

I wish that some of you test the droplet on their side because I can’t decide if I found a bug or if something is odd on my system.

Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) mercredi 9 novembre 2016 16:32:03

Are these files you have downloaded from somewhere? Some could have the quarantine bit set – that would explain the behavior you are seeing.

Hello Shane

As far as my memory is OK, the files were created on my machine.

I will make an other attempt after creating some files to be sure.

I created six files from scratch and the script behaved flawlessly.
It seems that my memory was wrong. Not too surprising as some files are dated 2010.

Now I will have to search how to change the quarantine attribute of a file.

Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) jeudi 10 novembre 2016 11:04:00

Opening them is the usual method.

Thanks Shane.

I opened them 10th of times.

It appears that there was something wrong in my running system yesterdays.
Today the script correctly treat the set of files failing yesterdays.

Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) jeudi 10 novembre 2016 14:30:03

Hey Yvan, thanks for working on this :slight_smile:
When i drop a .xlsx file onto the droplet, i encounter the following error: The variable specifier is not defined. (-2753)

Change specifier to reference. Long story…

Hello Shane

It seems that some askers don’t understand what is the “title” [Open this Scriplet in your Editor] designed to.

When I clicked on [Open this Scriplet in your editor] specifier was automatically replaced by reference.

It’s when I copied/pasted that specifier was compiled as the name of a variable.

I’m not trying to find an excuse.
I made an error before posting to Adam a mail with the script attached. I forgot to re-run the script after removing the instructions [format]use Bridge Plus
load framework[/format]
which were in the “older” version.

The only change between the script posted here and the one which I sent to Adam is the addition of the handler “on Open”.
I really don’t understand why the one sent in the mail worked and the one sent here didn’t as both contained “specifier”.

Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) vendredi 11 novembre 2016 16:11:41

Use xattr, and remove com.apple.quarantine attribute from the file.

Thanks.

I found no file with the quarantine bit set in the folder from which I grabbed files to test the offending handler.

I remember that on 2016/11/09 one of the custom shortcuts added to Numbers was dead but I don’t know if it was before of after the problem with “on open”

What I know is that on 2016/11/10 the shortcut was available and “on open” behaved flawlessly.

I assume that something was corrupted during some scripts testing.

Sometimes my memory is fair enough to tell : don’t worry, reboot to see if the problem continue to strike but sometimes it doesn’t do its job :frowning:

Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) vendredi 11 novembre 2016 19:46:48

It does depend on their default editor, though.

What happened is an intermittent bug where sometimes AppleScript compiles with specifier instead of reference – but confusingly the resulting script will not compile in Script Editor. I’ve never been able to track down the exact cause (or solution), but it does seem to happen more often in scripts that use Script Libraries.