Delete columns

Removing a column by script is easy, either via a 'Run AppleScript' action in Automator, or directly from AppleScript.

You need to keep in mind the hierarchy in Numbers. You will want to target a column in a table on a sheet in a document. If you are getting a "column undefined" or similar message then you probably haven't specified the hierarchy in a way that Numbers can understand

Here is a simple example:

tell application "Numbers"
	tell front document
		tell sheet "MySheetName" -- could also use active sheet
			tell table "MyTableName"
				remove column "E"
			end tell
		end tell
	end tell
end tell

1. Copy-paste into Script Editor (in Applications > Utilities) or into a 'Run AppleScript' action in Automator.
2. The first time, make sure Script Editor (and/or Automator is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.
3. Change MySheetName and MyTableName to exactly match the names you are using in your document. (leave the names between the double quotes).
4. Change the column letter to the one you want to remove.
5. With the document open click the <run> button in Script Editor (or Automator).

If you are doing this often then there are ways to trigger the script by a menu pick and/or keyboard shortcut rather than having to open Script Editor or Automator.

SG

SG's script can be revised to delete more than one column by repeating the the "remove column "E" line with E changed to name the column to be removed by 'this line'.

Take care to list the targeted columns from right to left as all columns to the right of the removed one will shift left as the current target is removed, leading eventually to this (intended) error message to my test run:

Letters in row 2 of the table indicate the original position (address) of that column.

With the Table returned to its original form (columns A to G) and script revised to list the removals in right to left order, running the script had this result.

Regards,

Barry

and Thank you, SG.

Hi Raymond,

I Haven't used Automator enough to tell you how to get it to do this task, but do have a few questions you might ask yourself about it.

Deleting a single column is a simple matter of activating the table by clicking any cell, taking the pointer to the column reference tab for the column to be deleted, clicking the v that appears near the right end of that tab and choosing delete column.

Deleting a contiguous group of columns is almost as simple:

Activate the table as above. Click on the tab for the first column to be deleted, then press and hold the command key and click on the tab for each of the other columns to be deleted. When all have been selected, place the pointer on one of the selected tabs, click the v and choose Delete Selected Columns.

Done.

Assuming that it is not always the same column(s) that will be deleted, you will need to tell Automator which ones it is this time (in a way that Automator understands).

As with many automated procedures, you may find the game is not worth the candle, and doing it 'manually' is just as efficient.

Regards,

Barry

Hello Barry and SG

I wonder if anyone could help me. I receive a spreadsheet every day from which I have been manually deleting 30 columns. I have been doing this for a couple of years. I would love to be able to automate this process. I have very little experience with Script Editor or Automator. Nonetheless from the code you posted above I have created the code below in Script Editor. When I run the code with my document open in Numbers I receive the following error message.

Syntax Error Expected end of line, etc. but found unknown token.

Hmm! What am I doing wrong ?

Many thanks for any assistance

Andrew

tell application "Numbers"

tell front document

tell sheet "Square"

tell table "Square"

remove column "AN"

remove column “AM”

remove column “AL”

remove column “AI”

remove column “AH”

remove column “AG”

remove column “AF”

remove column “AD”

remove column “AC”

remove column “AB”

remove column “AA”

remove column “Z”

remove column “X”

remove column “W”

remove column “T”

remove column “S”

remove column “R”

remove column “Q”

remove column “P”

remove column “N”

remove column “L”

remove column “K”

remove column “J”

remove column “I”

remove column “H”

remove column “G”

remove column “F”

remove column “E”

remove column “C”

remove column “B"

endtell

endtell

endtell

endtell

Andelou wrote:

Hello Barry and SG

Syntax Error Expected end of line, etc. but found unknown token.

Hmm! What am I doing wrong ?

It looks as if you have "curly" quotes in the script whereas you need "straight" quotes.

Use "

Not ”

SG

You can copy-paste the script you are trying to use into a post here but make sure you paste into a code insertion block. Click the < > icon below the compose menu here and then click and paste within the shaded area that it creates.

SG

instead of repeatedly typing columns to be removed in a script as new lines, you can add the column numbers to a set and repeat over it. (notice they are in reverse order since the Column 10 becomes Column 9 if you delete any column before it. so you always want to count from high to low so the columns remain in their original places while working like this )

tell application "Numbers"
	tell front document
		tell sheet "MySheetName" -- could also use active sheet\
			tell table "MyTableName"
				set myColumns to {9, 8, 7, 6, 5}
				repeat with theColumn in myColumns
					remove column theColumn
				end repeat
			end tell
		end tell
	end tell
end tell

If all your columns are sequential, meaning you want to start at E (col 5) and delete to Column I (5 columns) you can just delete column 5, 5 times

tell application "Numbers"
	tell front document
		tell sheet "MySheetName" -- could also use active sheet
			tell table "MyTableName"
				repeat 5 times
					remove column 5
				end repeat
			end tell
		end tell
	end tell
end tell

Jason



Andelou wrote:

Thanks SG - I've amended above code to straight quotes and now

Syntax Error: Expected end of line, etc. but found end of script.

Paste the script in here in the shaded area that appears after you click the < > icon below.

Then we can help you find where it needs cleaning it.

There are usually ways to shorten a script or make it more efficient.

But your simple approach should work well.

The script probably has an extra character here or there, or a line was left out, easy to spot if you post the entire script with that "code insertion" icon.

SG

tell application "Numbers"

tell front document

tell sheet "Square"

tell table "Square"

remove column "AN"

remove column "AM"

remove column "AL"

remove column "AI"

remove column "AH"

remove column "AG"

remove column "AF"

remove column "AD"

remove column "AC"

remove column "AB"

remove column "AA"

remove column "Z"

remove column "X"

remove column "W"

remove column "T"

remove column "S"

remove column "R"

remove column "Q"

remove column "P"

remove column "N"

remove column "L"

remove column "K"

remove column "J"

remove column "I"

remove column "H"

remove column "G"

remove column "F"

remove column "E"

remove column "C"

remove column "B"

endtell

endtell

endtell 

endtell

You had endtell instead of end tell (missing spaces)

Try something like this:

tell application "Numbers"
	tell front document
		tell sheet "Square"
			tell table "Square"
				remove column "AN"
				remove column "AM"
				remove column "AL"
				remove column "AI"
				remove column "AH"
				remove column "AG"
				remove column "AF"
				remove column "AD"
				remove column "AC"
				remove column "AB"
				remove column "AA"
				remove column "Z"
				remove column "X"
				remove column "W"
				remove column "T"
				remove column "S"
				remove column "R"
				remove column "Q"
				remove column "P"
				remove column "N"
				remove column "L"
				remove column "K"
				remove column "J"
				remove column "I"
				remove column "H"
				remove column "G"
				remove column "F"
				remove column "E"
				remove column "C"
				remove column "B"
			end tell
		end tell
	end tell
end tell

You should be able to copy-paste the above directly into Script Editor (in Applications > Utilities) and run it from there.

Once you get that working you can try shortening and optimizing the script if you want. But one step at a time.😀

SG

Thanks SG - so I corrected the code as you suggested and tried to run it through Script Editor with document open in Numbers and now I get:

Script Error

Numbers got an error: Can’t get table "Square" of sheet "Square" of document 1.

My document is named 'Square' and sheet is also named 'Square'.

If you are sure you have the spelling exactly right then it could be a permissions thing.

Try going to System Preferences > Security & Privacy > Privacy > Accessibility and making sure Script Editor (and I think possibly Numbers as well) are listed and checked there.

SG

Thanks SG - I've amended above code to straight quotes and now

Syntax Error: Expected end of line, etc. but found end of script.

Any ideas ?

Another approach:

Copy column A in a new table (on a new sheet) and delete the (old) table manually, instead of deleting all these columns.

Easy and quick.

Paul.