英文:
Applescript for Apple Numbers, Move data around from a single column
问题
我明白你的需求，你已经成功使用 Applescript 的 find and replace 功能来删除无用数据。接下来，你可以使用以下脚本将数据从单列转移到两列（A 和 B）：
set rawData to {"$500.00", "January 1 2023", "-JUNKdata-", "$400.00", "January 2 2023", "-JUNKdata-", "$600.00", "January 3 2023", "-JUNKdata-"}
set cleanedData to {}
repeat with i from 1 to (count rawData) by 3
set amount to item i of rawData
set dateInfo to item (i + 1) of rawData
set formattedData to amount & " | " & dateInfo
set end of cleanedData to formattedData
end repeat
return cleanedData
这段脚本会将清理后的数据按照你想要的格式存储在 cleanedData 列表中。
英文:
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.
答案1
得分: 0
假设列“A”和“B”的单元格采用“Automatic”格式，可以像这样操作：
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
set theData to value of cells of column "A" -- 将数据保存为AppleScript列表
clear column "A" -- 清除列“A”的内容
-- set format of column "B" to text -- 强制将列“B”的格式设置为文本
set j to 0 -- 设置额外的计数器
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
注意：如果需要“Text”格式，然后取消注释 -- set format of column "B" to text 代码行。
如果列“A”和“B”最初是文本格式，那么可以通过以下方式大幅提高脚本速度。可以一次性过滤所有空（缺失值）元素，仅获取文本类型的theData元素。使用ignoring application responses，还可以多次提高速度。
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") -- 将数据保存为AppleScript列表
ignoring application responses -- 加速
clear column "A" -- 清除列“A”的内容
set j to 0 -- 设置额外的计数器
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论