Thursday, July 9, 2020

#1 2020-04-03 05:04:18 am

vw-as
Member
Registered: 2015-08-25
Posts: 11

Adding an increasing number to a Numbers document

I received some very helpful suggestions on how to put the same value in a number of cells all in the same column (https://macscripter.net/viewtopic.php?p … 93#p199993).

Now I would like to put an increasing number in those cells (for example: 1, 2, 3, 4, 5).

To do so manually in Numbers is very easy, but I would like to automate the process with AppleScript.

I understand (a little, as a beginner) about using a counter and also about using repeat, but cannot work out how to use these with Numbers adding the increasing number to the rows.

For example,

Before numbering:
Column 1, Column 2, Column 3
1/4/20, calls and emails,
1/4/20, discuss matter,
2/4/20, complete work,

After numbering:
Column 1, Column 2, Column 3
1/4/20, calls and emails,1
1/4/20, discuss matter,2
2/4/20, complete work,3

Any help would be gratefully received.

Offline

 

#2 2020-04-03 05:53:37 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4546

Re: Adding an increasing number to a Numbers document

Here are two proposals.

Using standard AppleScript. Does the job but may be slow when the table is large.

Applescript:

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
   set n to 1
   tell column 3
       repeat with i from 1 to count rows
           set value of cell i to n
           set n to n + 1
       end repeat
   end tell
end tell

Using GUI scripting. I know that some users dislike it but it's hugely faster for large tables.

Applescript:

tell application "Numbers"
   set row_loc to localized string "ROW" from table "TSCalculationEngine"
   set autofillCell_loc to localized string "Autofill Cells" from table "TSTables"
   tell document 1 to tell sheet 1 to tell table 1
       set firstRow to 5 -- of course you may replace by 1
       tell column 3
           set value of cell firstRow to "=" & row_loc & "() -" & firstRow & "+1"
           set theRange to (name of cell firstRow & ":" & name of cell -1)
       end tell
       set selection range to range theRange
   end tell
end tell
tell application id "com.apple.systemevents" to tell process "Numbers"
   set frontmost to true
   tell menu bar 1 to tell menu bar item 6 to tell menu 1 to tell menu item autofillCell_loc to tell menu 1 to click menu item 3
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) vendredi 3 avril 2020  13:53:22

Offline

 

#3 2020-04-03 07:29:55 am

KniazidisR
Member
Registered: 2019-03-03
Posts: 1259

Re: Adding an increasing number to a Numbers document

It’s better not to use AppleScript here. Better to do everything manually in the Numbers.app, using the concept of "formula":

1) set the value of the 1st cell of the column, for example = number 1
2) set in the 2nd cell of the column instead of the number the formula =A1+1 (Insert -> Formula -> Custom Formula ...)
3) copy the contents of the 2nd cell (right mouse click + Copy).
4) select all the other cells in the column and paste the copied one (right click + Paste).

Your automated Numbers's document is ready. Now, if you change the value of any cell, Numbers will automatically increase the values of all subsequent ones and very quickly. You can save this automated document and use forever.

No AppleScript is needed here. AppleScript makes sense when you need to automate something that is not yet automated...

Of course, you can automate 4 steps described above using AppleScript, but I think something you do only once, no need the automation.

Last edited by KniazidisR (2020-04-03 07:46:28 am)


Model: MacBook Pro
OS X: Catalina 10.15.4
Web Browser: Safari 13.1
Ram: 4 GB

Offline

 

#4 2020-04-03 08:07:21 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4546

Re: Adding an increasing number to a Numbers document

@KniazidisR
It's right if this task is the only one to achieve but my understanding, given the other question asked by the same OP, is that it's just one of a flow of tasks whose one of them is the creation of a new document from scratch exactly as I do quite every day.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) vendredi 3 avril 2020  16:07:16

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)