Tuesday, June 2, 2020

#1 2020-03-23 11:36:18 am

IchBinGlitched
Member
Registered: 2007-06-07
Posts: 19

Revisiting an old Excel script for Numbers (fill value down).

Hello, again, everybody. 

With the move to Catalina killing off 32 bit apps I chose to walk away from some of the legacy software I'd been using (most notably Excel and the Adobe apps) and try out some of the alternatives that have sprung up. 

Numbers has been an interesting beast.  frustrating for some things, but sooooo much nicer for others. 

I've come across a new context for an old script of mine that the folks here were kind enough to help me with back in 2009. 

I have a Numbers document with about 48,000 rows where I need to start at the top and fill down values into empty cells and then recognize when the next cell is no longer empty and copy its contents and fill them down until the next non-empty cell... 

Example: 
B1 "August 12, 2019"
B2
B3 "August 13, 2019"
B4
B5
B6 "August 16, 2019"
B7
B8
B9

would become:
B1 "August 12, 2019"
B2 "August 12, 2019"
B3 "August 13, 2019"
B4 "August 13, 2019"
B5 "August 13, 2019"
B6 "August 16, 2019"
B7 "August 16, 2019"
B8 "August 16, 2019"
B9 "August 16, 2019"

I modified the Excel script and it compiles ok, but get an error "Numbers got an error: Can't get cell "B10" of table 1 of sheet 1 of document id "66C7C9FE-25F9-405D-A77B-5C59889EEC34":

Applescript:

tell application "Numbers"
   activate
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range)
       end tell
       tell selectedTable
           set TotalRows to row count
           set cell_No to TotalRows
           set cell_dest to "not empty"
           set cell_source to "not empty"
           repeat until cell_No is "1"
               set cell_source to value of cell ("A" & cell_No)
               set cell_dest to value of cell ("A" & cell_No + 1)
               if cell_dest is "" then
                   set value of cell ("A" & cell_No + 1) to cell_source
               end if
               set cell_No to cell_No + 1
           end repeat
       end tell
   end tell
end tell

when i try to streamline things and take it one at a time I get "Numbers got an error:  Can't get cell "B2":

Applescript:

tell application "Numbers"
   --    tell document 1
   --        tell active sheet
   --            set the selectedTable to (the first table whose class of selection range is range)
   --        end tell
   --        tell selectedTable
   set cell_No to 2
   set cell_contents to "not empty"
   --    repeat until cell_contents is ""
   set cell_contents to value of cell ("B" & cell_No)
   --        if cell_contents is not "" then
   --            set value of cell ("B" & cell_No + 1) to cell_contents
   --        end if
   --        set cell_No to cell_No + 1
   --    end repeat
end tell
--    end tell
--end tell

and if i uncomment the beginning i get "missing value"

Applescript:

tell application "Numbers"
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range)
       end tell
       tell selectedTable
           set cell_No to 2
           set cell_contents to "not empty"
           --    repeat until cell_contents is ""
           set cell_contents to value of cell ("B" & cell_No)
           --        if cell_contents is not "" then
           --            set value of cell ("B" & cell_No + 1) to cell_contents
           --        end if
           --        set cell_No to cell_No + 1
           --    end repeat
       end tell
   end tell
end tell

if feels like i'm missing something insanely obvious.  any insights?

Offline

 

#2 2020-03-23 12:25:49 pm

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

Re: Revisiting an old Excel script for Numbers (fill value down).

(1) your instruction :

Applescript:

repeat until cell_No is "1"

is equivalent to

Applescript:

repeat

because, as cell_No is a number the value "1" (a string) will never be reached.

(2) when I execute your script on a table with 20 rows, the log history
display:

Applescript:

tell application "Numbers"
   activate
   get table 1 of active sheet of document 1 whose class of selection range = range
       --> table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
   get row count of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> 20
   get value of cell "A20" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A21" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> error number -1728 from cell "A21" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
Résultat :
error "Erreur dans Numbers : Il est impossible d’obtenir cell \"A21\" of table 1 of sheet 1 of document id \"47821688-D7F9-4E04-BF6E-CF5BBDF61457\"." number -1728 from cell "A21" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"

It's perfectly normal because you are asking for the value of a cell which doesn't exist.
To get rid of that I added a try / on error / end try block:

Applescript:

tell application "Numbers"
   activate
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range)
       end tell
       tell selectedTable
           set TotalRows to row count
           set cell_No to TotalRows
           set cell_dest to "not empty"
           set cell_source to "not empty"
           repeat until cell_No is 1 --"1"
               set cell_source to value of cell ("A" & cell_No)
               try -- ADDED
                   set cell_dest to value of cell ("A" & cell_No + 1)
               on error -- ADDED
                   exit repeat -- exit the loop when we ask for a non-existing cell -- ADDED
               end try -- ADDED
               if cell_dest is "" then
                   set value of cell ("A" & cell_No + 1) to cell_source
               end if
               set cell_No to cell_No + 1
           end repeat
       end tell
   end tell
end tell

This time the log history become :

Applescript:

tell application "Numbers"
   activate
   get table 1 of active sheet of document 1 whose class of selection range = range
       --> table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
   get row count of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> 20
   get value of cell "A20" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A21" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> error number -1728 from cell "A21" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
end tell

We may look at what you are really doing:
You enter the loop with cell_No set to 20 ( the number of rows)
You set cell_source to the value of cell "A20" which is correct
then you try to set cell_dest to the value of cell "A21" which doesn't exist so an error is logically issued and  we exit the loop.

I edited the script to a more logical one:

Applescript:

tell application "Numbers"
   activate
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range)
       end tell
       tell selectedTable
           set TotalRows to row count
           set cell_No to 1 -- EDITED
           set cell_dest to "not empty"
           set cell_source to "not empty"
           repeat with cell_No from 1 to TotalRows - 1 -- EDITED
               set cell_source to value of cell ("A" & cell_No)
               set cell_dest to value of cell ("A" & cell_No + 1)
               if cell_dest is "" then
                   set value of cell ("A" & cell_No + 1) to cell_source
               end if
               set cell_No to cell_No + 1
           end repeat
       end tell
   end tell
end tell

This time it scan correctly the entire table with this log history:

Applescript:

tell application "Numbers"
   activate
   get table 1 of active sheet of document 1 whose class of selection range = range
       --> table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
   get row count of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> 20
   get value of cell "A1" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A2" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A2" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A3" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A3" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A4" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A4" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A5" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A5" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A6" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A6" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A7" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A7" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A8" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A8" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A9" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A9" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A10" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A10" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A11" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A11" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A12" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A12" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A13" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A13" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A14" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A14" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A15" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A15" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A16" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A16" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A17" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A17" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A18" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A18" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A19" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A19" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
   get value of cell "A20" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
       --> missing value
end tell
Résultat :
20

This make visible an other problem.
It appears that you assume that the value of an empty cell is the empty string "".
It's perhaps true for  Merdosoft Excel, but it's wrong for Numbers.
With this app, the value of an empty cell is : missing value.
I am speaking of a really empty cell.
If the cells are filled by formulas, it's the formula which defines what it will store in a cell containing nothing. I saw formulas setting this value to "", others setting it to " ".
Assuming that your formula uses "", the test must be edited as :

Applescript:

tell application "Numbers"
   activate
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range)
       end tell
       tell selectedTable
           set TotalRows to row count
           set cell_No to 1 -- EDITED
           set cell_dest to "not empty"
           set cell_source to "not empty"
           repeat with cell_No from 1 to TotalRows - 1 -- EDITED
               set cell_source to value of cell ("A" & cell_No)
               set cell_dest to value of cell ("A" & cell_No + 1)
               if cell_dest is in {"", missing value} then -- EDITED
                   set value of cell ("A" & cell_No + 1) to cell_source
               end if
               set cell_No to cell_No + 1
           end repeat
       end tell
   end tell
end tell

Once again the code scan the entire table.
It may be time to re-read your message. You wrote about cells of column "B" but you scan cells of column "A".
This pushed me to edit again as:

Applescript:

tell application "Numbers"
   activate
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range)
       end tell
       tell selectedTable
           set TotalRows to row count
           set cell_No to 1 -- EDITED
           set cell_dest to "not empty"
           set cell_source to "not empty"
           repeat with cell_No from 1 to TotalRows - 1 -- EDITED
               set cell_source to value of cell ("B" & cell_No) -- EDITED
               set cell_dest to value of cell ("B" & cell_No + 1) -- EDITED
               if cell_dest is in {"", missing value} then -- EDITED
                   set value of cell ("B" & cell_No + 1) to cell_source -- EDITED
               end if
               set cell_No to cell_No + 1
           end repeat
       end tell
   end tell
end tell

Now, what a surprise, the script does what it was supposed to do.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) lundi 23 mars 2020  19:25:42

Last edited by Yvan Koenig (2020-03-23 12:29:43 pm)

Offline

 

#3 2020-03-23 01:03:34 pm

IchBinGlitched
Member
Registered: 2007-06-07
Posts: 19

Re: Revisiting an old Excel script for Numbers (fill value down).

Yvan Koenig wrote:

It may be time to re-read your message. You wrote about cells of column "B" but you scan cells of column "A".



D'oh!  in the sample i had pasted i had been trying a couple things and didn't realize i hadn't changed the A back to B.   Sorry if that was confusing.  The same is true of the "repeat until cell_No is '1'" problem.  i had tried working backwards to see if that would make more sense and i forgot to change that one back. Oh.  i also just realized that you meant the quotes around the 1 were a problem.

The insight into numbers and excel handling blanks cells differently was very valuable, thank you.

I'm out of the house right now, but I'll respond more later to see if I have properly learned the lessons you taught.  the script you made definitely works though and for that I am very grateful.

I appreciated the "merdosoft excel".  I don't speak french, but that's a word that I recognize. hahahah. 


-david

Offline

 

#4 2020-03-23 02:53:53 pm

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

Re: Revisiting an old Excel script for Numbers (fill value down).

Here is a cleaned version :

Applescript:

tell application "Numbers"
   activate
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range)
       end tell
       tell selectedTable
           set TotalRows to row count
           tell column 2
               repeat with cell_No from 1 to TotalRows - 1
                   set cell_source to value of cell cell_No
                   tell cell (cell_No + 1)
                       set cell_dest to its value
                       if cell_dest is in {"", missing value} then
                           set its value to cell_source
                       end if
                   end tell
               end repeat
           end tell
       end tell
   end tell
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) lundi 23 mars 2020  21:53:47

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)