Fill Forty Forms From One Spreadsheet

Issuing 1099s to forty contractors is not forty pieces of work. It is one piece of work repeated forty times, and the only reason it takes an afternoon is that most tools make you sit through each repetition.

The shape of the problem

You have a form and a list. Every row of the list needs its own copy of the form, filled with that row's values and named so you can tell it apart afterwards. Word's mail merge does this for letters. For PDFs with real form fields, the usual answer is a paid desktop suite or a script.

Why column matching is the interesting part

The obvious approach is to make the user rename their spreadsheet headers to match the form's field names exactly. That is technically simple and practically hostile: the form says RECIPIENT'S name, the spreadsheet says Recipient Name, and nobody should have to reconcile an apostrophe by hand.

Matching properly means ignoring case, punctuation, possessives and filler words, then scoring what remains by token overlap. "Recipient Name" and "RECIPIENT'S name" reduce to the same two words and match. "Invoice Total" and "Street address" share nothing and do not.

Every proposed pairing is shown before anything runs, with the uncertain ones flagged. A mapping you have not seen is a mail merge waiting to go wrong.

The failure worth designing against

The expensive mistake in any merge is a column landing in the wrong field. Every document wrong in the same way, discovered after they are sent. Two habits prevent it:

• Read the mapping, not the output. Four pairings are quicker to check than forty documents.
• Open the first and last file. If row one and row forty are right, the rows between almost certainly are too.

Naming matters more than you expect

Forty files called document.pdf is barely better than not having them. Naming from the data. 1099: Acme Ltd.pdf. Turns the output into something you can distribute without opening each one.

Where the ceiling is

Each row produces a complete PDF in your browser's memory. A hundred rows of a small form is comfortable; several hundred of a large one is not, and you will feel it. Split a big run into batches rather than hoping.