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.
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