Mail merge

southcoast-dweller wrote:

I'm using a template included in Pages (Avery C32011). Each label on this template consisted of a Name/Job title box and an Address box. I have removed the Name/Job title boxes and saved the remaining label with the address boxes as a new template.

My Pages doesn't include an Avery C32011 template. Also it seems that you have altered that template in some way.

Have you tried using the template included in my sample download?

With that template I ran the script using your 14 rows of data with results as expected.

So your results seem to have something to do with the template you have used. Perhaps the script is looking for textboxes that you have removed.

SG

That is some slick code. However, the first envelope text box (on this example) is the return address, not the addressee text box as you have indicated, and using your code, it does fill (and truncate) the return address box with Pages v10.1 on Mojave.

var totxtr = doc.textItems[0].objectText;  // Urna Semper return address text box
var totxta = doc.textItems[1].objectText;  // Addressee text box
...
totxta.set(rowvals);  // populate the addressee text box

And the Contacts application can import rows of a spreadsheet CSV as individual Contact entries in a custom Contact Group. Then, one can print the individual information on the selected output form factor without using a line of code.



Pages doesn't have built-in Mail merge but it's quite easy to mail merge to envelopes from Numbers with a short script:

In Pages, File > New and pick the envelope template you want. Delete the "to" placeholder text and replace it with a new Text Box (Insert > Text Box) and style it the way you want, with font size, etc. Make sure it is tall enough to hold the address with the most number of lines. An easy way to do that is simply enter the "tallest" address. It will be replaced with others as the script runs.

In Numbers arrange the data similar to the screenshot, with one Header Row. Each row cell will print on a separate line so have first name and last name combined in one cell, not in separate cells. You can add/insert columns as needed and some cells in the middle (for example the second line of an address) can be blank but make sure there are no blank rows at the bottom. As written the script assumes the data will be in the first table on the first sheet of the frontmost document.

Open Script Editor (in Applications > Utilities), choose Javascript in the dropdown upper left (i.e. change it from the default AppleScript), and copy paste the following script:

var table = Application("Numbers").documents[0].activeSheet.tables[0];
var doc = Application("Pages").documents[0];
var totxt = doc.textItems[0].objectText;

for(var i=1;i<table.rowCount();i++) {
	var rowvals = table.rows[i].cells.whose({_not:[{value:null}]}).value().join("\n");
	totxt.set(rowvals);
	doc.print();
}

With the Pages and Numbers documents open, click the Run button in Script Editor. If "nothing happens" make sure Script Editor is listed and checked at System Preferences > Security & Privacy > Privacy Accessibility.

Test on a short table first to make sure it is doing what you want. Setup should take only a few minutes.

SG

Here's a revised mailing label script. It's longer but just as easy to use as before, with more flexibility. Aside from "mapping" the columns in your table in the first line generally you shouldn't have to worry about the rest.

Working from this Excel table:

I used this script:

const cols = {fname:"B", lname:"A", mname:"C", street1:"D", street2:"E", 
              city:"F", state: "G", code:"H", salut:"I", usr1:"J", usr2:"K"};

             // change column letters between " " above to match Numbers table

Object.keys(cols).forEach(key => cols[key]="ABCDEFGHIJK".indexOf(cols[key]));
			 			 
const table = Application("Numbers").documents[0].activeSheet.tables[0],
      doc = Application("Pages").documents[0],
	  notNull = value => value !== null; //function for filter()

for(var i=1;i<table.rowCount();i++) {

    let rv = table.rows[i].cells.formattedValue(); // values on a row

	let name = [rv[cols.fname],rv[cols.mname],rv[cols.lname]].filter(notNull).join(" ");
	let street = [rv[cols.street1],rv[cols.street2]].filter(notNull).join("\n");
    let ctystcode = [[rv[cols.city], rv[cols.state]].join(", "),rv[cols.code]].join(" ");
	let nameaddr = [name,street,ctystcode].join("\n");
    
	let lbltxt = doc.textItems[i-1].objectText;
    lbltxt.set(nameaddr);
}

Giving me something like this:

The sheet will fill up in no particular order as more rows are added to the table.

If there are more rows in the table than boxes for labels on the template you are using then you need to add pages to the template.

Let me know if you have different columns in your table or you don't use the US address conventions. The script is easily adapted.

SG

And here's a script that inserts personalized information value into a letter (something that can't be done via the Contacts app).

Working from this Numbers table:

And a saved template under My Templates in the Template Chooser at File > New that looks like this:

(The grey portions show where I selected text and chose Format > Advanced > Define as Placeholder Text. Normally they wouldn't be gray. The black parts are where text has been typed in, replacing any placeholders that were originally there. )

"Out of the box" (not much tinkering needed below the first line except to uncomment the print line when ready) this script will produce letters that look like this with a click:

const mytemplate = "My Pages Template"  // change to your template name

const cols = {fname:"B", lname:"A", mname:"C", street1:"D", street2:"E", 
              city:"F", state: "G", code:"H", salut:"I", usr1:"J", usr2:"K"};

             // change column letters between " " above to match Numbers table

Object.keys(cols).forEach(key => cols[key]="ABCDEFGHIJK".indexOf(cols[key]));
             // "A" offset 0, "B" offset 2, "C" offset 3, etc.

const pages = Application("Pages"), 
      t = pages.templates[mytemplate],
      table = Application("Numbers").documents[0].activeSheet.tables[0],
	  notNull = value => value !== null; //function used in filter()

for(let i=1;i<table.rowCount();i++) {

    let rv = table.rows[i].cells.formattedValue(); // get formatted values for row
	
	// name address block
	let name = [rv[cols.fname],rv[cols.mname],rv[cols.lname]].filter(notNull).join(" ");
	let street = [rv[cols.street1],rv[cols.street2]].filter(notNull).join("\n");
    let ctystcode = [[rv[cols.city], rv[cols.state]].join(", "),rv[cols.code]].join(" ");
	let nameaddr = [name,street,ctystcode].join("\n");

	// if remove these, also remove set() below, and placeholder(s) in Pages template
	let salutation = rv[cols.salut];
	let amount = rv[cols.usr1];
	let date = rv[cols.usr2];
	
	// make new doc from template and fill in placeholders
	let doc = pages.Document({documentTemplate:t}).make();
	doc.placeholderTexts[0].set(nameaddr); 
	doc.placeholderTexts[0].set(salutation); // 0 as now 1st placeholder
	doc.placeholderTexts[0].set(amount); // ditto
	doc.placeholderTexts[0].set(date); // ditto
	//doc.print();  // when ready to print, uncomment (remove first set of //)
	
	//pages.activate(); // uncomment if want Pages to pop up in front
    //doc.delete(); // uncomment to delete each doc after print
	//doc.close();  // uncomment for prompt to save each document during printing
} 

SG

You're getting an error because you're using the first script that was targeted at a much simpler data table. Here I see you have three street columns and the first two of these presumably go together on the same line. Also you don't have a separate column for a middle name.

So a JXA script that works for your table would be like this:

const cols = {fname:"B", lname:"A", street1:"C", street2:"D", street3: "E", 
              city:"F", state: "G", code:"H", salut:"I", usr1:"J", usr2:"K"};

             // change column letters between " " above to match Numbers table
			 // have Pages labels template open and frontmost before running script
			 // number of boxes in template must equal or exceed table rows
			 // compile again (hit the hammer) before rerunning

Object.keys(cols).forEach(key => cols[key]="ABCDEFGHIJK".indexOf(cols[key]));
			 			 
const table = Application("Numbers").documents[0].activeSheet.tables[0],
      doc = Application("Pages").documents[0],
	  notNull = value => value !== null; //function for filter()

for(var i=1;i<table.rowCount();i++) {

    let rv = table.rows[i].cells.formattedValue(); // values in this row

	let name = [rv[cols.fname],rv[cols.mname],rv[cols.lname]].filter(notNull).join(" ");
	let street = [rv[cols.street1] +" "+rv[cols.street2],rv[cols.street3]].filter(notNull).join("\n");
    let ctystcode = [[rv[cols.city], rv[cols.state]].join(", "),rv[cols.code]].join(" ");
	let nameaddr = [name,street,ctystcode].join("\n");
    
	let lbltxt = doc.textItems[i-1].objectText;
    lbltxt.set(nameaddr);
}

The results here (after adjusting font size on labels template to fit) look like this:

Is this close to what you want?

SG

In order to download a blank Pages Address label template (e.g 5160) from Avery, one must have an established account there beforehand. The Avery 5160 blank Pages template uses an unordered array of shapes, each with redundant object text. One has control over the color, size, and font face for each shape's object text. One could perform a loop from shape 1 to the count of shapes, and randomly enter address information until done. I have colored where shape 2 is located.

When one uses the following AppleScript, it builds a table of shapes in numeric order, but those shapes are not in visual order as you can see above.

use scripting additions

tell application "Pages"
	tell front document
		set tbl to shapes of it
	end tell
end tell



I got my Avery template for testing here. Many others available in Pages format. No account needed.

This is script I used for testing the order in which the labels print.

const app=Application("Pages"),
      doc = app.documents[0],
	  ti = doc.textItems;
	  
for (i in ti) {ti[i].objectText = i}

It results in something like this:

The properties of a text box look something like this:

When I cut and paste back some boxes, the order changes. But I can't figure out the pattern.

In theory one could reorder by x and y position in some automated way. But that would be work!

SG

Yes, thanks for pointing that out. The order of the text boxes apparently can vary. Not sure why.

On my setup it's definitely textItems[0].objectText. If turns out to be the wrong box, then just change to textItems[1].objectText.

Thanks for pointing out that print feature in Contacts. Select a group, File > Print > Show Details and pick Envelope, Mailing Labels or other.

Understand some on Big Sur are having trouble dragging from Contacts to Numbers. Not sure how well the other direction is working.

In any case, the simple script definitely works for me in the here and now. No fussing with csv export/import. One click.😀

SG

JXA. Great when it works, and good to see you using it here.

I wrote a JXA script that prompts for an image file (including Camera RAW), and then dumps out the EXIF, etc. information. Worked on Mojave, and returned nothing on Catalina. Bump. The same Cocoa code in Python and Swift works just fine on both platforms.

Yes, JXA is great when you can get it to work. Documentation is spotty, and there are way too few examples in the wild to study. (I tend to learn by example.)

Did you happen to post your (now non-working) script somewhere in the discussions here or somewhere else on line?

SG