Numbers - Extracting ID From URLs

Hi there,

I have a long list of URLs in a column in Numbers.

The URL Structure looks something like this:
I need to just get the 6 digit ID from each URL in the list.

I could maybe do UI scripting to execute a search and replace… but I was wondering if there was a cleaner method?


Before I write something -

What logic do you want for parsing the URLS? Like, are they possibly going to also contain 5 and 7 digit numbers, and you just need the 6 digit numbers? Or can I just extract all consecutive digits following the first digit I find?

Also, do you want to specify what column I’m finding these in, or should I just use Column A and you can adapt from there?

FYI for other MacScripter users, if you were passing this because you don’t have Numbers: Apple made iWork free a few months ago.

Ah. But not Sierra-capable Macs. :wink:

Actually, it’s been a few years now since iWork ’09 was replaced by the practically useless free version. I gather the latter’s now improved a little and regained some of the former’s scriptability (albeit it with some changes). App Store’s stopped nagging me about iWork updates since Sierra came out, so I presume the latest version only runs on that system.

A simple solution to Adam239’s query, given the information provided so far, would be as below, but I don’t know if the Numbers 2.3 syntax works with the Sierra version:

tell application "Numbers"
	-- NB. Numbers 2.3 syntax and assuming the values of interest are in column "A".
	set cellValues to value of cells of column "A" of table 1 of sheet 1 of document 1
end tell

set theIDs to {}
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to "/"
repeat with thisValue in cellValues
	if (thisValue begins with "http") then set end of theIDs to text item 4 of thisValue
end repeat
set AppleScript's text item delimiters to astid

return theIDs

Hi guys,

Thank you both for taking the time to reply to my query. I managed to solve the issue by adding each URL in the column to a list using the following, although using the text delimiters looks like a much simpler approach!

set theColumn to "A"
tell application "Numbers"
	tell document 1 to tell sheet 2 to tell table 1
		repeat with i from (count rows) to 1 by -1
			set rawURLs to rawURLs & the value of cell i of column theColumn
		end repeat
	end tell
end tell

Then, I used a sub routine I found online to extract the ID from each item in the list:

to extractID(SearchText, startText, endText)
	set tid to AppleScript's text item delimiters 
	set AppleScript's text item delimiters to startText 
	set endItems to text of text item -1 of SearchText  
	set AppleScript's text item delimiters to endText 
	set beginningToEnd to text of text item 1 of endItems 
	set AppleScript's text item delimiters to tid 
	return beginningToEnd
end extractID

Hi Adam239.

I’m not sure how you’re using the handler to extract the ID, but if you’re getting what you need, that’s great.

Just to point out that ‘text of text item’ in the handler is wrong. It should just be ‘text item’, ie.:

set endItems to text item -1 of SearchText


set beginningToEnd to text item 1 of endItems

I’ve barely ever touched iWork apps and don’t know the history at all, but there was some announcement about the apps being made free this April, not years ago.

I needed Numbers to open a file someone sent me earlier this year and went to the app store and they wanted $10 for it; a few weeks later I saw this announcement in the news, and it had switched to free in the App store for me.

Hi t.spoon.

I see we’re both partly right. iWork 2013 was indeed free, but only to people who’d already bought one of the previous versions or whose Macs were bought from October 2013 onwards or whose iOS devices were bought from September that year. It was made free to all with compatible macOS and iOS devices in April this year.

Wikipedia iWork article