Batch processing hyperlinks inside a Numbers spreadsheet file

Scripting Forums AppleScript | Mac OS X
,
#1

Hi,

I have a Numbers spreadsheet on hand with hundreds of hyperlinks in it. They are all different links but all pointed to a same domain name. So right now I’m hoping to replace a new domain name for all those links. First I tried to look inside the built-in search feature, obviously it didn’t work. I can use this feature to replace text strings floating on the surface, but not the actual hyperlink underground. So I tried to dig into AppleScript, turns out no luck either. I tried three properties, which are “value”, “formula”, and “formatted value”, I can use none of them to find and replace hyperlinks inside the text string. I also tried the Hyperlink function, but that just makes the spreadsheet look horribly weird.

In the end, I came up with a workaround solution: copy and paste them to a blank Microsoft Excel file, and wrote a VBA macro to do the work for me. To me, the problem is temporarily solved, but the way I’m currently using is ugly, and that is how far it goes. So right now I’m still wondering, is there a possible way to do this replacing task inside Numbers just with AppleScript or JavaScript?

(update)

Moderator Nigel Garvey provided an answer at http://macscripter.net/viewtopic.php?pid=43585#p43585, thank you very much.

In case anyone only using Excel for Mac is hoping to solve this via VBA macro, here’s the code I previously used.

#2

Which version of Numbers are you using ?

In Numbers 2.3, I first checked the box “Désactiver tous les liens” and then I was able to use “Rechercher et remplacer”.
I leave the french wording because I am not sure of the English one

Yvan KOENIG (VALLAURIS, France) mercredi 28 janvier 2015 15:18:54

#3

Oh right.

It is the latest one from the Mac App Store, 3.5.2.

#4

I’m puzzled.
In this version I am unable to make the menu item Format > Insérer un lien (cmd + K) active.

I pasted the web link http://support.apple.com/kb/HT201068?viewlocale=fr_FRin a cell.
Find & Replace was able to edit the content of the cell so that it display http://support.apple.com/kb/HT201067?viewlocale=fr_FR
but clicking the cell continue to open the original page.

I searched in the French Help but found no entry upon Hyperlien which is the french word for Hyperlink.
As the link was inserted in B2, in C2 I inserted the formula =“” & B2.
I copied the result and pasted-value in D2
The result was the string http://support.apple.com/kb/HT201067?viewlocale=fr_FR
I am unable to find the equivalent of the setting “Automatically recognize weblink” available in the preferences pane of Numbers 2.3.

I hope that the info is available somewhere and that I’m not fully awake.

Yvan KOENIG (VALLAURIS, France) mercredi 28 janvier 2015 17:16:59

Bingo.
I selected the string http://support.apple.com/kb/HT201067?viewlocale=fr_FR in D2
The menu item Format > Insérer un lien (cmd + K) was active.
I triggered it and the string became an active link opening correctly the technical note HT201067.
Now I will try to do the job with AppleScript.

#5

At this time, I have this skeleton :



tell application "Numbers" to tell document 1 to tell active sheet to tell table 1
	set aValue to value of cell "B2"
	set aValue to "" & aValue
	if aValue contains "201067" then set aValue to my remplace(aValue, "201067", "201068")
	set value of cell "B2" to missing value
	set value of cell "B2" to aValue
	set selection range to range "B2"
	(*
	Now must put the cursor in the cell, double click it then select all and at last issue cmd + k
	I hope that this may be achieved with ASObjC.
	Maybe Shane Stanley will give the trick.
	*)
	tell application "System Events" to tell process "Numbers"
		set frontmost to true
		keystroke "a" using {command down}
		keystroke "k" using {command down}
	end tell
end tell

#=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d1}
	set l to text items of t
	set AppleScript's text item delimiters to d2
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end remplace

#=====

Alas, as you may read, I am unable to select the contents of the cell to apply the shortcut cmd + K

Yvan KOENIG (VALLAURIS, France) mercredi 28 janvier 2015 19:11:18

#6

Bad News

I asked Shane Stanley which responded that ASObjC can’t help to select the contents of a cell so I am unable to do the entire task.

All what I may do is to replace the old active links by their edited string version.
When this will be done, you will be forced to reactivate the links by hand one by one.
So I guess that you will have to use the Excel road.

Yvan KOENIG (VALLAURIS, France) jeudi 29 janvier 2015 16:12:28

#7

Thanks for answering my question. :slight_smile:

The other way I’m trying now is “the clipboard as «class HTML»”.

I saw an example by utilizing Perl to do the data reading and manipulation (http://stackoverflow.com/questions/2545289/getting-rtf-data-out-of-mac-os-x-pasteboard-clipboard), but I still haven’t figured a way to implement this.

#8

I am really puzzled.

If I copy a web address in the address area of Safari and paste it in a cell of Numbers, I get an active link.
If I ask Applescript to display what is available in the clipboard I get :

the clipboard as record
--> {«class utf8»:"http://macscripter.net/post.php?tid=43585", «class ut16»:"http://macscripter.net/post.php?tid=43585", string:"http://macscripter.net/post.php?tid=43585", Unicode text:"http://macscripter.net/post.php?tid=43585"}

If I If I copy a web address in the address area of Safari and run this script :

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set selection range to range "D2"
	tell application "System Events" to tell process "Numbers"
		set frontmost to true
		keystroke "v" using {command down}
	end tell
end tell

I get a text object.

What is making the difference between the two processes ?

Yvan KOENIG (VALLAURIS, France) jeudi 29 janvier 2015 17:26:58

#9

Hello. Yvan.

You’ll get a text object from your first script, if you don’t coerce the clipboard to a record.

Been there, done that. :confused:

#10

No, if I coerce to text I don’t get a text contents in the clipboard.

set theValue to "http://macscripter.net/post.php?tid=43585" as text
set the clipboard to theValue

the clipboard as record
--> {Unicode text:"http://macscripter.net/post.php?tid=43585", string:"http://macscripter.net/post.php?tid=43585", scrap styles:«data styl01000000000010000E00030000000C00000000000000», «class utf8»:"http://macscripter.net/post.php?tid=43585", «class ut16»:"http://macscripter.net/post.php?tid=43585"}

But this is not the problem.
What is puzzling me is why the clipboard is correctly pasted as a link when I paste by hand but I get a string object when it’s AppleScript which paste.

Yvan KOENIG (VALLAURIS, France) jeudi 29 janvier 2015 19:52:08

#11

Hello.

I can explain that with my own words, if not the technical terms at the moment. Numbers, in this case, informs the clipboard service, of the formats it will accept. I think those are ranked too. Maybe a link goes first to the clip board, and since it is a link, the contents of the clipboard is used as a link.
Applescript, doesn’t subscribe to the link format, so it gets just text.

If I say that Applescript has its private relationship with the clipboard (pasteboard), and Numbers has its private relationship with the clipboard, and that those relationships are under different contracts, with different limits, maybe that is a better analogy?

That is at least my understanding of how this works, at least it is how it works under drag and drop.

Edit
Another way to say it is that if I don’t subscribe to Le Figaro, then it won’t show up in my mailbox. (Os X is less prone to error, than the mail man.) :smiley:

#12

Hi.

I don’t know if this helps as I’m describing what happens with Numbers 2.3.

If you paste plain text representing a URL into a Numbers cell, Numbers only sees it as text. If you type a URL into a cell (not into the text bar above the table), Numbers analyses the content when the cursor leaves the cell, realises it’s a URL, and formats it as a link. You can copy and paste links too.

When a link is copied from Numbers to the clipboard, the link information appears to be stored in the RTF version of the data. This can be written to a temporary file, read back as text, edited, written back to the file, and lastly read back once again as data. In this form, it can be put back onto the clipboard and pasted into the Numbers selection.

This works pleasingly well on my machine, but may not necessarily do so with Numbers 3.x:


set oldDomain to "http://www.apple.com"
set newDomain to "http://macscripter.net"

-- Assuming the Numbers range containing the URLs is already selected, copy everything to the clipboard.
tell application "System Events"
	set frontmost of application process "Numbers" to true
	keystroke "c" using {command down}
end tell
delay 0.5

-- Extract the RTF data («class RTF<space>») from the clipboard.
set RTF to (the clipboard as «class RTF »)

-- The old "write to file, read back as ." trick.
set fRef to (open for access file ((path to temporary items as text) & "RTF from Numbers.rtf") with write permission)
try
	-- Write the RTF data to file and read it back as text.
	set eof fRef to 0
	write RTF to fRef
	set txt to (read fRef from 1 as «class utf8»)
	
	-- Edit the domain names (assuming they only appear in the links).
	set astid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to oldDomain
	set txt to txt's text items
	set AppleScript's text item delimiters to newDomain
	set txt to txt as text
	set AppleScript's text item delimiters to astid
	
	-- Write the edited text back to the file and read it back again as data.
	set eof fRef to 0
	write txt as «class utf8» to fRef
	set newRTF to (read fRef from 1 as «class RTF »)
end try
close access fRef

-- Put the new data onto the clipboard.
set the clipboard to {«class RTF »:newRTF}

-- Paste it into the Numbers selection.
tell application "System Events"
	set frontmost of application process "Numbers" to true
	keystroke "v" using {command down}
end tell
#13

Wow, this is just incredible.

It worked perfectly well on Numbers 3.5.2 too.

OS X and its AppleScript never ceases to amaze me when it comes to easy to use and powerfulness.

Thanks for everyone’s help.

#14

Thank you Nigel

I will copy your answer in a safe area.

Oops, I wrote too fast.
I made tests with a subset of the script :


-- Assuming the Numbers range containing the URLs is already selected, copy everything to the clipboard.
tell application "System Events"
	set frontmost of application process "Numbers" to true
	keystroke "c" using {command down}
end tell
delay .5

-- Extract the RTF data («class RTF<space>») from the clipboard.
set RTF to (the clipboard as «class RTF »)

-- The old "write to file, read back as ." trick.
set fRef to (open for access file ((path to temporary items as text) & "RTF from Numbers.rtf") with write permission)
try
	-- Write the RTF data to file and read it back as text.
	set eof fRef to 0
	write RTF to fRef
	
end try
close access fRef

I have two valid links in cells C2 & C3 :

http://macscripter.net/viewtopic.php?pid=178436#p178436
http://support.apple.com/kb/DL1782?viewlocale=fr_FR

I selected both cells and the instruction : set RTF to (the clipboard as «class RTF »)
failed with the error :
error “Il est impossible de rendre des données dans le type attendu.” number -1700 to item

I was running the script from the Editor and saw in the events log :
tell application “Script Editor”
the clipboard as «class RTF »

I thought that maybe the failure was due to the fact that the instruction was received by the edito so I edited it as :

tell me to set RTF to (the clipboard as «class RTF »)

The event log display now :
tell application “Script Editor”
the clipboard as «class RTF »
end tell
tell current application
path to temporary items as text
open for access file “SSD 500:private:var:folders:v2:40_tdtj114gbn1fw4xts8hzh0000gn:T:TemporaryItems:RTF from Numbers.rtf” with write permission
set eof 62 to 0
write «data RTF 7B5C727466315C616E7.

which is not what I was assuming but it seems to work OK.
As I am curious, I opened the temp folder, and double click the “RTF from Numbers.rtf” file .
The window didn’t contain the links but the error message :
error “Il est impossible de rendre des données dans le type attendu.” number -1700 to item

I made several attempts and each time, the file was containing what I copied just before running it

At last I had a look in Activity Monitor and discovered that the old Numbers '09 was active like the Numbers 3.5.2 one.
I killed it and this time, bingo, the script worked.

The lesson : never forget to check that only one of the two application is running because both name their process with the same value.

Yvan KOENIG (VALLAURIS, France) vendredi 30 janvier 2015 14:37:29

#15

I was again too optimistic.

The cells C2 thru C4 were containing :
these three active links.
http://macscripter.net/viewtopic.php?pid=178436#p178436
http://www.apple.com
http://support.apple.com/kb/DL1782?viewlocale=fr_FR

I ran the exact script posted by Nigel and got :
"
"
http://macscripter.net/viewtopic.php?pid=178436#p178436
http://macscripter.net
Yes, C2 is empty,
C3 contains what was in C2
C4 contains the link newDomain
The link to support.apple is gone.

What I don’t understand is the fact that the RTF from Numbers.rtf contains, after execution:
a blank paragraph then three cells with the wanted links.

http://macscripter.net/viewtopic.php?pid=178436#p178436
http://macscripter.net
http://support.apple.com/kb/DL1782?viewlocale=fr_FR

In Numbers I pressed cmd + Z to cancel the changes and this time I selected the range C2 thru C5 (C5 was empty)
The result is :
"
"
http://macscripter.net/viewtopic.php?pid=178436#p178436
http://macscripter.net
http://support.apple.com/kb/DL1782?viewlocale=fr_FR
Yes, C2 is empty and the links are in C3 thru C5.

Is there a problem due to the fact that I am using the system and the application in French ?

I post this message then I will reboot in English for see.

Identical behaviour whit the system and the app used in English.

Yvan KOENIG (VALLAURIS, France) vendredi 30 janvier 2015 15:27:59

#16

Hi Yvan.

The weakness I see in the script ” which sort of fits the symptoms you describe ” is that the data from the initial copy have to get to the clipboard before the clipboard’s read. Otherwise, the script works ” or tries to! ” on the clipboard’s previous contents.

You could try copying the Numbers selection manually and then, without moving the selection, running the script. If it produces the correct result, it could be that (the clipboard as «class RTF ») is reading the clipboard too soon on your computer.

#17

Hi Yvan,

I noticed the same issue, and tried to use «class HTML» with this script, the extra line break is immediately gone under this modification.

#18

Hi Nigel

(1) I ran the script upon a Numbers '09 document and it worked perfectly.

(2) When I made tests upon Numbers 3.5.2, I thought to the delay problem and tried to replace your delay 0.5 by delay 2 with the same behavior.
As I always try to be fair, in the script I disabled the code supposed to copy and as you wrote I copied by hand and ran the script without moving the selection. No change, the range in which the script paste is not changed but there is an extraneous field at beginning.

In the spreadsheet, after execution, the selected range is the original one, 3 cells when I start selecting 3 ones, 4 cells when I start with 4 selected ones.

As I am curious, just for see, I disabled the code changing the link. Always the same wrong behavior.

As I was puzzled, I redid the test with the subset of script which write the contents of the clipboard in the rtf file.
I renamed it “RTF from Numbers begin.rtf”
When I open it by double click, I see a table with four cells ” three with the original links, one empty ” which is exactly what was selected.
I ran the full script and renamed the final file as “RTF from Numbers final.rtf”
When I open it, it contains only three cells with three links (the second one is correctly edited).

If I understand, you don’t have Numbers 3.5.2 so you can’t make tests on your side.

If you wish I may send you off list the events logs and the two rtf files.

For me, it’s not an annoying problem. I have no use for this script. But I am puzzled by the fact that the original asker wrote that “It worked perfectly well on Numbers 3.5.2 too.”

I hope that he come back to this thread to tell us if he really got a correct result or if he just missed the fact that the datas are moved one row down.

Yvan KOENIG (VALLAURIS, France) vendredi 30 janvier 2015 19:06:29

#19

Hi Yvan.

I’ve now had an opportunity to unzip a copy of Numbers 3.something I quarantined back in September and have tried the script with that and your three URLs.

I get exactly the same results as you: a blank cell, followed by the previous contents of the first cell, followed by one or other of the other two entries.

Pasting the edited clipboard contents into Numbers manually has the same effect. Manually copying the original contents and immediately pasting them back gives the original contents. But opening the before- and after- text versions of the RTF data in TextWrangler shows them to be exactly the same except for the changed URLs.

The RTF file produced from the clipboard contents won’t open in TextEdit, whether derived from Numbers 3.x or from Numbers 2.3. But the edited RTF data on the clipboard can be pasted into a TextEdit document. The result in both cases is all three links, correctly edited, framed by their cell borders.

The only conclusion I can draw is that either Numbers 3.x is as dreadful internally as it is appalling to behold (which of course is impossible.) or it additionally makes use of some other kind of data when accepting pastes from the clipboard.

#20

Thanks Nigel.

Now that I know that the problem strikes on another machine I will file a report.
I’m not too optimistic about a result but at least I would have done what I may do.

Yvan KOENIG (VALLAURIS, France) dimanche 1 février 2015 20:23:49

PS.

As I am curious, I made a test replacing the instruction :
set the clipboard to {«class RTF »:newRTF}

by the instruction :
set the clipboard to “a” & return & “b” & return & “c”

With this version, the three values are pasted at the correct location.