How to Automatically Generate PDFs from Google Sheets

The problem

You have data in a Google Sheet. Client names, invoice amounts, project details, course completions. And you need to turn individual rows into formatted PDF documents. Invoices, proposals, certificates, summaries, reports.

Right now you're doing it manually. Copy the data from the row, paste it into a template, adjust the formatting, export as PDF, save to Drive or email it out. For one document, that's fine. When you're doing it 10 or 50 times a week, that's hours of work that a script can do in seconds.

Google Sheets has a built-in "Download as PDF" option, but it exports the entire sheet as a flat grid. It doesn't generate individual, formatted documents from row data. For that, you need a small amount of Apps Script, which is Google's built-in scripting language that runs inside your spreadsheet.

This guide walks through the complete setup. No coding experience required. Every code block is copy-paste ready.

What we're building

An automation that takes a row of data from your Google Sheet, plugs it into a Google Doc template, converts the result to PDF, saves it to a Drive folder, and optionally emails it to the recipient. One click per row, or fully automatic when new rows appear.

The pattern works for any row-to-document workflow: invoices, proposals, client summaries, certificates, quotes, receipts, agreements. Anything where the structure stays the same but the details change per row.

Before you start

You need three things:

1. A Google Sheet with your data (one row per document you want to generate)
2. A Google Doc template with placeholder tags like {{client\_name}}, {{amount}}, {{date}}
3. A Google Drive folder where generated PDFs will be saved

Setting up the Doc template

Create a new Google Doc. Write it exactly the way you want the final PDF to look. Add your branding, adjust the layout, set the fonts. Wherever you want dynamic data from the Sheet to appear, insert a placeholder in double curly braces:

• {{Client Name}}
• {{Invoice Number}}
• {{Amount}}
• {{Date}}
• {{Project Description}}

Make sure the placeholder names match your column headers in the Sheet exactly, including capitalization and spacing. Save the Doc and copy its ID from the URL. It's the long string between /d/ and /edit in the address bar.

Setting up the Drive folder

Create a folder in Google Drive. Name it whatever makes sense for your workflow ("Generated Invoices," "Client PDFs," etc.). Open it and copy the folder ID from the URL. It's the string after /folders/.

Step-by-step setup

Step 1: Open the Apps Script editor

In your Google Sheet, click Extensions, then Apps Script. This opens the script editor in a new tab. Delete any code that's already there.

Step 2: Paste the PDF generator script

Copy and paste this entire script:

function generatePDF() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row = sheet.getActiveRange().getRow();

  // Skip if header row
  if (row === 1) {
SpreadsheetApp.getUi().alert('Select a data row, not the header.');
return;
  }

  // === CONFIGURATION ===
  var templateId = 'YOUR\_GOOGLE\_DOC\_TEMPLATE\_ID';
  var folderId   = 'YOUR\_DRIVE\_FOLDER\_ID';
  var emailCol   = 'E';  // column with email address, or null
  // === END CONFIGURATION ===

  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var data    = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  // Copy the template
  var folder  = DriveApp.getFolderById(folderId);
  var docName = data[0] + ' - Document';
  var copy    = DriveApp.getFileById(templateId).makeCopy(docName, folder);
  var doc     = DocumentApp.openById(copy.getId());
  var body    = doc.getBody();

  // Replace every {{placeholder}} with the matching row data
  for (var i = 0; i < headers.length; i++) {
body.replaceText('\\\\\{\\\\\{' + headers[i] + '\\\\\}\\\\\}', data[i] || '');
  }
  doc.saveAndClose();

  // Convert to PDF and delete the temp Doc
  var pdf = folder.createFile(
DriveApp.getFileById(copy.getId()).getAs('application/pdf')
  ).setName(docName + '.pdf');
  DriveApp.getFileById(copy.getId()).setTrashed(true);

  // Email the PDF (optional)
  if (emailCol) {
var email = data[emailCol.charCodeAt(0) - 65];
if (email && email.toString().includes('@')) {
  MailApp.sendEmail({
    to: email,
    subject: docName,
    body: 'Please find the attached document.',
    attachments: [pdf.getAs(MimeType.PDF)]
  });
}
  }

  SpreadsheetApp.getUi().alert('PDF generated and saved to Drive.');
}

// Add a menu button to your Sheet
function onOpen() {
  SpreadsheetApp.getUi()
.createMenu('PDF Generator')
.addItem('Generate PDF for selected row', 'generatePDF')
.addToUi();
}

Step 3: Configure the script

Update three values near the top:

1. Replace YOUR\_GOOGLE\_DOC\_TEMPLATE\_ID with the ID from your Doc template URL
2. Replace YOUR\_DRIVE\_FOLDER\_ID with the ID from your output folder URL
3. Set emailCol to the column letter containing email addresses, or set it to null if you don't want email delivery

Step 4: Authorize and run

Click the Save icon (or Ctrl+S). Select onOpen from the function dropdown at the top. Click Run.

Google will ask you to authorize the script. It needs access to Sheets, Docs, Drive, and Gmail. Click through the permissions. You'll see a security warning because the script isn't verified by Google. Click "Advanced," then "Go to [your project name]."

Go back to your Sheet and refresh the page. A new menu item appears in the menu bar: PDF Generator.

Step 5: Test it

Click on any data row in your Sheet (not the header). Click PDF Generator in the menu bar, then "Generate PDF for selected row."

Check your Drive folder. The PDF should be there. If you set an email column, check that the email arrived too.

Open the PDF. Every placeholder from your Doc template should be replaced with the corresponding data from the row you selected.

How to extend this

Run it automatically for every new row. In the Apps Script editor, click the clock icon in the left sidebar. Click "Add Trigger." Select generatePDF as the function and set the event type to "On form submit" or "On change." Now every new row generates a PDF without you clicking anything.

Batch process an entire sheet. Add this function to process every row at once:

function generateAllPDFs() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var last  = sheet.getLastRow();
  for (var r = 2; r <= last; r++) {
sheet.setActiveRange(sheet.getRange(r, 1));
generatePDF();
Utilities.sleep(1500); // pause to avoid rate limits
  }
}

Use Google Slides instead of Docs. If your output needs to be a designed layout (certificates, branded proposals, one-pagers), use a Slides template instead. The pattern is identical. Use SlidesApp.openById() instead of DocumentApp.openById(), and replaceAllText() on each slide.

Add a file name pattern. Replace the hardcoded docName line with something dynamic. For example: var docName = data[0] + ' - Invoice ' + data[1]; would generate filenames like "Acme Corp - Invoice INV-2026-001."

Common issues

"TypeError: Cannot read properties of null" means your template ID or folder ID is wrong. Double-check both by opening them directly in a browser tab.

Placeholders not replaced. The placeholder text in your Doc must match the Sheet column header exactly. {{client name}} is not the same as {{Client Name}}. Check capitalization and spacing.

"Service invoked too many times." Google limits daily email and Drive operations. If you're processing hundreds of rows, add Utilities.sleep(2000) between iterations and spread large batches across multiple days.

PDF formatting looks wrong. The fix is almost always in the Google Doc template, not the script. Adjust margins, fonts, and spacing in the Doc. The PDF conversion inherits the Doc's formatting exactly.

Blank rows generating empty PDFs. If your Sheet has empty rows mixed into the data, add a check at the top of the function: if (!data[0]) return; This skips any row where the first column is empty.