Applescript in numbers to set cell format to numbers with one decimal.

Question

Applescript in numbers to set cell format to numbers with one decimal.

I have an OBDLink MX+ with generates CSV files.

I want to setup a script to automate the repetitive tasks associated with bringing the CSV file into numbers.

So far I have: (see script below)

I want to wrap all of the text in Row 2.

I also want to set most of the cell formats to numbers with one decimal place. There are a few variables that need three decimal places. The Grok 2 suggestions don't quite work

Current script

tell application "Numbers"
	activate
	try
		-- get template names
		set the templateNames to the name of every template
	end try
	
	tell table 1 of sheet 1 of front document
		set header row count to 2
		set header rows frozen to true
		set header column count to 1
		set header columns frozen to true
		set the width of every column to 50
		
	end tell
	
end tell

Grok2 mini suggestion

tell application "Numbers"
    activate
    tell document 1
        tell sheet 1
            tell table 1
                -- Select the range or cells you want to change. Here, it's set for the entire table
                -- If you want to change a specific range, replace 'every cell' with something like 'cell "A1:B10"'
                tell every cell
                    set the format of value format of it to number format with value type number format with decimals 1
                end tell
            end tell
        end tell
    end tell
end tell

iMac 27″, macOS 12.7

Posted on Nov 18, 2024 1:51 PM

Reply

Answer 1

SGIII

Level 8

36,128 points

Nov 18, 2024 6:59 PM in response to Nicholas James

I don't think AppleScript has that much granular control over format in Numbers.

You can set the cell format to number (or currency or data and time, or whatever) but you can't set the number of decimals.

With a lot of work you might be able to devise something using GUI-scripting.

Setting the number of decimals to whatever you want through the Numbers interface is, of course, quick and easy.

So you need to weigh the tradeoff between the time and effort of setting up automation against the time saved by the automation.

Unless you're importing csv's every minute or so, automation may not be worth the effort.

SG

Reply

Answer 2

Nov 18, 2024 9:08 PM in response to SGIII

Indeed?

Found the word wrap. So one more element down.

====-

tell application "Numbers"

activate

try

-- get template names

set the templateNames to the name of every template

end try

tell table 1 of sheet 1 of front document

set header row count to 2

set header rows frozen to true

set header column count to 1

set header columns frozen to true

set the width of every column to 50

set the font size of every cell to 12

set format of every cell of column 3 to number

set format of every cell of row 1 to text

set format of every cell of row 2 to text

set text wrap of column 2 to true

return text wrap of column 2

end tell

Reply

Answer 3

Nov 18, 2024 10:56 PM in response to SGIII

So this GUI script is working to set the decimal points.

tell application "System Events" to tell process "Numbers"
	set frontmost to true
	delay 1
	
	UI elements
	
	tell window 1
		UI elements
		
		tell splitter group 1
			UI elements
		end tell
		
		tell radio group 1
			UI elements
			if value of radio button "Cell" is 0 then
				click radio button "Cell"
				repeat until value of radio button "Cell" is 1
					delay 0.2
				end repeat
			end if
		end tell
		-- Try to find the correct text field for setting decimal places
		if exists text field 1 of scroll area 3 then
			tell text field 1 of scroll area 3
				set value of attribute "AXFocused" to true
				set value to "2" -- Setting 3 decimal places
				keystroke return
			end tell
		else if exists text field 1 of group 1 then
			-- This is another possible location for decimal place input
			tell text field 1 of group 1
				set value of attribute "AXFocused" to true
				set value to "2"
				keystroke return
			end tell
		else
			display dialog "Text field for setting decimal places not found."
		end if
		
	end tell
	
end tell

It is not cleaned up. Just got it to work. The issue with the original code was that it did not have the 'splitter group 1" in it.

Help was from here:

Used the 'Accessibility Inspector' that comes with X code to look at the Numbers setup.

All of the 'UI elements' commands from the help are still in place. That helped me work down the hierachry. But it really helped that n8henrie instructions specifically called out the missing "splitter group 1" element.

Reply