Client Statements From a Spreadsheet: One PDF per Client

Your log is rows. A statement is a group.

The data is in exactly the right shape, which is why this feels like it should be easy. A transactions log has one row per event, and the columns are almost always the same four. Here are four rows out of the 340:

In that excerpt Mrs. Delgado owns two of her eleven rows. Her statement is those rows, a heading with her name on it, and a total at the bottom. That is the whole operation: filter to one value of the client column, then sum what survives.

Do it once and it is a pivot table. Do it twenty-four times, on the first working day of every month, and re-derive the same grouping by hand each time, and it is the thing you are searching for a way out of. Notice which half is the work. Nobody is stuck on the subtotal. They are stuck on the twenty-four rounds of copy, paste, rename, attach.

The pivot table, and then the copying

This is what almost everyone does, and for a short client list it is the correct answer. It needs no add-on and no account anywhere.

1. Select the transactions range and choose Insert > Pivot table in Google Sheets, or Insert > PivotTable in Excel.
2. In the pivot editor, put Client in Rows and Date and Description under it, so each client's transactions stay listed rather than collapsing into one line.
3. Put Amount in Values, summarized by SUM. You now have every client's lines and every client's subtotal on one sheet.
4. Add Client to Filters and set it to a single name.
5. Copy the visible block.
6. Paste it into your statement template in Docs or Word, under that client's address, and fix whatever the paste did to the column widths.
7. Export the file as PDF and rename it after the client.
8. Attach it to an email, write the covering note, send.
9. Change the pivot filter to the next client and go back to step 5.

Steps 1 through 4 you build once and reuse forever. Steps 5 through 8 are the ones that multiply, and they do not get meaningfully faster with practice, because most of the time is spent waiting on menus and typing a name into a filename box. Twenty-four clients is twenty-four rounds of it.

Where it breaks:

• The pivot survives a new client appearing. The stack of documents does not: nobody tells you that Whitlock started in August except the pivot itself, and only if you read the filter list carefully before you start.
• Formatting is re-done on every paste. A pivot block dropped into Docs arrives as a table with the wrong widths and no currency alignment.
• You send on the 3rd, and on the 14th a row dated the 12th turns up. Every statement that has gone out is now short a line, and you have no list of who to resend to.
• There is no record of who you sent what. The Sent folder is the record, and it is a bad one when somebody says in October that August never arrived.

Use this route when you have six clients, or when this is the second month you have ever done it and you do not yet know whether the format is right.

One tab per client, built on FILTER

There is a trick that skips the pivot. Give each client their own tab, type that client's name into B1 of their tab, and let a formula pull their rows in. The log stays where it is, on a tab called Transactions. In Google Sheets:

=FILTER(Transactions!A2:D, Transactions!B2:B = $B$1)

That lists exactly their transactions and re-lists them the moment the log changes. Note that B1 has to be on the statement tab: over on Transactions, B1 is the Client header. The total below it is the same idea:

=SUM(FILTER(Transactions!D2:D, Transactions!B2:B = $B$1))

Excel will not take those. The open-ended A2:D form is Google's own range notation (opens in a new tab), where a range ending in a bare column letter means every row from there down. Excel has no equivalent, so bound it instead:

=FILTER(Transactions!A2:D1000, Transactions!B2:B1000=$B$1, "No transactions")

Better, turn the log into a table (Insert > Table, named tblTx here) so it grows on its own and you never pick a bound:

=FILTER(tblTx, tblTx[Client]=$B$1, "No transactions")

The total has an easier answer in Excel, and SUMIF has shipped for far longer than FILTER has:

=SUMIF(tblTx[Client], $B$1, tblTx[Amount])

Format the tab once to look like a statement, then duplicate it per client and change B1. Export each tab from Sheets with File > Download > PDF and Current sheet selected. The Google Sheets PDF guide covers the export settings that decide whether the result is readable, and the same page has the /export?format=pdf URL if you would rather not walk the menu twenty-four times.

Where it breaks: the tab list is a copy of your client list, maintained by hand, and it goes stale the first month somebody new shows up or somebody leaves. Everything else here is fine, which is what makes it tempting to keep past the point where it is a good idea. One version caveat: FILTER is a dynamic-array function, so an older Excel will not have it. Microsoft's page for FILTER (opens in a new tab) lists Excel 2021, Excel 2024 and Microsoft 365, along with the Mac and mobile builds. The SUMIF above works regardless of any of that. In Google Sheets the question never comes up.

Mail merge add-ons: one document per row

This is the trap, and it costs people an evening before they see it.

The obvious move is a merge add-on. Autocrat, Form Publisher, and the rest read a Google Sheet, swap <<tags>> in a Docs template for cell values, and write out PDFs. Point one at your transactions log and you get exactly what it promises: one document per row. Mrs. Delgado's eleven transactions become eleven statements, each with one line on it. For a forty-transaction month across twenty clients, that is forty PDFs and forty emails, none of which is a statement.

The tools are not broken. Conventional row-based merges map one row to one document, and every other job people bring them, a certificate or an offer letter, really is shaped that way. Statements are the case where the assumption fails.

So before you buy any merge tool, ask whether several rows can share one output document. Fills-a-template is table stakes. Some tools do it. DocsAutomator sells the capability as Line Items, which it describes as lists of things where the quantity is not known in advance; you put a marker at the start of a table row and it repeats that row down the matching records, mapped from a linked Google Sheet. Autocrat writes one file per row, and neither its Marketplace listing nor its own documentation mentions grouping or line items anywhere. If a feature list never says "line items" or "repeating rows", assume one file per row. I'd test it on three clients before committing the month to it.

The same question applies to a merge tool you are already paying for. Check its docs for "line items" before you conclude it cannot do this. And if the answer comes back one-file-per-row, the grouped route further down this page is the shape you were reaching for.

An Apps Script that does the grouping

If the log lives in Google Sheets and you are willing to own about sixty lines of code, this is a real answer and it is free. The script reads the tab, buckets the rows by client, and writes one Doc per bucket with a table of that client's lines.

// Transactions tab columns, in this order: Date | Client | Description | Amount.
// Format Date as a date and Amount as a number. toAmount below rescues a
// currency-formatted string like "$42.00"; a date typed as text is on you.
// The Docs template holds {{client}} and {{total}} placeholders. This is the naive
// version: it has no memory, so a second run produces a second set of documents.
function toAmount(value) {
  if (typeof value === "number") return value;
  const digits = String(value).replace(/[^0-9.-]/g, "");
  return digits ? Number(digits) : 0;
}

function buildStatements() {
  const TEMPLATE_ID = "YOUR_DOC_TEMPLATE_ID";
  const FOLDER_ID = "YOUR_FOLDER_ID";
  const TAB = "Transactions";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(TAB);
  if (!sheet) throw new Error("No tab named " + TAB);

  const byClient = {};
  sheet.getDataRange().getValues().slice(1).forEach(function (row) {
    const client = String(row[1]).trim();
    if (!client) return;
    if (!byClient[client]) byClient[client] = [];
    byClient[client].push(row);
  });

  const folder = DriveApp.getFolderById(FOLDER_ID);
  const tz = ss.getSpreadsheetTimeZone();

  Object.keys(byClient).forEach(function (client) {
    const lines = byClient[client];
    let total = 0;
    const table = [["Date", "Description", "Amount"]];
    lines.forEach(function (row) {
      const when = row[0] instanceof Date ? row[0] : new Date(row[0]);
      const amount = toAmount(row[3]);
      total += amount;
      table.push([
        isNaN(when.getTime()) ? String(row[0]) : Utilities.formatDate(when, tz, "yyyy-MM-dd"),
        String(row[2]),
        amount.toFixed(2)
      ]);
    });

    const copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy(client + " statement", folder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();
    body.replaceText("{{client}}", client);
    body.replaceText("{{total}}", total.toFixed(2));
    body.appendTable(table);
    doc.saveAndClose();

    const pdf = DriveApp.getFileById(doc.getId()).getAs("application/pdf");
    folder.createFile(pdf.setName(client + " statement.pdf"));
  });
}

Paste it into Extensions > Apps Script, set the two ids, run it once, and approve the authorization prompt. One Doc and one PDF per client land in the folder.

Two things about that code before you trust it with a real month. appendTable puts the table at the end of the body, so if your template has a signature block or a payment footer, the transactions land underneath it. Moving the table where you want it means finding the placeholder paragraph and inserting at its index instead, which is another ten lines. And the script has no memory at all: run it twice on the 1st and every client gets two statements, because Drive is happy to hold two files with the same name.

Where it breaks:

• Six minutes is the ceiling on any one execution, and all your triggered runs draw down a shared daily budget: 90 minutes on a consumer account, 6 hours on Workspace (Google's quota table (opens in a new tab)). Every client on the list costs a file copy plus a PDF export, with a Docs session in between, so what you can finish inside six minutes falls as the list grows. I have not benchmarked where it gives out, and neither has anyone else on your client list, so time one real run before you trust a long one to it.
• Emailing each statement is another block of code (MailApp.sendEmail with the PDF in its attachments array) and another failure mode, because a send that dies at client nine leaves you guessing about clients one through eight.
• The output looks like a Google Doc. That is fine for most statements and wrong for anything that has to match letterhead a designer built.
• You now maintain a script that finds its columns by counting them. Insert a column in front of Amount in March and nothing errors: every statement quietly totals the wrong field, and it looks fine until a client checks the arithmetic.

Every item in that list is the rent you pay for owning the loop yourself. Worth it if you enjoy the code, and the SheetRender section below is the version where somebody else is on the hook for all four.

If you already use QuickBooks or Xero, stop here

Statements are a first-class feature in accounting software, and both of the big ones ship more than one kind of them. QuickBooks Online has three formats: a Balance Forward statement that covers a date range and ends in the current balance, an Open Item statement listing unpaid invoices from a start date to today, and a Transaction Statement listing everything recorded inside the range (Intuit's page carries the click-path). Xero has two. An Activity statement shows all the movement on the account across a date range; an Outstanding statement shows only what is still owed as at a date, leaving out anything already paid or credited (Xero Central).

Neither makes you go customer by customer. In QuickBooks you tick the customers you want and use Batch action > Statement; in Xero it is Send statements under Contacts, a checkbox per customer, one Email button.

What you get there starts with payments. They land against the specific invoices they paid, so a balance means something. Out of that falls an aging view of what is 30 or 60 days late, and a running balance the software has been carrying since the account opened. No spreadsheet route on this page gives you any of it. That is ledger work, and a statement is the ledger's natural printout.

Where it breaks:

• It only knows what you have entered into it. A studio logging classes in Sheets and an agent tracking rent in a workbook have ledgers their accounting package has never seen, and a statement can only summarize the transactions the software holds.
• Getting there means moving your bookkeeping. That is a migration and a monthly bill, and it dwarfs the afternoon you were trying to save.
• The statement looks the way the vendor draws it. There is some branding to set, and then there is the layout, and the layout is theirs.

One thing neither vendor documents is a statement run that goes off by itself. QuickBooks' recurring templates cover invoices and a handful of other transaction types, and statements are not among them; Xero's automatic follow-ups are aimed at overdue invoices. So somebody still starts it. That is a smaller job than any route on this page, and it is still a reminder in somebody's calendar.

I'd give the boring recommendation here. If your transactions are already in QuickBooks or Xero, don't build any of this. Use the statement run that ships with it, and spend the afternoon on something else.

The reason this page exists at all is that plenty of people are deliberately not in that world. A tutor tracking sessions. Property managers with a rent-and-charges tab per building, and bookkeepers running a side ledger for a client whose real books live somewhere else entirely. For all of them the spreadsheet is the system of record, and moving it into accounting software to get a PDF out is a bigger project than the PDF is worth.

SheetRender, which does the grouping for you

SheetRender is our product.

Grouping is the whole reason this section is here. You point it at a key column, the client name, and every row carrying that same value comes out as one PDF listing those rows, with the totals summed across them.

The look comes from an example. Hand it one statement you have sent before, a PDF or a photo of a printed one, and we rebuild the layout and wire it to your columns. You check that mapping before anything renders. Eleven transactions and ninety both work: a long statement runs on to a second page with the table headers repeating at the top.

The address each statement goes to can be a column, so building the files and sending them happens in a single run. Schedules are on the paid plans, and a schedule is the part that moves this off your desk: it fires on the 1st, reads the tab as it stands that morning, and the statements are out before you open the laptop. The recurring shape has its own page in automated PDF reports. For this job end to end, the reports use case walks the setup.

Where it breaks:

• It is not accounting software, and the list two sections up is precisely what it does not do. Nothing here applies a payment against an invoice or ages a charge into a 60-day bucket.
• No running balance column. A total is a sum over the group's rows, so "amount, and then the balance after this line" is not a thing it can work out. If the statement you are copying carries a running balance, that column has to arrive already computed in the sheet.
• Some fields belong to the document rather than to a line: the client's name, their address, the email address it gets sent to. Under grouping those are read from the group's first row, in the order the sheet has them. So a client whose August rows carry a personal address and whose September rows carry the office one gets both statements at whichever address sits higher up the tab. Make those columns consistent per client before you turn a run loose.
• The reconstruction is a model's best guess at your example, so open two or three statements yourself before the rest go out. Making the template also sends your column headers and a sample of rows to Anthropic. If the descriptions in that log are somebody's rent history or somebody's therapy sessions, find out what your own rules say about that before you upload anything. The Apps Script above never leaves your Google account, and that is a real advantage it has.
• What lands is a PDF. There is no editable document behind it, and nothing gets written back into the tab you started from, so which statements went out on the 1st is a fact the app holds and your sheet does not.

The free plan allows 50 documents a month, 50 emails, and 2 templates active at a time. It deletes what it made after 30 days and puts a footer watermark on every page it renders, the first one included. One number there is worth reading carefully: under grouping, a document is a finished statement, so 340 rows belonging to 24 clients spends 24 of the 50. That makes free enough to run a real month for a small list and see whether the layout survived. Schedules are not part of it, so the version that fires on its own is a paid tier.

How the routes compare

Quick answers

How do I create a statement of account for each customer in Excel? Build a pivot table with Customer in Rows and Amount summarized by SUM, add Customer to the report filters, and step the filter through your customer list, exporting a PDF each time. That is the no-tools answer. To stop doing the stepping by hand you need a script, or a tool that already knows how to group rows together.

What is the difference between an invoice and a statement? An invoice charges for specific work and creates a new obligation. A statement is a summary of an account over a period: what was billed and what was paid, and where that leaves the account. Most of its rows are things the client has already been sent once. Sending a statement is a reminder and a reconciliation, not a new bill.

Can Google Sheets email each client their own statement? Not on its own. An Apps Script can group the rows, build a Doc per client, and hand the PDF to MailApp in the same loop. Merge add-ons with email delivery can do it if they support grouping rows into one document, and hosted renderers pull the recipient from a column. Sheets itself has no send-this-to-that-address feature.

How do I show a running balance on a client statement? Compute it in the sheet, in a column of its own, before anything renders. A pivot table subtotals but will not carry a balance forward, and template tools list back whatever you hand them. In Google Sheets the per-client running total is a SUMIF over the rows above the current one, which means sorting the tab by client and then by date first, and anchoring the range so it grows as it fills down. Get either wrong and the balances are quietly nonsense. The reason accounting software gets this right without the fuss is that it holds every payment as well as every charge. That is a ledger doing the work, and no formula in your spreadsheet has access to the same facts.

Which one should you pick?

• Six clients, or a format you are still deciding on: the pivot table. It is a ten-minute build and you learn what the statement should say.
• A stable client list you rarely add to: one tab per client with FILTER. When it breaks it breaks loudly, which is the good kind.
• You are comfortable in Apps Script and the log lives in Google Sheets: write the grouping loop. Sixty lines, free, and yours to fix.
• The transactions are in QuickBooks or Xero already: use their statement run, and close this tab.
• The statement has to look like the one you have been sending, the client list keeps changing, and nobody wants to be at the keyboard on the 1st: use SheetRender.

Sort the routes above by one question and they fall into two piles: did you tell a machine which column holds the client, once, or do you re-derive that grouping yourself every month by dragging a filter and keeping a tab list current? The second pile is larger and it is where most people are, because the work in it is invisible until somebody counts it. Twenty-four clients at four minutes apiece is an hour and a half. The same hour and a half arrives on the 1st of next month, and the one after that. If you are going to still be doing this in six months, spend the afternoon now on something that groups the rows for you.