I am using AppleScript to access cells in a Numbers spreadsheet. It all worked well until I added a category to the table. Now the script return incorrect values (offset from the target) or "missing value" errors. When I remove the category, it all works again. How can I compensate for the added column & rows produced by the category mechanism, or is Applescript's object classes not being kept up to date any more?
Mac mini (M4)
Yes, I get the same results. The cell addressing is disturbed when a Category is added.
If you frequently need to retrieve a value from a particular cell in a table then you might consider leaving that table "static" (i.e. no Categories) and extracting subtotals using a Pivot Table (which leaves the source data table unchanged).
Click in table, then in menu Organize > Create Pivot Table > On Current Sheet, and drag the fields down into the boxes below.
If you need a listing for a particular subtotal just option-click (right-click) the relevant cell in the Pivot Table and choose Create Table for Source Data:
Giving this:
Intro to pivot tables in Numbers on Mac – Apple Support
SG
Yes, I get the same results. The cell addressing is disturbed when a Category is added.
If you frequently need to retrieve a value from a particular cell in a table then you might consider leaving that table "static" (i.e. no Categories) and extracting subtotals using a Pivot Table (which leaves the source data table unchanged).
Click in table, then in menu Organize > Create Pivot Table > On Current Sheet, and drag the fields down into the boxes below.
If you need a listing for a particular subtotal just option-click (right-click) the relevant cell in the Pivot Table and choose Create Table for Source Data:
Giving this:
Intro to pivot tables in Numbers on Mac – Apple Support
SG
The devil is in the details. Could you post your script and a screenshot of the table with categories?
SG
Ok, here's a simplified version of what Im doing, which still shows the problem: This is the script:
tell application "Numbers"
tell document "Financial Report 2025.numbers"
tell sheet "Example"
tell table "Expenses"
set myCell to value of cell "D8"
end tell -- table
end tell -- sheet
end tell -- chosenDocumentFile
end tell -- application "Numbers"
This is the target table:
When the code is run, the result returned is "UnityWater" which, as you can see, is the contents of C7. If I remove the "Date" category & re-run the script, I get $117.49. If I reference cells further down the list, the problem gets worse, i.e. the vertical offset grows each time a category break is displayed. I hope that makes sense. Thanks for having a look at it.
Thanks very much for your detailed reply and work-around. I am using pivot tables for other results and can certainly build a summary table which can sit alongside the static table.
I guess I'll post a bug report about the issue.
Using AppleScript with Numbers spreadsheets
