0

I am trying to use Applescript to help automate cleanup of a spreadysheet

I have a spreadsheet with all the data in a single column, basically a monetary amound and a date. I am trying to separate them out of the single column into columns A and B

Basically Every 3 rows is a dollar amount, and the following every three rows is date. The 3rd set is junk data that can be deleted with find and replace to "" (black) which I have working. The raw data looks like this.

$500.00 
January 1 2023
-JUNKdata-
$400.00 
January 2 2023
-JUNKdata-
$600.00 
January 3 2023
-JUNKdata-

I am trying to get it to translate to (with | being cell separator between A and B)

$500.00 | January 1 2023
$400.00 | January 2 2023
$600.00 | January 3 2023

and so on.

Is there an easy way to do this with Applescript? I was able to get find and replace working to get ride of the junk data that is always the same data but beyond that I'm a bit lost.

I am not a programmer so I am completely lost and trying to look at tutorials I can't seem to find commands I am looking for. I have tried to use select, copy, paste with ranges and manually typing out the rows such as A1, A4, A7) but I keep getting errors. I'm trying to be hacky with it but I can't even get that far.

Will Cloxton
  • 3
  • 3

1 Answers1

0

Assuming you have "Automatic" format for cells of columns "A" and "B", you can do something like this:

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
    
    set theData to value of cells of column "A" -- remeber the data as AppleScript list
    clear column "A" -- clear contents of column "A"
    -- set format of column "B" to text -- force text format for column "B" 
    
    set j to 0 -- set additional counter
    repeat with i from 1 to (count theData) - 2 by 3
        set j to j + 1
        set value of cell j of column "A" to item i of theData
        set value of cell j of column "B" to item (i + 1) of theData
    end repeat
    
end tell

NOTE: If you need "Text" format instead, then uncomment -- set format of column "B" to text code line.

If you have columns "A" and "B" initially in text format, then you can get a big gain in script speed from this. You can filter all empty (missing value) elements from theData at once, getting only text type elements of theData. Using ignoring application responses, you can also improve the speed several times.

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
    set theData to text of (get value of cells of column "A") -- remeber the data as AppleScript list
    
    ignoring application responses -- speed up
        clear column "A" -- clear contents of column "A"
        set j to 0 -- set additional counter
        repeat with i from 1 to (count theData) - 2 by 3
            set j to j + 1
            set value of cell j of column "A" to item i of theData
            set value of cell j of column "B" to item (i + 1) of theData
        end repeat
    end ignoring
    
end tell
Robert Kniazidis
  • 1,760
  • 1
  • 7
  • 8