Making Thousands of Certificates, Invitations or Invoices From One Sheet

Certificates for 300 participants. Invitations for 150 guests. Monthly invoices for 80 customers. Payslips for every employee. They all share a pattern: the content is almost identical, and only the name, number, date and amount change. Yet in many offices, documents like these are still made one by one with copy and paste.

I once built a system that generated thousands of certificates and sent payment reminders and payslips from one sheet, which I wrote up in the certificate and payslip automation case study. In this article I sum up the concept and what you should know before you start.

The concept: one dataset, one template, many results

Every bulk document job works the same way:

1. 1Data in Google SheetsOne row per person
2. 2TemplateGoogle Slides or Docs with placeholders
3. 3Automatic fillEach row produces one document
4. 4Save and sendPDFs in Drive with tidy names, then email

Once the data sits cleanly in one sheet, the rest is just connecting the steps. The biggest problem is usually not the automation, but messy data.

If all you need is personalized email

For emails tailored to each person, you may not need to build anything. According to Gmail's help page on mail merge, Google Workspace accounts on eligible plans can send personalized emails using tags like @firstname, with data taken from spreadsheet columns. Each recipient gets their own copy, and replies come back as separate threads.

The limits: up to 1,500 recipients per day for mail merge, and these sends also count toward the account's daily sending limit, which is 2,000 for most work accounts.

If each person needs their own document

As soon as each person needs their own document, like a named certificate or an invoice with a unique number, you'll usually need Google Apps Script to fill the template and create the PDFs. A few things decide whether it works:

1. The execution time limit. According to the Apps Script quotas page, one execution is limited to 6 minutes. Thousands of documents won't finish in one run, so the job has to be split into batches. In the certificate system I built, the script processes 100 rows per batch and then schedules the next batch a minute later.
2. A status column on every row. Record which rows are done and which failed, so a rerun doesn't create duplicates.
3. Consistent file and folder names. For example per batch, then per person, so results are easy to find.
4. Email quotas. If documents are also emailed from Apps Script, there's a daily recipient limit.

Email sending limits to know

Daily email recipient limits(recipients)

• Apps Script, consumer Gmail account100
• Apps Script, Google Workspace account1,500
• Gmail mail merge1,500
• Daily sending limit for most work accounts2,000

In practice, if you need to reach thousands of people in a day, plan the sending in stages. A "send only the checked rows" option helps you manage the waves.

Check the data before you press the button

One typo in a name gets printed on a certificate and sent to that person. Before running a bulk job:

• Tidy up the format of names, dates and numbers.
• Remove duplicate rows.
• Test with two or three rows first, then check the results.
• Make sure personal data like salary amounts only reaches the right recipient.

It's the same principle I discussed in At What Point Should Humans Still Check AI and Automation Results: automation speeds up what's right, and it speeds up what's wrong too.

Ready-made tool or build your own

For simple needs, Gmail's built-in mail merge or a ready-made add-on is usually enough. Building your own makes more sense when:

• The volume is large and recurring.
• There are special rules, like certificate numbers in a particular format.
• The process connects to other flows, such as payment status or automatic follow-ups.

Closing

Bulk documents are one of the automations whose results you feel fastest. The keys are clean data, a clear template and a process that knows its limits.

If your team still makes documents one by one every month, tell me what you need.