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?
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
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 :
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
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
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
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
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.
Oops, I didn’t guess that the source cell may contain several words.
I get the wanted result with :
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
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.