Mail merge

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.



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

Could you post image or list the columns that you have in your Numbers table so I have an idea of what you are working with?

I have been making enhancements to make the script more practical in the "real world" so it would help to know what columns you have.

(I suspect they are different from the [Name,Street1,Street2,City,Code] I have in my screenshot above. For example in the US there usually needs to be State).

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

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

VikingOSX wrote:

Those Avery sheets may require tweaks to the JXA code though.

Yes, one thing I haven't been able to figure out is the order of the text items in Pages (the boxes in the templates). I had thought Arrange > Send Back or Bring forward might change that but in my quick tests that didn't.

If you or anyone else knows the answer to that please do post.

SG

VikingOSX wrote:

At some point, I may adapt your code to see if I can fill out this form with mischief data from Numbers.

If you've got different data and and a different table setup then you may find this one easier to adapt:

const doc=Application('Pages').documents[0],
      tbl=Application("Numbers").documents[0].activeSheet.tables[0],
	  ltrs = tbl.columns.name(),          // column letters
	  vals = tbl.columns.cells.value();   // array of arrays

//place each column's values into an array named for that column letter	  
for (let i=0;i<ltrs.length;i++) {this[ltrs[i]] = vals[i]};


for (let i = tbl.headerRowCount(); i < A.length; i++) {

    //  below is template literal - don't delete those backticks!
	//  variables surrounded by {} are inserted into template
	//  can switch col ltrs, insert spaces, line returns, punctuation
	//  use ternary operator ? to handle null values

   
   outblock = ` 

${A[i]} ${B[i]} 
${C[i]} ${D[i]}${E[i]?' '+ E[i]:''}
${F[i]}${G[i]?' '+G[i]:''} ${H[i]} 


`   

  let lbltxt = doc.textItems[i-1].objectText;
  lbltxt.set(outblock.trim())
   
}

The "template literal" seems to make it a lot easier to move around and "map" to the Numbers table than the approach I tried in previous scripts. Just put each column letter (followed by [i] and surrounded by ${}) where that column's data should appear in the label.

The script automatically makes available new column letters as variables for use in the template if more columns are added to the data table.

After working with AppleScript I am impressed with how much can be done with such little code in JavaScript.

SG

Were you successful in downloading the reposted JXA and Python scripts?

VikingOSX wrote:

Were you successful in downloading the reposted JXA and Python scripts?

Yes, thanks. Studying them. Very useful. Parts are above my head.😆

SG

Here are the JXA Javascript scripts, the Numbers document with the sample mail merge data, and the Pages letter, labels, and envelopes templates to get you started (Dropbox download).

SG