Paste content into Numbers then export as CSV

Question

Level 1

44 points

Paste content into Numbers then export as CSV

I am trying to make an Apple script that does the following:

Create a new Numbers sheet
Paste the clipboard contents into cell A1. The clipboard content is a list, so each line of the list should be in a different cell. The number of lines in the list is variable.
Every cell in the B column should have "missing". So if the clipboard list has 10 lines, the A column will have 10 rows and B1 - B10 will have "missing" in each cell.
Export the Numbers sheet on the desktop in CSV format with the name as "missing.csv".
Close the Numbers sheet without saving.

This is what I have so far:

tell application "Numbers"
	activate
	set thisDocument to make new document
end tell

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set value of cell "a1" to (the clipboard as text)
	set value of cell "b1" to "missing"
end tell

This is what I am stuck on:

a) Pasting the clipboard as a list, so each line of the clipboard appears in a seperate cell: At the moment, the whole list ends up in A1

b) Getting the B column to have the words "missing" so it matches up with the number of cells in column A.

c) Export as CSV and then closing the file.

Can someone give me some pointers? Thanks!

MacBook

Posted on Jan 25, 2020 11:47 PM

Reply

Answer 1

Best reply

Camelot

Level 9

54,339 points

Jan 27, 2020 2:52 PM in response to big_smile

> Is there away to increase the number of rows to match the pasted contents. Thanks!

Since the script knows how many paragraphs of data there is, it's easy to use that to extend (or contract) the table as needed.

Just add two lines to the script immediately after 'tell document 1 to tell sheet 1 to tell table 1'

set row count to (count importData)

set column count to 2

This will size the table to accommodate all the import data, and strip off the extra columns.

Reply

Answer 2

Camelot

Level 9

54,339 points

Jan 26, 2020 7:35 PM in response to big_smile

The problem is that you explicitly set the value of cell A1 to the contents of the clipboard, so it's doing exactly what you ask.

Instead, you likely need to iterate through the contents of the clipboard - which isn't hard to do. This should work - there are some optimizations that might apply if you're dealing with many (hundreds or thousands) of rows, otherwise this quick and dirty script should get you on the right path.

use AppleScript version "2.4" -- Yosemite (10.10) or later

use scripting additions

tell application "Numbers"

activate

set thisDocument to make new document

-- parse the clipboard data into paragraphs

set importData to paragraphs of (get the clipboard as text)

tell document 1 to tell sheet 1 to tell table 1

-- iterate for each paragraph

repeat with i from 1 to (count importData)

-- set the appropriate cell

set value of cell ("a" & i as text) to (item i of importData)

set value of cell ("b" & i as text) to "missing"

end repeat

end tell

Reply

Answer 3

Camelot

Level 9

54,339 points

Jan 26, 2020 11:03 PM in response to Camelot

oops - forgot the 'export as CSV' and 'close' elements of your request. Just add:

tell application "Numbers"

export document 1 to file (choose file name) as CSV

close document 1 saving no

end tell

to the end of the script

Reply

Answer 4

big_smile Author

Level 1

44 points

Jan 27, 2020 2:06 AM in response to Camelot

Hi Camelot, Thank you so much. There's only one issue. By default, a new spreadsheet has only 22 rows. So if the clipboard has more than 22 lines, and error occurs. it says:

error "Numbers got an error: Can’t set cell \"a23\" of table 1 of sheet 1 of document 1 to

Is there away to increase the number of rows to match the pasted contents. Thanks!

Also, is there a way to remove the unneeded columns, so the sheet only has two columns (A &B)

Thanks for your help. I greatly appreciate it.

Reply

Answer 5

Camelot

Level 9

54,339 points

Jan 28, 2020 9:51 PM in response to big_smile

> I can't mark your post as correct. It only gives me the options for helpful.

Only one post can be marked as solved/correct, and two has 'helpful'. But it's all good - I got the credits overall. Thanks :)

Reply

Answer 6

big_smile Author

Level 1

44 points

Jan 28, 2020 3:00 AM in response to Camelot

Thanks, that's perfect. For some reason, I can't mark your post as correct. It only gives me the options for helpful.

Thanks again for your help.

Reply

Paste content into Numbers then export as CSV

Similar questions