I wish to simply copy a formula in one cell and paste it into a range of cells. For instance, cell F2 contains the formula, and I wish to copy the formula in F2 and in essence autofill or paste it into the range of cells F3:F434 using the duplicate command (from Standard Suite). Tried this but it aborts the app.
(telltable X ofsheet Y of Z) duplicatecell “F2” to range “F3:F434”
Causes: AppleScript Execution Error
Numbers got an error: Cells can not be copied. (-1717)
I’m sure a silly syntax or reference error, but can’t find examples of duplicate command anywhere.
Thanks in advance.
I don’t believe you can use the duplicate command to copy from one cell to another. The following worked on my Sonoma Computer:
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set theFormula to formula of cell "A2"
set the value of every cell in range "A3:A5" to theFormula
end tell
Unfortunately, you can only get a formula, you cannot set it. You have to increment the row number yourself —which you can do with a repeat loop— and then enter it as text. I don’t see any other way of doing this without using ui scripting. Numbers is not a good spreadsheet.
In this scenario, every cell in column E has a random number in it. Cell F2 has a formula to get the absolute number of cell E2.
This script gets the number of rows in the table and then cycles through every cell in column F that’s below row 2, and inserts the function from cell F2 but with the appropriate row argument.
tell application "Numbers"
set d1 to document 1
set s1 to sheet 1 of d1
set t1 to table 1 of s1
set lar to row count of t1 -- will be last row to set formula of
tell column "F" of t1
set fs to name of cell 2 --> "F2", source cell of original formula
set fsf to formula of cell fs --> "=ABS(E2)", original formula
end tell
end tell
-- get cell formula segments
set text item delimiters to {"(", ")"}
set iParts to text items of fsf --> {"=ABS", "E2", ""}
set cellFunction to text item 1 of iParts --> "=ABS"
-- separate letters from numbers in cell address
set cellName to middle item of iParts --> "E2", target cell
set text item delimiters to {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}
set cellLetter to first text item of cellName --> "E", column letter of target cell
set text item delimiters to cellLetter
set cellNumber to last text item of cellName --> "2", row number of target cell
tell application "Numbers"
activate
tell column "F" of t1
repeat with fy from (cellNumber + 1) to lar
set cefy to cellLetter & fy
set value of cell fy to cellFunction & "(" & cefy & ")" --> "=ABS(E10)" for last cell
end repeat
end tell
end tell
It’s not especially fast but it seems to work. You’ll have to tweak it to work with your formula.
If speed is a concern, then it might be quicker to just select the range in column F and use ui scripting to run the Table > Autofill Cells > Autofill Down menu command.
I’d have to see a sample of the table layout to get a good idea of how to approach a solution. Maybe you could put the formulas into a template and use that.
That said, you should be able to script selecting the table and sorting it using regular applescript. The copy formula wouldn’t be difficult to make work with ui scripting. You can also turn filtering on or off.
