Mail merge with Pages and Numbers | JavaScript for Automation (JXA)

Mail merge, i.e. creating a series of letters with different recipients and possibly personalized messages is a natural application of a word processor. However, Apple removed this function from its Pages after iWork 09. You can, however, rebuild a mail merge (and adjust it to your needs) using Pages, Numbers and a JXA script. The German Mac&i magazine published a more detailed version of this text in its January 2022 issue.

Define a Pages document with placeholders#

First, you need a Pages document that will work as the blueprint for your serial letters. You can use either a template or a normal Pages document. In any case, you have to define the placeholders for name, address and other data that change for every letter. These placeholders are defined with “Format/Advanced/Define as Placeholder Text” from Pages’ menu: Select some text in your document (for example the address), then click on this menu item or press ⌃⌥⌘T (Ctrl-Option-Command T) to define it as a placeholder. The text will be highlighted by a orangey-red bar and be accessible as this placeholder’s tag in a script. Therefore, you should choose self-explaining and unique placeholders.

Define a Numbers document with replacement text#

In the second step, you define a table in a Numbers document that provides the data for your documents. The first row must contain the column headings. You can use whatever label you want here, but it is probably easiest to use the same headings as the placeholder texts in your Pages document. If your Number document contains several sheets or tables, you should give them names that permit you to easily address them later.

The sample script uses hard-coded names for the Pages and Numbers documents and assumes that the column headings match the placeholder tags. You can of course amend the script to ask the user for the documents and to choose the table to use by providing appropriate dialogs.

Offering more flexibility for the relationship between placeholders tags and column titles requires a lot more work for the user interface than is possible with the simple JXA dialogs. So we’ll not go there: placeholder tags and column titles simply have to be identical.

The script itself consists mostly of small functions that handle small tasks. First you’ll see how to open the Pages document

/* Open a Pages document */
function openPagesDoc(filename) {
  const pagesApp = Application('Pages');
  return doc = pagesApp.open(Path(filename));
}

Note that the file name passed in as a string parameter can’t be used directly in the open method: you have to convert it to a Path object by passing it to a method of the same name. The same happens in the function to read the numbers table:

// Access a Number's table
function readNumbersTable(filename) {
  const numbersApp = Application('Numbers');
  return numbersApp.open(Path(filename)).sheets[0].tables[0];
}

It opens the Numbers document and returns the first table of the first sheet. There’s no point in returning the Numbers document itself, because that’s not needed in the future. You could modify this function to accept not only the file name but also the name of the sheet or table (or their indices).

Use copies of the Pages document#

createNewLetter expects the existing Pages document and the name of a new file as input to which the document is copied. The subsequent functions will work only on this copy. This step is necessary because the placeholder tags will be replaced with the actual values from the Numbers table. Working with the original would remove the placeholders so that the document could only be used once.

// Create a new Pages document
function createNewLetter(pagesDoc, newDoc) {
  shellCommand(`touch "${newDoc}" && chmod 600 "${newDoc}"`);
  const pagesApp = Application('Pages');
  pagesApp.save(pagesDoc, {in: Path(newDoc)});
  return pagesApp.open(Path(newDoc));
}

The function uses the shell command touch to create the new file. That is simpler then working with the Pages’ JXA methods. At the end, createNewLetter returns the Object Specifier of the newly created Pages document. The function shellCommand is a trivial wrapper around doShellScript from the „StandardAdditions“ framework.

After the Pages document has been duplicated and the Numbers table is chosen, the script can start to merge the data from the table into the document. That’s what the function mergeData does. It expects a Pages document (i.e. the copy of the template returned by createNewLetter) and one row of the Numbers table as inputs.

Replace the placeholders#

Firstly, it gets all the placeholder texts from the Pages document and saves their count in a variable. The latter step saves some time in the following loop and increases readability, it is not strictly necessary. Then the function loops over all the placeholder texts and replaces the first of them in every step with the corresponding entry in the table row.

Only changing the first entry of the placeholder list in every loop iteration looks a bit weird. But it should become obvious when considering what happens when the placeholder is replaced: It’s gone. In fact, the whole list is modified by that. Imagine that the list had two elements and you replaced the first one in the first run through the loop. After that, the list has only one element left. Consequently, always replacing the first entry in the list of placeholder is the only way to iterate over them. Note the usage of the set method here to change the placeholder: it is the only way to achieve what we want, assignment to the placeholder does not work.

// Replace placeholders in Pages document
function mergeData(pagesDoc, tableRow) {
  const placeholderList = pagesDokument.placeholderTexts;
  const placeholderCount = placeholderList.length;
  for (let i = 0; i < placeholderCount; i++) {
    const placeholderName = placeholderList[0].tag();
    const replacementText = tableRow[placeholderName];
    placeholderList[0].set(replacementText);
  }
}

The tableRow passed into mergeData is a JavaScript object that might look like this

{
  firstname: "Jon", 
  lastname: "Doe",
  zip: "12345"
…}

The attributes firstname etc are used to match the placeholders’ name, so these two have to be identical. buildTableRow constructs the object like so:

function buildTableRow(columnTitles, row){
  const tableRow = {};
  columnTitles.forEach((title, index) => {
    tableRow[title] = row.cells[index].value();
  });
  return tableRow;
}

The columnTitles is an array consisting of the first row’s content, row is the current table row.

Putting everything together#

Putting together the parts shown, the main part of the script looks like this:

// Complete mail merge script for Pages and Numbers
(() => {
  const sourceFolder = getSourceFolder();
  const targetFolder = `${sourceFolder}/Letters`;
  createFolder(targetFolder);

  const pagesName = 'MergeLetter.pages';
  const numbersName = 'Addresses.numbers'
  
  const pagesDoc = openPagesDoc(`${sourceFolder}/${pagesName}`);
  const numbersTable = numbersTabelleLesen(`${sourceFolder}/${numbersName}`);
  const rowCount = numbersTable.rows.length;
  const columnTitles = numbersTable.rows[0].cells.value();
  for (let i = 1; i < rowCount; i++) {
    const row = numbersTabelle.rows[i];
    const dataObject = buildTableRow(columnTitle, column);
    const mergeName = `${sourceFolder}/Merged-${i}.pages`;
    const mergeDoc = createNewLetter(pagesDoc, mergeName);
    mergeData(mergeDoc, dataObject);
    saveLetter(mergeDoc, targetFolder);
    cleanup(mergeDoc);
  }
})()

For simplicity, the script uses fixed names for the Pages and Numbers documents as well as for the target folder to save the merged documents to. You can of course use user interface methods to ask the user for those. The functions saveLetter, getSourceFolder, createFolder and cleanup are not detailed here because they’re fairly simple. You’ll see them in the (complete listing of the script)[Merge%20letter.js]. saveLetter creates a PDF file for each merged document.