E-Invoicing from Google Sheets: ZUGFeRD, Factur-X and XRechnung with Apps Script

Somewhere in almost every company there is a Google Sheet that issues real invoices. A row per invoice, a Docs template, File, Download, PDF, and off it goes. It works, it is auditable in its own way, and nobody wants to buy an ERP to replace it. The problem arrived by law rather than by scale: since January 2025 every German business must accept structured e-invoices and issuance becomes mandatory from 2027, France switches on in September, and a PDF exported from a spreadsheet satisfies none of it.

The fix does not require leaving the spreadsheet. Apps Script ships with every Google account, and its UrlFetchApp can call a REST API that does the hard part: EN 16931 semantics, CII XML, Schematron validation, PDF/A-3 embedding. The script in this guide reads the active row, builds a JSON body, makes one HTTP call, and files a validated ZUGFeRD hybrid in Drive with a Gmail draft ready to send. Swap the endpoint and the same script produces Factur-X for France or XRechnung for German public buyers.

The spreadsheet invoicing gap

What the mandates want is a structured document: machine-readable XML carrying the EN 16931 semantic model, either standalone (XRechnung) or embedded in a PDF/A-3 container (ZUGFeRD, Factur-X). What a spreadsheet exports is a picture of an invoice. Between the two sit XML generation against a 160-field semantic model, business-rule validation that changes on regulators' calendars, and archival-grade PDF embedding, none of which Google Workspace does natively and none of which you want to hand-build in a script editor.

The data, though, is fine. A well-kept invoicing sheet already holds everything the formats require: numbers, dates, parties, VAT identifiers, line amounts, rates. Which makes the spreadsheet exactly one HTTP call away from compliance.

The architecture

Five steps, all inside your Google account except the one API call:

1. A custom menu (or a trigger) starts the script for a row.
2. The script maps the row, plus its lines from a second sheet, to invoice JSON.
3. UrlFetchApp POSTs it to /v1/create/zugferd (or facturx, or xrechnung).
4. The response blob is saved to a Drive folder and attached to a Gmail draft.
5. The row's status cell records the outcome, including validation findings on failure.

Totals and the VAT breakdown are deliberately not columns: the API computes them from the lines and refuses to emit a document whose numbers do not reconcile, so a formula typo becomes a readable error in the status cell instead of a rejected invoice at your customer.

Setup: key storage first

Create a free InvoiceXML account (100 free credits with the 30-day trial, no credit card), copy the API key, and store it where shared spreadsheets cannot leak it: open the bound script (Extensions, Apps Script), then Project Settings, Script Properties, and add INVOICEXML_API_KEY. Read it at runtime:

function getApiKey_() {
  const key = PropertiesService.getScriptProperties().getProperty('INVOICEXML_API_KEY');
  if (!key) throw new Error('Set INVOICEXML_API_KEY in Project Settings, Script Properties.');
  return key;
}

Never put the key in a cell or in the code. Sheets get shared and scripts get copied; Script Properties travel with neither.

The menu makes the script usable by the people who actually run the sheet:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('E-Invoicing')
    .addItem('Generate ZUGFeRD for this row', 'generateForActiveRow')
    .addToUi();
}

From sheet row to invoice JSON

The layout this guide assumes is the common one: an Invoices sheet with one row per invoice and a Lines sheet with one row per line item, keyed by invoice number. The mapping worth pinning up:

And the code that performs it:

function buildInvoicePayload_(rowIndex) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inv = ss.getSheetByName('Invoices');
  const headers = inv.getRange(1, 1, 1, inv.getLastColumn()).getValues()[0];
  const values = inv.getRange(rowIndex, 1, 1, inv.getLastColumn()).getValues()[0];
  const row = Object.fromEntries(headers.map((h, i) => [h, values[i]]));

  const lines = ss.getSheetByName('Lines').getDataRange().getValues().slice(1)
    .filter(l => l[0] === row['Number'])
    .map(l => ({
      quantity: l[2],
      item: { name: l[1] },
      priceDetails: { netPrice: l[3] },
      vatInformation: { rate: l[4] }
    }));

  return {
    invoice: {
      invoiceNumber: String(row['Number']),
      issueDate: Utilities.formatDate(row['Date'], 'Europe/Berlin', 'yyyy-MM-dd'),
      currency: row['Currency'],
      seller: {
        name: 'Nordlicht Solartechnik GmbH',
        vatIdentifier: 'DE298765430',
        postalAddress: { line1: 'Speicherstadtweg 8', city: 'Hamburg', postCode: '20457', country: 'DE' }
      },
      buyer: {
        name: row['Buyer'],
        postalAddress: {
          line1: row['Buyer Street'], city: row['Buyer City'],
          postCode: String(row['Buyer Postcode']), country: row['Buyer Country']
        }
      },
      paymentDetails: { paymentAccountIdentifier: 'DE02120300000000202051' },
      lines: lines
    }
  };
}

In production the seller block belongs in a Settings sheet rather than the code; it is inlined here to keep the example runnable. Two spreadsheet-specific traps: dates arrive from getValues() as Date objects, so format them explicitly, and postcodes with leading zeros must be forced to strings or the sheet will have silently turned 01067 into 1067.

The one call that matters

function generateForActiveRow() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rowIndex = sheet.getActiveRange().getRow();
  const payload = buildInvoicePayload_(rowIndex);

  const response = UrlFetchApp.fetch('https://api.invoicexml.com/v1/create/zugferd', {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + getApiKey_() },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  const statusCell = sheet.getRange(rowIndex, sheet.getLastColumn());
  if (response.getResponseCode() !== 200) {
    const findings = JSON.parse(response.getContentText());
    statusCell.setValue('Failed: ' + JSON.stringify(findings.errors || findings));
    return;
  }

  const pdf = response.getBlob().setName(payload.invoice.invoiceNumber + '.pdf');
  storeAndDraft_(pdf, payload.invoice);
  statusCell.setValue('Issued ' + new Date().toISOString().slice(0, 10));
}

muteHttpExceptions: true is the important flag: without it a 400 throws and you lose the response body, which is exactly the part you want, because it lists the violated EN 16931 rules as structured findings with field paths. Written into the status cell, they tell whoever owns the sheet precisely which column to fix. Retrying unchanged data reproduces them, so there is no retry logic to write for validation failures.

Archive to Drive, draft in Gmail

function storeAndDraft_(pdf, invoice) {
  const folder = DriveApp.getFolderById(
    PropertiesService.getScriptProperties().getProperty('INVOICE_FOLDER_ID'));
  folder.createFile(pdf);

  GmailApp.createDraft(
    invoice.buyer.email || '',
    'Invoice ' + invoice.invoiceNumber,
    'Please find attached invoice ' + invoice.invoiceNumber + '.',
    { attachments: [pdf] }
  );
}

A draft rather than an immediate send is a deliberate default: it keeps a human on the button while the mandate-relevant work (the document itself) is already done and validated. Fully automated flows swap createDraft for sendEmail. The file in Drive is the archival copy; PDF/A-3 is an archival format by construction, and the embedded XML rides inside it, so one file serves both the reader and the bookkeeping software at the other end, including DATEV, Lexoffice, and sevDesk imports on the German side.

XRechnung for German B2G

Invoicing a German federal, state, or municipal buyer from the sheet means XRechnung rather than ZUGFeRD: pure XML, validated by portals such as ZRE and OZG-RE against the KoSIT rules. Two changes to the script: the endpoint becomes /v1/create/xrechnung, and the payload gains the buyer's Leitweg-ID:

payload.invoice.buyerReference = String(row['Leitweg-ID']); // BR-DE-15 rejects the document without it

The Leitweg-ID is assigned by the public buyer during onboarding and appears on no source document, so it earns its own column on the Invoices sheet (as text: Leitweg-IDs contain leading zeros and dashes that number formatting mangles). The response is XML rather than PDF, so name the blob accordingly: response.getBlob().setName(invoiceNumber + '.xml'). For approvals, POST /v1/render/xrechnung/to/pdf turns the XML into a readable preview, since pure XML gives the person reviewing the draft nothing to look at. The full B2G treatment is in the XRechnung API toolkit.

Automating with triggers

The menu covers the invoice-by-invoice workflow. For batch issuance, a time-driven trigger (in the script editor under Triggers) runs a sweep over every row marked ready:

function issuePendingInvoices() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoices');
  const statusCol = sheet.getLastColumn();
  const rows = sheet.getDataRange().getValues();

  for (let i = 1; i < rows.length; i++) {
    if (rows[i][statusCol - 1] !== 'Ready') continue;
    generateForRow_(i + 1); // same logic as generateForActiveRow, parameterized by row
  }
}

Two platform limits worth knowing before you trust it with month-end: a single Apps Script execution stops at six minutes, so very large batches should process in chunks (mark rows as they complete and let the next trigger run resume), and UrlFetchApp has a daily call quota that sits in the thousands for Workspace accounts, far beyond any invoicing workload but worth remembering if the same project makes other calls in bulk.

Incoming e-invoices into a sheet

Reception is where the German mandate has already bitten: since January 2025 your suppliers may send structured e-invoices and you must accept them. For a Sheets-run operation the natural landing place is another sheet, and the extraction endpoints turn received files into rows:

function importReceivedInvoice(fileBlob) {
  const response = UrlFetchApp.fetch('https://api.invoicexml.com/v1/extract/json', {
    method: 'post',
    headers: { Authorization: 'Bearer ' + getApiKey_() },
    payload: { file: fileBlob },   // multipart/form-data, handled by UrlFetchApp
    muteHttpExceptions: true
  });

  const data = JSON.parse(response.getContentText());
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Received').appendRow([
    data.invoice.invoiceNumber, data.invoice.issueDate, data.invoice.seller.name,
    data.invoice.totals.grandTotal, data.invoice.currency
  ]);
}

/v1/extract/json is deterministic: it reads the embedded XML of a ZUGFeRD or Factur-X hybrid, or standalone XRechnung, CII, or UBL XML, and what the supplier declared is exactly what lands in the row. For the suppliers still sending plain PDFs, /v1/parse/json reads them with AI (scans included) and returns the same JSON shape plus a confidence object, so the script can write high-confidence reads directly and flag uncertain ones in a review column. A Gmail search for unread attachment mail, fed through this function on a trigger, is a complete intake pipeline in about thirty lines. /v1/extract/attachments rounds it out by unpacking embedded supporting documents (delivery notes, timesheets) as a ZIP.

Why not build the XML in Apps Script?

Apps Script does ship an XML library, and a determined afternoon can produce something CII-shaped with it. What it cannot produce is compliance: the EN 16931 semantic model with its calculation and code-list rules, the KoSIT layer for XRechnung, the French layer for Factur-X, and, decisively, PDF/A-3 embedding, which no Apps Script service performs at all. And all of it moves: KoSIT publishes on its own schedule, FeRD revises profiles, France finalizes specifics on the way to September. Self-built scripts age exactly as fast as those calendars.

Keeping that machinery behind one API call is the point of the service: the current rule sets are live on their effective dates with nothing for you to monitor, update, or redeploy, validation guarantees every document was checked against them at creation time, and there is professional support behind the endpoint when a rule finding needs a human explanation. The spreadsheet stays what it is good at being: the place your data lives.

Endpoint reference

Full OpenAPI 3.1 schema: api.invoicexml.com/v1/openapi  |  Interactive API explorer: api.invoicexml.com/v1/scalar

Get started

Everything in this guide fits in one script file bound to the sheet you already have. Store the key, paste the functions, adjust the column names, and the first menu click produces a validated ZUGFeRD hybrid in your Drive.

Create a free InvoiceXML account → get 100 free credits with the 30-day trial, no credit card required.

Related resources:

• ZUGFeRD API: the complete toolkit for German B2B
• Factur-X API: the complete toolkit
• XRechnung API: the complete toolkit for German B2G
• Create and validate ZUGFeRD and XRechnung invoices in Node.js
• E-invoicing on SAP BTP: ZUGFeRD, XRechnung and Factur-X from Integration Suite
• E-invoicing in Zapier: three InvoiceXML apps for ZUGFeRD, XRechnung, Factur-X, CII and UBL
• E-invoicing mandate deadlines by country
• Full API documentation

InvoiceXML is a REST API for European e-invoice compliance covering ZUGFeRD, XRechnung, Factur-X, Peppol UBL, and CII. Stateless processing, GDPR compliant by architecture, and callable from anywhere that speaks HTTPS, Google Apps Script very much included.