AppleScript for copy and paste in Numbers.

Question

Level 1

5 points

AppleScript for copy and paste in Numbers.

Dear All

Firstly I still practical and learning AppleScript.

Referring this AppleScript as I understood, it should be create table with header and also, it will be reading and paste data each row until end of records (equal count row)

tell application "Numbers"

tell front sheet of front document

set myOriginaltable to front table

set myRows to count the rows in myOriginaltable -- count rows

set myCols to count the columns in myOriginaltable -- count column

set myNewtable to make new table with properties {column count:myCols, row count:myRows} -- create table

-- create header --

set value of cell 1 of row 1 of myNewtable to "!TIMEACT"

set value of cell 2 of row 1 of myNewtable to "BILLINGSTATUS"

set value of cell 3 of row 1 of myNewtable to "DATE"

set value of cell 4 of row 1 of myNewtable to "DURATION"

set value of cell 5 of row 1 of myNewtable to "EMPLOYEE"

set value of cell 6 of row 1 of myNewtable to "ITEM"

set value of cell 7 of row 1 of myNewtable to "JOB"

set value of cell 8 of row 1 of myNewtable to "NOTE"

repeat with i from 2 to myRows -- start write in row 2

set myDate to value of cell 3 of row i of myOriginaltable

set myDur to value of cell 5 of row i of myOriginaltable

set myItem to value of cell 2 of row i of myOriginaltable

set myJob to value of cell 7 of row i of myOriginaltable

set myNote to value of cell 8 of row i of myOriginaltable

tell row i of myNewtable

set value of cell 1 to "!TIMEACT"

set value of cell 2 to "1"

set value of cell 3 to myDate

set value of cell 4 to myDur

set value of cell 5 to ""

set value of cell 6 to myItem

set value of cell 7 to myJob

set value of cell 8 to myNote

end tell

end repeat

end tell

I had question is, if would like to read data each row but paste in only 2 columns and repeat it until end of data in column (equal count row). Please suggest.

Raw Data

Expectation

MacBook Pro (13-inch, 2017, 4 TBT3), macOS High Sierra (10.13.3), null

Posted on Feb 27, 2018 4:07 AM

Reply

Answer 1

Best reply

SGIII

Level 8

35,288 points

Feb 27, 2018 6:54 AM in response to sarit.s

I used your first example because I couldn't figure out the source for Category in your second.

It is possible to read many values from a table at once. It's slow, however, to set cells's value one by one. In general it's MUCH faster to paste tabbed values via the clipboard. Not only is that faster, but you don't need to script the number of columns and rows. The table will expand automatically to accommodate the data.

The script below shows one way to do that, and also how to select a subset of the columns from the original table.

SG

set {begCol, endCol} to {3, 5} -- first and last columns containing data to transfer

set tgtCol to 1 -- leftmost target column (where you want data to start) in new table

tell application "Numbers"

tell front document's active sheet

tell table 1

set vv to rows's cells's value

set vvPasteStr to my listToStr(vv, tab, begCol, endCol)

end tell

set the clipboard tovvPasteStr

set myNewTable to make new table ¬

with properties {column count:2, row count:2, header column count:0}

tell myNewTable to set selection range to row 1's cell tgtCol

activate

tell application "System Events" to keystroke "v" using {option down, shift down, command down}

end tell

to listToStr(lst, delim, begCol, endCol)

set text item delimiters to delim

set pasteStr to ""

repeat with i in lst

set dataLine to i'sitemsbegCol thru endCol as text

set pasteStr to pasteStr & (i'sitemsbegCol thru endCol as text) & return

end repeat

return pasteStr

end listToStr

Reply

Answer 2

sarit.s Author

Level 1

5 points

Feb 28, 2018 3:34 AM in response to SGIII

Dear SGIII

Thank you for your suggest. Also, I am apologize for make you miss understood in my example.Your code it’s better for solve my problem soon. So, just in case I would like to change code like this its maybe make you fully guess my expectation.

tell application "Numbers"

tell front sheet of front document

set myOriginalTable to front table

-- version 1.0.0

set cRows to count the rows in myOriginalTable

set tCols to 8

set tRows to cRows * tCols

set myNewtable to make new table with properties {column count:tCols, row count:tRows}

set value of cell 1 of row 1 of myNewtable to "cardNo"

set value of cell 2 of row 1 of myNewtable to "emCode"

set value of cell 3 of row 1 of myNewtable to "emName"

set value of cell 4 of row 1 of myNewtable to "itemCode"

set value of cell 5 of row 1 of myNewtable to "itemName"

set value of cell 6 of row 1 of myNewtable to "effDate"

set value of cell 7 of row 1 of myNewtable to "amt"

set value of cell 8 of row 1 of myNewtable to "remark"

repeat with i from 2 to cRows -- maybe this position incorrect and require change --

set myItemCode to value of cell 1 of row i of myOriginalTable

set myItemCode to value of cell 1 of row i of myOriginalTable

set myAmt to value of cell 2 of row i of myOriginalTable

tell row i of myNewtable

set value of cell 1 to myCardNo

set value of cell 4 to myItemCode

set value of cell 7 to myAmt

end tell

end repeat

end tell

endtell

Example

Expectation

Reply

Answer 3

sarit.s Author

Level 1

5 points

Feb 28, 2018 4:08 AM in response to sarit.s

Dear All

I have changed script a bit but still incorrect. My question is how to get the value from range B1:Q1 to "itemCode" and value from range B2:Q17 to "Amount"

tell application "Numbers"

tell front sheet of front document

set myOriginalTable to front table

set itemCode to range "B1:Q1"

set Amount to range "B2:Q400"

set setCols to 8

set myNewTable to makenewtablewith properties ¬

{column count: setCols, row count: count of itemCode) + 1, header column count:0}

tell myNewTable

set value of cell 1 of column "A" to "cardNo"

set value of cell 1 of column "B" to "emCode"

set value of cell 1 of column "C" to "emName"

set value of cell 1 of column "D" to "itemCode"

set value of cell 1 of column "E" to "itemName"

set value of cell 1 of column "F" to "effDate"

set value of cell 1 of column "G" to "amt"

set value of cell 1 of column "H" to "remark"

set x to 2

repeat with eachItemCode in itemCode

set value of cell x of column "D" to eachItemCode

set x to (x + 1)

end repeat

set x to 2

repeat with eachAmount in Amount

set value of cell x of column "G" to eachAmount

set x to (x + 1)

end repeat

end tell

Thank you

Reply

AppleScript for copy and paste in Numbers.

Similar questions