Google Sheets to PDF: Automate Document Generation with Typsetter

The use case: monthly invoice generation

You manage a small agency or freelance practice. Client billing data lives in Google Sheets — client name, email, services rendered, amount. On the first of every month, you want invoices generated and emailed to each client automatically, without touching a single spreadsheet cell.

By the end of this tutorial, that system will be running without you. The total setup time is about 30 minutes.

Prerequisites

A Typsetter account with an invoice template set up
Your Typsetter API key from Settings → API Keys
A Google account with Google Sheets access
Basic comfort with Google Apps Script (JavaScript-like scripting embedded in Google Workspace)

Step 1

Design your invoice template in Typsetter

Log into your Typsetter dashboard and open the Template Store. Find invoice-professional and click Use Template. This adds it to your account.

Open the template editor and note the variable names used in the template. They look like {{ client_name }}, {{ invoice_number }}, etc. You'll need these exact names when you build your Sheets columns. For the invoice-professional template, the key variables are:

client_name — full name or company name
client_email — for the invoice header (not used for sending)
invoice_number — e.g., INV-2026-001
invoice_date — YYYY-MM-DD format
due_date — YYYY-MM-DD format
service_description — one-line description
amount — numeric, e.g., 3500
currency — ISO 4217 code, e.g., USD

If you need to customize the template, use the visual editor or the Typst code editor. Click Save when done. Note the template slug from the URL or the template card.

Step 2

Prepare your Google Sheets structure

Create a new Google Sheet (or use an existing one). Add a sheet tab named Clients with the following column headers in row 1:

A	B	C	D	E	F	G	H	I
client_name	client_email	invoice_number	invoice_date	due_date	service_description	amount	currency	status
Acme Corp	billing@acme.com	INV-2026-001	2026-02-01	2026-03-01	Web Development	4500	USD	pending

The status column (column I) is important. The script will set it to sent after generating and emailing the invoice, so you never send duplicates on reruns.

Step 3

Get your Typsetter API key

In the Typsetter dashboard, go to Settings → API Keys. Click Create New Key and name it "Google Sheets". Copy the key — it starts with ts_live_sk_.

You'll add this key to the Apps Script as a script property (not hardcoded) so it stays secure.

Step 4

Write the Google Apps Script

Open your Google Sheet. Click Extensions → Apps Script. This opens the Apps Script editor. Replace the default content with the following complete script:

Code.gs — Google Apps Script
// Configuration — set via Project Settings > Script Properties
// TYPSETTER_API_KEY: your ts_live_sk_... key
// TYPSETTER_TEMPLATE: template slug, e.g. "invoice-professional"
// FROM_EMAIL: your Gmail address for the From name

function generateAndSendInvoices() {
  const props    = PropertiesService.getScriptProperties();
  const apiKey   = props.getProperty('TYPSETTER_API_KEY');
  const template = props.getProperty('TYPSETTER_TEMPLATE') || 'invoice-professional';

  const sheet = SpreadsheetApp.getActiveSpreadsheet()
                              .getSheetByName('Clients');
  const data  = sheet.getDataRange().getValues();
  const headers = data[0]; // first row is headers

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const rowObj = {};
    headers.forEach((h, idx) => { rowObj[h] = row[idx]; });

    // Skip rows already sent
    if (rowObj.status === 'sent') continue;

    try {
      // 1. Generate PDF via Typsetter API
      const pdfBytes = generatePdf(apiKey, template, rowObj);

      // 2. Create a Blob from the PDF bytes
      const blob = Utilities.newBlob(pdfBytes, 'application/pdf',
                                       `invoice-${rowObj.invoice_number}.pdf`);

      // 3. Email the invoice to the client
      GmailApp.sendEmail(
        rowObj.client_email,
        `Invoice ${rowObj.invoice_number} from Your Company`,
        `Hi ${rowObj.client_name},\n\nPlease find your invoice attached.\n\nThank you!`,
        { attachments: [blob], name: 'Your Company' }
      );

      // 4. Mark row as sent in column I (index 8)
      sheet.getRange(i + 1, 9).setValue('sent');
      Logger.log(`Sent invoice ${rowObj.invoice_number} to ${rowObj.client_email}`);

    } catch (err) {
      Logger.log(`ERROR on row ${i + 1}: ${err.message}`);
      // Mark as error so you can investigate
      sheet.getRange(i + 1, 9).setValue('error');
    }
  }
}

function generatePdf(apiKey, template, data) {
  const payload = JSON.stringify({
    template: template,
    format: 'pdf',
    data: {
      client_name:         data.client_name,
      client_email:        data.client_email,
      invoice_number:      data.invoice_number,
      invoice_date:        data.invoice_date,
      due_date:            data.due_date,
      service_description: data.service_description,
      amount:              data.amount,
      currency:            data.currency,
    }
  });

  const response = UrlFetchApp.fetch('https://api.typsetter.dev/v1/render', {
    method:  'post',
    headers: {
      'Authorization':  `Bearer ${apiKey}`,
      'Content-Type':   'application/json',
    },
    payload: payload,
    muteHttpExceptions: true,
  });

  if (response.getResponseCode() !== 200) {
    throw new Error(`API error ${response.getResponseCode()}: ${response.getContentText()}`);
  }

  return response.getContent(); // byte array
}

Step 5

Store your API key securely

In the Apps Script editor, click the gear icon (Project Settings) in the left sidebar. Scroll to Script Properties and click Add Script Property.

Add two properties:

Key: TYPSETTER_API_KEY — Value: your API key starting with ts_live_sk_
Key: TYPSETTER_TEMPLATE — Value: your template slug, e.g. invoice-professional

Click Save script properties. These are stored securely outside the script code and won't be visible in version history.

Security

Never hardcode your API key in the script source. Always use Script Properties. The script is visible to anyone who has edit access to the spreadsheet, but Script Properties are not.

Step 6

Test the script manually

Back in the script editor, select the generateAndSendInvoices function from the dropdown at the top (next to the Run button). Click Run.

The first time you run it, Google will ask for permission to access your spreadsheet, send Gmail, and make external HTTP requests. Grant all permissions.

After the run completes, check:

The Execution log at the bottom of the editor — you should see "Sent invoice INV-2026-001 to billing@acme.com"
Your Gmail Sent folder — the email with PDF attachment should be there
The Google Sheet — the Status column for that row should now show "sent"

If you see an error, check the log message. Common issues: wrong template slug, missing column in Sheet, API key typo.

Step 7

Set up the monthly trigger

To run automatically on the first of each month, set up a time-based trigger.

In the Apps Script editor, click the clock icon (Triggers) in the left sidebar. Click Add Trigger.

Function to run: generateAndSendInvoices
Event source: Time-driven
Type of time-based trigger: Month timer
Day of month: 1
Time of day: 7am — 8am

Click Save. The trigger is now live. On the 1st of each month, the script will run automatically, generate invoices for all rows with status "pending", and email them.

Workflow tip

At the end of each month, add new client rows with status "pending" to the Clients sheet. The script will pick them up on the 1st. Leave previous months' rows in place — they'll be skipped because their status is "sent".

Advanced: adding a Google Drive backup

Add these lines inside the try block, after the GmailApp.sendEmail call, to also save each PDF to a Google Drive folder:

Code.gs — Google Drive backup (add to try block)
// Save to Google Drive folder named "Invoices 2026"
const folderName = `Invoices ${new Date().getFullYear()}`;
let folders = DriveApp.getFoldersByName(folderName);
const folder  = folders.hasNext()
  ? folders.next()
  : DriveApp.createFolder(folderName);
folder.createFile(blob);

What you've built

You now have a fully automated invoice pipeline: client data in Google Sheets, PDF generation via Typsetter's Typst engine, email delivery via Gmail, and monthly execution via Apps Script triggers. The entire stack is free at the scale of a freelance practice or small agency (Google Workspace is free for individuals; Typsetter's free plan handles 100 PDFs/month).

When you outgrow the per-row generation approach, Typsetter's batch endpoint can generate all invoices in a single API call by uploading the entire sheet as a CSV — bringing generation time from minutes to under 30 seconds for 500 invoices.

Build your Google Sheets automation now

Free Typsetter account. 100 PDFs/month. Invoice template included. Google Sheets integration ready to go.

Start Free → View All Integrations