Using Arrays to Simplify Script of Repetitive Tasks

Scripting Forums AppleScript | Mac OS X
#1

I’m looking to simplify this script to avoid having all of these repetitive lines, by being able to put all the column range and width values for the first set into a single-line array that gets called by a single set width of column line, and all the column range and alignment values into a single-line array that gets called by a single set alignment of column. Is that possible to do?

Thanks!

tell application "Numbers"
	tell table 1 of sheet 1 of document myDoc
		set width of column "A" to 90
		set width of column "B" to 90
		set width of column "C" to 80
		set width of column "D" to 60
		set width of column "E" to 60
		set width of column "F" to 80
		set width of column "G" to 50
		set width of column "H" to 50
		set width of column "I" to 90
		set width of column "J" to 60
		set width of column "K" to 60
		set width of column "L" to 110
		set width of column "M" to 110
		set width of column "N" to 110
		set width of column "O" to 60
		set width of column "P" to 60
		set width of column "Q" to 70
		set width of column "R" to 40
		set width of column "S" to 60
		set width of column "T" to 80
		set width of column "U" to 70
		set width of column "V" to 40
		set width of column "W" to 40
		set alignment of column "A" to right
		set alignment of column "B" to right
		set alignment of column "C" to right
		set alignment of column "D" to right
		set alignment of column "E" to right
		set alignment of column "F" to right
		set alignment of column "G" to right
		set alignment of column "H" to right
		set alignment of column "I" to right
		set alignment of column "J" to right
		set alignment of column "K" to right
		set alignment of column "L" to right
		set alignment of column "M" to right
		set alignment of column "N" to right
		set alignment of column "O" to right
		set alignment of column "P" to right
		set alignment of column "Q" to right
		set alignment of column "R" to right
		set alignment of column "S" to right
		set alignment of column "T" to right
		set alignment of column "U" to right
		set alignment of column "V" to right
		set alignment of column "W" to right
	end tell
end tell
#2

Try this

tell application "Numbers"
	set colList to {90, 90, 80, 60, 60, 80, 50, 80, 90, 60, 60, 110, 110, 110, 60, 60, 70, 40, 60, 80, 70, 40, 40}
	tell table 1 of sheet 1 of document 1
		repeat with i from 1 to (count colList)
			set myID to my character id (i + 64)
			set width of column myID to item i of colList
			set alignment of column myID to right
		end repeat
	end tell
end tell
1 Like
#3

Thanks for that and it works, but I don’t see how the actual column letters are being assigned if at all, and assume that it’s just going from one end of the sheet to the other (since that’s what my list items imply). But assuming the column choices are more random, can this array assign a letter to each value, and the same as well for the alignment, so that instances where the columns are not all together can be processed?

#4

Hi.

You don’t need to use the column letters as the columns are indexed from left to right anyway:

set columnWidths to {90, 90, 80, 60, 60, 80, 50, 50, 90, 60, 60, 110, 110, 110, 60, 60, 70, 40, 60, 80, 70, 40, 40}
tell application "Numbers"
	tell table 1 of sheet 1 of document myDoc
		repeat with i from 1 to (count columnWidths)
			set width of column i to item i of columnWidths
			set alignment of column i to right
		end repeat
	end tell
end tell

Another way to phase it would be the following, but the version above’s probably easier to edit if you want to add more properties:

set columnWidths to {90, 90, 80, 60, 60, 80, 50, 50, 90, 60, 60, 110, 110, 110, 60, 60, 70, 40, 60, 80, 70, 40, 40}
tell application "Numbers"
	tell table 1 of sheet 1 of document mydoc
		repeat with i from 1 to (count columnWidths)
			tell column i to set {width, alignment} to {item i of columnWidths, right}
		end repeat
	end tell
end tell
#5

Yes, but as just noted in my comment above, as soon as I need to do this on non-aligned columns, then it does not know which ones need changing. My script posted earlier was more of a sample of the repetitive nature of the issue, but in fact the columns are all over the place, and not necessarily does each column get both a width change and alignment change.

So I envisioned having each column letter and value paired up, if that’s possible.

#6 
tell application "Numbers"
	-- This list of records is set inside the Numbers tell statement to get the numbers terminolgy
	-- for the alignment values, but the records' property labels are chosen to AVOID being keywords.
	set columnDetails to {{letter:"A", wdth:90, alignmnt:right}, {letter:"B", wdth:90, alignmnt:right}, {letter:"C", wdth:80, alignmnt:right}} -- etc.
	tell table 1 of sheet 1 of document myDoc
		repeat with theseDetails in columnDetails
			tell column (theseDetails's letter)
				set its width to theseDetails's wdth
				set its alignment to theseDetails's alignmnt
			end tell
		end repeat
	end tell
end tell
#7

“character id 65” is the letter “A”.
So by incrementing the count from 65 to 87 gets us all the characters from A to W

#9

So when I modified the script to change the font, but only on the first column, it did that great but then had an error because it expects to find the same values for every column. Can that be fixed so that each column can have any number of property values, independent from the others?

tell application "Numbers"
	-- This list of records is set inside the Numbers tell statement to get the numbers terminolgy
	-- for the alignment values, but the records' property labels are chosen to AVOID being keywords.
	set columnDetails to {{letter:"A", wdth:90, alignmnt:right, fnt:"HelveticaNeue-Bold"}, {letter:"B", wdth:90, alignmnt:right}, {letter:"C", wdth:80, alignmnt:right}} -- etc.
	tell table 1 of sheet 1 of document 1
		repeat with theseDetails in columnDetails
			tell column (theseDetails's letter)
				set its width to theseDetails's wdth
				set its alignment to theseDetails's alignmnt
				set its font name to theseDetails's fnt
			end tell
		end repeat
	end tell
end tell
#10

you would need a “fnt” field in each record. In your example, only the first sub-record has a “fnt” field

#11

Just leave it blank then. Should’ve thought of that!

#12

That didn’t work. If I leave it blank it won’t compile, and if I assign it as “” the script errors out:

tell application "Numbers"
	-- This list of records is set inside the Numbers tell statement to get the numbers terminolgy
	-- for the alignment values, but the records' property labels are chosen to AVOID being keywords.
	set columnDetails to {{letter:"A", width:90, alignment:right, font:"HelveticaNeue-Bold"}, {letter:"B", width:90, alignment:right, font:}, {letter:"C", width:80, alignment:right, font:}} -- etc.
	tell table 1 of sheet 1 of document 1
		repeat with theseDetails in columnDetails
			tell column (theseDetails's letter)
				set its width to theseDetails's width
				set its alignment to theseDetails's alignment
				set its font name to theseDetails's font
			end tell
		end repeat
	end tell
end tell
#13 
tell application "Numbers"
	-- This list of records is set inside the Numbers tell statement to get the numbers terminolgy
	-- for the alignment values, but the records' property labels are chosen to AVOID being keywords.
	set columnDetails to {{letter:"A", width:90, alignment:right, font:"HelveticaNeue-Bold"}, {letter:"B", width:90, alignment:right, font:""}, {letter:"C", width:80, alignment:right, font:""}} -- etc.
	tell table 1 of sheet 1 of document 1
		repeat with theseDetails in columnDetails
			tell column (theseDetails's letter)
				set its width to theseDetails's width
				set its alignment to theseDetails's alignment
				set its font name to theseDetails's font
			end tell
		end repeat
	end tell
end tell
#14

Sure, just use if not missing value or something like that. But you’ll never get real flexibility here, because AppleScript can’t iterate over the fields in a record. So you have to know which fields can appear and handle them one by one. In another language, you’d do something like

const columnDetails = { "A" : {width: 90, alignment: "right"}, "B": {fontName: "Helvetica"}};
const app = Application("Numbers");
const table = app.documents[0].sheets[0].tables[0];
Object.keys(columnDetails).forEach( key => {
  // key is "A", "B" etc.
  const detail = columnDetails[k];
  const c = table.columns[k];
  Object.keys(detail).forEach{ attribute => {
    // attribute is "width", "alignment", "font" etc. 
    c[attribute] = detail[attribute];
  })
})

You’d simply add whatever you need in the columnDetails and let the code take care of the rest (data-driven programming). And there’d be no need to remove vowels from attribute names because no conflicts would arise.

In AppleScript, you’d have to code for each possible attribute separately, as shown by @Nigel_Garvey. And you’d have to check for each attribute if there’s a value defined or not.

#15

Kind of limited to Applescript at my level of experience :slight_smile: How would I incorporate the if not missing value in the existing scrip?

#16 
tell application "Numbers"
	-- This list of records is set inside the Numbers tell statement to get the numbers terminolgy
	-- for the alignment values, but the records' property labels are chosen to AVOID being keywords.
	set columnDetails to {{letter:"A", wdth:90, alignmnt:right, fnt:"HelveticaNeue-Bold"}, {letter:"B", wdth:90, alignmnt:right}, {letter:"C", wdth:80, alignmnt:right}} -- etc.
	tell table 1 of sheet 1 of document 1
		repeat with theseDetails in columnDetails
			-- Concatenate a record containing a 'missing value' for each of the possible properties
			-- to the record fetched in this repeat. The result will be a record containing all the
			-- properties, the values being from the fetched list where it has those properties
			-- and from the concatenated list otherwise.
			set {letter:letter, wdth:wdth, alignmnt:alignmnt, fnt:fnt} to ¬
				theseDetails & {wdth:missing value, alignmnt:missing value, fnt:missing value}
			tell column letter
				if (wdth is not missing value) then set its width to wdth
				if (alignmnt is not missing value) then set its alignment to alignmnt
				if (fnt is not missing value) then set its font name to fnt
			end tell
		end repeat
	end tell
end tell
1 Like
#17

The variable letter is not defined.

#18

And again something learned. Thanks for that!

#19

That’s not really difficult to fix it you look closely at the property names.

#20

Sorry. Typo. Now fixed.

#21

One typo more to fix