Generate PDFs from Google Sheets Using Apps Script

Generating PDFs from a spreadsheet — invoices, reports, timesheets — is a common requirement that Apps Script handles well. You can export the whole sheet, a specific range, or a custom-styled version.

Export the active sheet as a PDF

function exportSheetAsPdf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetId = ss.getActiveSheet().getSheetId();
  const ssId = ss.getId();

  const url = `https://docs.google.com/spreadsheets/d/${ssId}/export` +
    `?format=pdf` +
    `&size=A4` +
    `&portrait=true` +
    `&fitw=true` +             // Fit to width
    `&sheetnames=false` +
    `&printtitle=false` +
    `&pagenumbers=false` +
    `&gridlines=false` +
    `&fzr=false` +             // Don't repeat frozen rows
    `&gid=${sheetId}`;

  const token = ScriptApp.getOAuthToken();
  const response = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + token },
  });

  const pdfBlob = response.getBlob().setName(ss.getName() + '.pdf');

  // Save to Drive
  const file = DriveApp.createFile(pdfBlob);
  Logger.log('PDF saved: ' + file.getUrl());
}

Export a specific range as PDF

function exportRangeAsPdf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const sheetId = sheet.getSheetId();
  const ssId = ss.getId();

  // Define range (row/column are 1-indexed, converted to 0-indexed for URL)
  const range = sheet.getRange('A1:F20');
  const startRow = range.getRow() - 1;
  const startCol = range.getColumn() - 1;
  const endRow   = startRow + range.getNumRows();
  const endCol   = startCol + range.getNumColumns();

  const url = `https://docs.google.com/spreadsheets/d/${ssId}/export` +
    `?format=pdf` +
    `&size=A4` +
    `&portrait=true` +
    `&fitw=true` +
    `&gridlines=false` +
    `&gid=${sheetId}` +
    `&r1=${startRow}&c1=${startCol}&r2=${endRow}&c2=${endCol}`;

  const token = ScriptApp.getOAuthToken();
  const blob = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + token },
  }).getBlob().setName('Report.pdf');

  DriveApp.createFile(blob);
  Logger.log('Range exported as PDF.');
}

Email the PDF as an attachment

function emailPdf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const sheetId = ss.getActiveSheet().getSheetId();

  const url = `https://docs.google.com/spreadsheets/d/${ssId}/export?format=pdf&gid=${sheetId}&size=A4&portrait=true&fitw=true&gridlines=false`;

  const token = ScriptApp.getOAuthToken();
  const pdfBlob = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + token },
  }).getBlob().setName('Monthly Report.pdf');

  GmailApp.sendEmail(
    '[email protected]',
    'Monthly Report',
    'Please find this month\'s report attached.',
    { attachments: [pdfBlob] }
  );

  Logger.log('Report emailed as PDF.');
}

Save a PDF per row (e.g. per invoice)

function generateInvoicePdfs() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName('Invoice Data');
  const templateSheet = ss.getSheetByName('Invoice Template');
  const data = dataSheet.getDataRange().getValues().slice(1);
  const outputFolder = DriveApp.getFolderById('YOUR_FOLDER_ID');

  data.forEach(row => {
    const [invoiceNum, clientName, amount] = row;

    // Populate template
    templateSheet.getRange('B1').setValue(invoiceNum);
    templateSheet.getRange('B2').setValue(clientName);
    templateSheet.getRange('B3').setValue(amount);
    SpreadsheetApp.flush();

    // Export as PDF
    const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export` +
      `?format=pdf&gid=${templateSheet.getSheetId()}&size=A4&portrait=true&fitw=true&gridlines=false`;

    const token = ScriptApp.getOAuthToken();
    const blob = UrlFetchApp.fetch(url, {
      headers: { Authorization: 'Bearer ' + token },
    }).getBlob().setName(`Invoice-${invoiceNum}.pdf`);

    outputFolder.createFile(blob);
    Logger.log(`Created: Invoice-${invoiceNum}.pdf`);
  });
}

Key URL parameters

Parameter	Values	Description
`format`	`pdf`	Output format
`size`	`A4`, `LETTER`, `LEGAL`	Page size
`portrait`	`true`/`false`	Orientation
`fitw`	`true`/`false`	Fit to page width
`gridlines`	`true`/`false`	Show gridlines
`sheetnames`	`true`/`false`	Print sheet name
`pagenumbers`	`true`/`false`	Print page numbers
`gid`	Sheet ID	Which sheet to export

Tips

ScriptApp.getOAuthToken() is required to authenticate the export URL request.
The export URL approach works because Apps Script runs as the script owner — no extra OAuth setup needed.
Use SpreadsheetApp.flush() after writing to a template before exporting, to ensure the latest values are included.
For complex layouts, consider creating a Google Doc template and exporting that as PDF instead.