Sunday, November 19, 2017

#1 2017-08-23 06:01:56 am

Adam239
Member
Registered: 2016-10-14
Posts: 70

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:
http://www.somesite.org/888977/somestuf … endofstuff
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?

Thanks!


running Sierra 10.12.5

Offline

 

#2 2017-08-23 04:13:28 pm

t.spoon
Member
From:: BFE, Massachusetts
Registered: 2013-01-13
Posts: 206

Re: Numbers - Extracting ID From URLs

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.

Last edited by t.spoon (2017-08-23 04:14:01 pm)


Hackintosh built February, 2012 |  Mac OS Sierra
GIGABYTE GA-Z68X-UD3H-B3 | Core i5 2500k | 16 GB DDR3 | GIGABYTE Geforce 1050 TI 4GB
250 GB Samsung 850 EVO | 4 TB RAID
Dell Ultrasharp U3011 | Dell Ultrasharp 2007FPb

Offline

 

#3 2017-08-24 04:17:20 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4422

Re: Numbers - Extracting ID From URLs

t.spoon wrote:

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:

Applescript:

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


NG

Offline

 

#4 2017-08-24 06:13:11 am

Adam239
Member
Registered: 2016-10-14
Posts: 70

Re: Numbers - Extracting ID From URLs

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!

Applescript:


set theColumn to "A"
tell application "Numbers"
   activate
   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:

Applescript:


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


running Sierra 10.12.5

Offline

 

#5 2017-08-24 07:56:30 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4422

Re: Numbers - Extracting ID From URLs

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.:

Applescript:

set endItems to text item -1 of SearchText

And:

Applescript:

set beginningToEnd to text item 1 of endItems


NG

Offline

 

#6 2017-08-24 12:53:43 pm

t.spoon
Member
From:: BFE, Massachusetts
Registered: 2013-01-13
Posts: 206

Re: Numbers - Extracting ID From URLs

Actually, it's been a few years now since iWork ’09 was replaced by the practically useless free version.



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.

https://www.imore.com/apple-makes-iwork … e-everyone

Last edited by t.spoon (2017-08-24 12:54:31 pm)


Hackintosh built February, 2012 |  Mac OS Sierra
GIGABYTE GA-Z68X-UD3H-B3 | Core i5 2500k | 16 GB DDR3 | GIGABYTE Geforce 1050 TI 4GB
250 GB Samsung 850 EVO | 4 TB RAID
Dell Ultrasharp U3011 | Dell Ultrasharp 2007FPb

Offline

 

#7 2017-08-24 03:07:49 pm

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4422

Re: Numbers - Extracting ID From URLs

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


NG

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)