Thursday, September 21, 2017

#1 2017-09-04 04:32:01 am

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

Numbers: Edit Cell Value if it contains keyword from List

Hi There,

I'm trying to make a script that looks at the value of a cell, if it contains any of the words in a list, then it changes the value of another cell.

So far I have the script below, it'll work with single words, but not with a list of words.

Can anyone see where I might be going wrong?

Applescript:

set checkedWords to {"Pineapple", "peaches"}

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
   set theColumn to "E"
   set theNotesColumn to "G"
   
   
   if value of cell 2 of column theColumn is in checkedWords then
       set the value of cell 2 of column theNotesColumn to "Fruit - "
   end if
end tell


running Sierra 10.12.5

Offline

 

#2 2017-09-05 10:50:08 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3192

Re: Numbers: Edit Cell Value if it contains keyword from List

I'm puzzled because here your script behaves flawlessly as is.
At first I assumed that you may be facing a case problem and I was ready to add a couple
case considering

end considering

I  just added what - from my point of vue - is an enhancement :

Applescript:

set checkedWords to {"Pineapple", "peaches", "apricot"}

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
   set theColumn to "E"
   set theNotesColumn to "G"
   
   
   if value of cell 2 of column theColumn is in checkedWords then
       set the value of cell 2 of column theNotesColumn to "Fruit - "
   else # ADDED
       clear cell 2 of column theNotesColumn # ADDED
   end if
end tell

Yvan KOENIG running Sierra 10.12.6 in French (VALLAURIS, France) mardi 5 septembre 2017 17:42:51

Offline

 

#3 2017-09-06 08:06:43 am

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

Re: Numbers: Edit Cell Value if it contains keyword from List

Hi Yvan,

I think I worked out what the issue was with this.
The script works if the target cell only contains one word. If it contains a phrase, it looks like this wont work.

For example, this script would work: Example 1

Applescript:

set checkedWords to {"Pineapple", "peaches", "apricot"}
set check to "peaches"
set flag to false
if check is in checkedWords then set flag to true
return flag

But this script won't: Example 2

Applescript:

set checkedWords to {"Pineapple", "peaches", "apricot"}
set check to "i like peaches"
set flag to false
if check is in checkedWords then set flag to true
return flag

neither will this one: Example 3

Applescript:

set checkedWords to {"Pineapple", "peaches", "apricot"}
set check to "i like peaches"
set flag to false
if checkedWords contains check then set flag to true
return flag

I found that the Example 3 method worked (if list contains  x then do something) but I needed to loop through each item of the list and query each individually.

If you have a list with 2000+ items in it, this can take a long time.

Eventually, I solved my issue by using a sub routine to remove everything except the key word I was comparing to the huge list. If you are only asking if a list contains a single specific word, then the response is almost instant.


running Sierra 10.12.5

Offline

 

#4 2017-09-06 11:28:31 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3192

Re: Numbers: Edit Cell Value if it contains keyword from List

Oops, I didn't guess that the source cell may  contain several words.

I get the wanted result with :

Applescript:

property checkedWords : {"Pineapple", "peaches", "apricot"}

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
   set theColumn to "E"
   set theNotesColumn to "G"
   
   set maybe to value of cell 2 of column theColumn
   set found to false
   repeat with aWord in my checkedWords
       if aWord is in maybe then
           set the value of cell 2 of column theNotesColumn to "Fruit - "
           set found to true
           exit repeat
       end if
   end repeat
   if not found then # ADDED
       clear cell 2 of column theNotesColumn # ADDED
   end if
end tell

If my ideas were more clear, I guess that I would be able to build a faster code using NSIntersectionRange in an ASObjC piece of code.
Alas, at this time I am unable to do that. Maybe Shane or Nigel would read this thread and give a valuable code.

Yvan KOENIG running Sierra 10.12.6 in French (VALLAURIS, France) mercredi 6 septembre 2017 18:28:23

Offline

 

#5 2017-09-06 06:41:14 pm

Shane Stanley
Member
From:: Australia
Registered: 2002-12-07
Posts: 5125

Re: Numbers: Edit Cell Value if it contains keyword from List

You could use this:

Applescript:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions

set checkedWords to {"Pineapple", "peaches", "apricot"}
set checkedWordsSet to current application's NSSet's setWithArray:checkedWords
set check to "i like peaches"
set found to my checkIn:check forCheckWords:checkedWordsSet

on checkIn:check forCheckWords:checkedWordsSet
   set checkSet to current application's NSSet's setWithArray:(words of check)
   return checkedWordsSet's intersectsSet:checkSet
end checkIn:forCheckWords:

But I imagine it would only make sense if checkWords was a long list.


Shane Stanley <sstanley@myriad-com.com.au>
www.macosxautomation.com/applescript/apps/

Offline

 

#6 2017-09-07 04:13:45 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3192

Re: Numbers: Edit Cell Value if it contains keyword from List

Thanks Shane

The asker wrote about a list of 3000 items so I guess that intersect may be efficient.

My memory is not perfectly clear. Is intersectsSet case sensitive ?
If it is it would be useful to convert the two sets into Uppercase items.

Yvan KOENIG running Sierra 10.12.6 in French (VALLAURIS, France) jeudi 7 septembre 2017 11:13:03

Offline

 

#7 2017-09-07 04:38:15 am

Shane Stanley
Member
From:: Australia
Registered: 2002-12-07
Posts: 5125

Re: Numbers: Edit Cell Value if it contains keyword from List

Yvan Koenig wrote:

Is intersectsSet case sensitive ?



Yes, any comparison will be case-sensitive, unless specified otherwise.


Shane Stanley <sstanley@myriad-com.com.au>
www.macosxautomation.com/applescript/apps/

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)