I am (still) a beginner in AppleScript. Numbers version: 6.2.1. macOS 10.15.2.
There are a certain amount of Numbers documents I will be opening, all of which will have one table in them, most with no more than 10 rows.
I would like to put the same value in a number of cells, all of which appear in the same column (eg, the same value entered in to E2, E3, E4, E5 in one Numbers document, while in another it might be E2, E3, E4, E5, E6, E7).
I know how to use the prompt command to set a value to a variable, eg
set NumberOfCells to the text returned of (display dialog "What is the number of cells: " default answer "5")
set CellValue to the text returned of (display dialog "What value would you like to put in the cells? " default answer "X")
And I get put an individual value into an individual cell, eg
set value of cell "A1" to CellValue
But I cannot workout how to set up / use the repeat command to put the same value in a range of cells which I specify.
You’re spoilt for choice. You can do your own repeat something like this:
-- Set these values from your input.
set theNumber to 22
set theColumn to "E"
set startRow to 2
set endRow to 7
-- Repeat loop:
tell application "Numbers"
tell document 1
tell table 1 of sheet 1
repeat with i from startRow to endRow
set value of cell i of column theColumn to theNumber
end repeat
end tell
end tell
end tell
Or with an AppleScript-style range specifier:
-- Set these values from your input.
set theNumber to 22
set theColumn to "E"
set startRow to 2
set endRow to 7
-- AppleScript range specifier:
tell application "Numbers"
tell document 1
tell table 1 of sheet 1
set value of cells startRow thru endRow of column theColumn to theNumber
end tell
end tell
end tell
Or with one of Numbers’s own ‘range’ specifiers:
-- Set these values from your input.
set theNumber to 22
set theRange to "E2:E7"
-- Cells of a Numbers 'range':
tell application "Numbers"
tell document 1
tell table 1 of sheet 1
set value of cells of range theRange to theNumber
end tell
end tell
end tell
The latter two should be slightly faster than the repeat.
repeat
set NumberOfCells to the text returned of (display dialog "What is the number of cells (4 or 6): " default answer 4)
if NumberOfCells is in {"4", "6"} then exit repeat
beep
end repeat
set CellValue to the text returned of (display dialog "What value would you like to put in the cells? " default answer "X")
set firstRow to 2
set lastRow to firstRow - 1 + (NumberOfCells as integer)
set theRange to "E" & firstRow & ":E" & lastRow
tell application "Numbers"
tell document 1 to tell table 1 of sheet 1
set value of cells of range theRange to CellValue
end tell
end tell
set cancelBtn to "Cancel"
set btn4 to "4"
set btn6 to "6"
set NumberOfCells to button returned of (display dialog "What is the number of cells to fill: " buttons {cancelBtn, btn6, btn4} default button btn4 cancel button cancelBtn)
set CellValue to the text returned of (display dialog "What value would you like to put in the cells? " default answer "X")
set firstRow to 2
set lastRow to firstRow - 1 + (NumberOfCells as integer)
set theRange to "E" & firstRow & ":E" & lastRow
tell application "Numbers"
tell document 1 to tell table 1 of sheet 1
set value of cells of range theRange to CellValue
end tell
end tell
set cancelBtn to "Cancel"
set btn5 to "5"
set btn7 to "7"
set lastRowToFill to button returned of (display dialog "What is the index of the last row to fill: " buttons {cancelBtn, btn7, btn5} default button btn5 cancel button cancelBtn)
set CellValue to the text returned of (display dialog "What value would you like to put in the cells? " default answer "X")
set theRange to "E2:E" & lastRowToFill
tell application "Numbers"
tell document 1 to tell table 1 of sheet 1
set value of cells of range theRange to CellValue
end tell
end tell
Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) vendredi 17 janvier 2020 14:06:20
As I am lazy, here is a version which spare user’s activity.
There is no need to enter the index of the lower cell to fill.
It’s supposed to be stored in the cell “E1”.
set CellValue to the text returned of (display dialog "What value would you like to put in the cells? " default answer "X")
tell application "Numbers"
tell document 1 to tell table 1 of sheet 1
-- The index of the lower cell to fill is stored in E1"
set lastRowToFill to value of cell "E1" as integer
set theRange to "E2:E" & lastRowToFill
set value of cells of range theRange to CellValue
end tell
end tell
Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) vendredi 17 janvier 2020 22:38:35