Rotate cell contents in Numbers 3 and 4: Workaround

Hi Ian,

This is a great user tip!

It showed me how rotation works, and inspired me to cobble together the script below to automate at least some of the steps up to the point shown. (Background color can't be scripted, though.)

One selects the header cells (it expects all columns) and clicks the 'run' button in AppleScript Editor (after of course copying and pasting the script there).

Best regards,

SG

--beginning of script

--generates "vertical" or "slanted" text boxes from contents of header cells

--select header cells, click 'run' button

property textWidth : 325 --> "width" of text box (height when rotated)

property headerRotation : 90 --> 90 is vertical, 1-89 slanted

property paddingTop : 30 -- space above top of table

tell application "Numbers"

set s to front document's active sheet

set t to s's first table whose selection range's class is range

set headerFont to t's selection range's font name

set headerValues to t's selection range's cells's value

set {hOffset, vOffset} to t's position

set vOffset to vOffset - textWidth - paddingTop

tell s to repeat with i from 1 to count headerValues

set myTextItem to make new text item with properties ¬

{object text:headerValues's item i's contents, width:textWidth, rotation:headerRotation, position:{hOffset, vOffset}}

set myTextItem's object text's font to headerFont

set hOffset to hOffset + (t's column i's width)

end repeat

end tell

--end of script

Hi SG,

Neat!

Colour fill will be easy manually.

In the manual version, the table columns are very slightly wider than the text boxes. This means exact alignment in not necessary. I did try it with borders but alignment was too fiddly. Not necessary as the cell borders show.

Left alignment of text looks better.

I look forward to the next instalment. Scripted column width?

Regards,

Ian.

Hi Ian,

It's a good thing you think left (down after rotation) alignment looks better, because there seems to be no way to script it to be otherwise!

It's not hard to script the width of a table column. If that will help, I can demonstrate how to do that.

But it doesn't seem possible to script the "width" (actually the "height" property, as before the box is rotated) of a text box.

The width (height before rotation) doesn't seem to be so easy to change manually either, except by changing the value for 'Text inset'. Have you found another way?

SG

Hi SG,

Resizing a text box manually has no option to change the height. Height is set by the font size and number of lines of text (and text inset as you say).

Plan A.Text box with border to show its size. Text selected to show return characters. Text is 12 pt, the two returns are 8 pt

Could scripting add varied font-sized returns before and after item i's contents ?

Plan B. Come to think about it, aren't we doing this to make narrow columns (duh!). Maybe scripting to match column width to a bee's knees bigger than the default text box 'width'.

Regards,

Ian.

Hi Ian,

Could scripting add varied font-sized returns before and after item i's contents ?

Yes, I think it can. Below is a slightly simpler variation that adds a space at the end and varies the size of that to change a text box's "height".

I couldn't find the dimensions of a bee's knees in the AppleScript Dictionary so I had trouble scripting that.😀

But what we've discovered already about rotating text isn't bad!

SG

--varies a text box's "height"

property boxHeight : 20

tell application "Numbers"

set s to front document's active sheet

tell s's first text item

if its object text's last character is not space then ¬

set its object text to its object text & space

set its object text's last character's size to boxHeight

end tell

end tell

--end of script

Here's one that succeeds in varying the "padding" above and below contents. Maybe the smallest permissible value of 1 is the "bee's knees."

SG

--beginning of script

(*

Simulates the effect of variying a text box's "padding" above and below

by adding leading and trailing spaces (separated from the text

by returns) and resizing those spaces.

*)

property padAbove : 1

property padBelow : 5

tell application "Numbers"

set s to front document's active sheet

tell s's first text item

if its object text's first character is not space then ¬

set its object text to space & return & its object text

if its object text's last character is not space then ¬

set its object text to its object text & return & space

set its object text's first character's size to padAbove

set its object text's last character's size to padBelow

end tell

end tell

--end of script

Hi SG,

Perhaps we are looking at this from the wrong end. We could leave the text boxes at their default height (= width after rotating) and adjust the column width of the underlying table.

Text boxes have borders for illustration only.

Increase the row height of the Header row.

Set column widths of the table to a bee's knees wider than the text boxes.

The bee's knees gives some leeway for adjusting the positions of the text boxes (by manual or scripting method).

Without borders on the text boxes, it looks seamless.

I wonder what is the record for the most discussion on a User Tip?

Regards,

Ian.

Hi Ian,

Here is another stab at this. First manually adust the columns to the desired widths and set the header background color to Mercury or whatever. (I've left the background color at my default):

Then with the table still selected run the script below, giving this:

SG

--generates "vertical" or "slanted" text boxes from contents of header cells

--set columns to desired width, click a table cell, click 'run' button

property textRotation : 90 --> 90 is vertical, 1-89 slanted

property shiftRight : 2 --> moves a box right from table column's left edge

property lenFactor : 6 --> adjust as needed to fit font/size

property lenPadder : 10 --> adjust as needed to fit font/size/text length

tell application "Numbers"

--put focus on the selected table

try

set s to front document's active sheet

set t to s's first table whose selection range's class is range

on error

display alert "First click in a cell of the table." buttons "Ok"

return

end try

--get horizontal and vertical offsets (on sheet) for first text box

set {hOffset, vOffset} to t's position

set hOffset to hOffset + shiftRight

tell t's first row

--get font and hide the text

set headerFont to font name

set text color to background color

--collect content of header cells and place in AppleScript list

set headerValues to cells's value

--estimate length for longest text box:

set maxLen to 0

tell s to repeat with i from 1 to count headerValues

set thisLen to (headerValues's item i's character count) * lenFactor + lenPadder

if thisLen > maxLen then set maxLen to thisLen

end repeat

--set row height to accomodate longest text box:

set height to maxLen

end tell

--create text box for each item in the list headerValues:

tell s to repeat with i from 1 to count headerValues

set myTextItem to make new text item with properties ¬

{object text:headerValues's item i's contents, width:maxLen, rotation:textRotation, position:{hOffset, vOffset}}

set myTextItem's object text's font to headerFont

set hOffset to hOffset + (t's column i's width)

end repeat

end tell

--end of script

Hi SG,

It works. My very first use of the AppleScript editor!

This is perhaps a more typical table. Header Column needs to be wide, but the others hold narrow values.

Manually adjust column widths of the table.

Run the script

Delete the text box in A and change the font colour back to what it was

Thanks!

Regards,

Ian.

Hi SG,

From the Macquarie Dictionary (the dictionary that I recognise as the National Dictionary for Australia):

Co- a prefix signifying partnership, joint responsibility or ownership, as in co-producer, co-writer.

Therefore co-author means equal author. Please feel free to edit this User Tip.

Better, use these ideas to create your own User Tip, you guru!

Regards,

Ian.

Edit: Better, use these ideas to create your own User Tip, you guru! Then we can delete our confusing comments and make a clean User Tip. Ian.

Hi SG,

I have edited this User Tip to put your AppleScript (the easy way) first and the "lesson" on how it works second.

Please edit if necessary.

Regards,

Ian.

Method 1 (AppleScript) seems to be broken in Numbers version 4 on MacOS Sierra.

Hi Ian,

It seems to be working here. What part seems broken for you?

SG

Hi SG,

It is working. Maybe I tested it with a blank table 😊.

Ian.