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?

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

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

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

You could use this:

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.

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

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