Numbers - Iterate Through Sheet & Delete Rows

Scripting Forums AppleScript | Mac OS X
,
#1

Hi,

I’m trying to use the script below to iterate through a numbers spreadsheet. If it finds the value “TRUE” in column F, it deletes that row.

When I run it on my spreadsheet, the script ends instantly, nothing happens. Can anyone see an error that I might be missing?

tell application "Numbers"
	set thisDocument to front document
	activate
	tell thisDocument to tell sheet 1 to tell table 1
		repeat with i from (count rows) to 1 by -1
			if value of cell i of column "F" is in {"TRUE"} then
				set selection range to range (name of cell i of column 1 & ":" & name of cell i of column 2)
				remove row of the selection range
			end if
		end repeat
	end tell
end tell
#2

Try to edit a single instruction:

if (value of cell i of column "F" as text) is in "true" then

or

if value of cell i of column "F"  is true then

You may use a shorter script :

tell application "Numbers"
	activate # More logical to activate before extracting infos upon front document
	set thisDocument to front document
	tell thisDocument to tell sheet 1 to tell table 1
		repeat with i from (count rows) to 1 by -1
			if value of cell i of column "F" is true then
				remove row i
			end if
		end repeat
	end tell
end tell

Yvan KOENIG running Sierra 10.12.6 in French (VALLAURIS, France) mardi 25 juillet 2017 17:53:42

#3

Thank you Yvan!
I just discovered that the problem - the word I’m trying to delete is the word “TRUE”.
However, TRUE is a boolean value, which is causing the problem.

Is there a way i can escape the word TRUE or anything like that? I have tried storing “TRUE” as in a variable as text but this hasn’t helped.

Adam

#4

To be clear, I’m trying to delete a row based on whether or not cell F has the word “TRUE” written in it.

The script below (based on the one Yvan gave me) would usually work, but because TRUE is a boolean value it won’t.

For instance, if i wanted to delete every row in the sheet that had the word “Teapot” in column F, the script below would work fine, but it falls over if you use it to delete for “TRUE”

tell application "Numbers"
	
	activate # More logical to activate before extracting infos upon front document
	set trueDis to "TRUE" as text
	set thisDocument to front document
	tell thisDocument to tell sheet 1 to tell table 1
		repeat with i from (count rows) to 1 by -1
			if value of cell i of column "F" = trueDis then
				remove row i
			end if
		end repeat
	end tell
end tell
#5

Problem Solved!
write “TRUE” as true and it works

tell application "Numbers"
	
	activate # More logical to activate before extracting infos upon front document
	set trueDis to true
	set thisDocument to front document
	tell thisDocument to tell sheet 1 to tell table 1
		repeat with i from (count rows) to 1 by -1
			if value of cell i of column "F" = trueDis then
				remove row i
			end if
		end repeat
	end tell
end tell
#6

As you discovered, the problem was that when you reach the searched cell,

value of cell i of column "F"

is not a string but is the boolean true.

This is why I proposed the syntax :

if (value of cell i of column "F" as text) is in "true" then

But alas I left a typo. The correct syntax is :

if (value of cell i of column "F" as text) is  "true" then

But coercing the value into a string is time consuming and comparing two strings is time consuming too.
Comparing ‘something’ to a boolean is faster and more faster if ‘something’ is also a boolean.

Would be a good idea to update from 10.12.4 to 10.12.6 which correct some oddities.

Yvan KOENIG running Sierra 10.12.6 in French (VALLAURIS, France) mardi 25 juillet 2017 19:25:06