Google Sheets AutomationDifficulty: Intermediate⚡ On Demand or Monthly Timer

Generate PDF from Google Sheet & Email as Attachment

Converts a specific sheet tab or printable invoice range into a styled PDF blob and emails it to a client or manager with custom HTML formatting.

Required OAuth Scopes: https://www.googleapis.com/auth/spreadsheets • https://www.googleapis.com/auth/gmail.send • https://www.googleapis.com/auth/drive
Code.gs (Apps Script V8 Runtime)
function emailSheetTabAsPdf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Invoice');
  const recipient = sheet.getRange('B4').getValue(); // Client email
  const invoiceNumber = sheet.getRange('B2').getValue();
  
  const url = ss.getUrl().replace(/edit$/, '') + 'export?exportFormat=pdf&format=pdf' +
    '&size=A4&portrait=true&fitw=true&gridlines=false&printtitle=false' +
    '&sheetnames=false&fzr=false&gid=' + sheet.getSheetId();
    
  const params = {
    headers: { 'Authorization': 'Bearer ' + ScriptApp.getOAuthToken() },
    muteHttpExceptions: true
  };
  
  const response = UrlFetchApp.fetch(url, params);
  const pdfBlob = response.getBlob().setName('Invoice_' + invoiceNumber + '.pdf');
  
  GmailApp.sendEmail(recipient, 'Your Invoice #' + invoiceNumber, 'Please find attached your invoice.', {
    attachments: [pdfBlob],
    name: 'Accounting Dept'
  });
}

Line-by-Line Code Explanation

Line 2-5: Identifies the active workbook, invoice sheet tab, client email cell (B4), and invoice number (B2).
Line 7-9: Constructs a Google Drive export URL requesting an A4 portrait PDF with gridlines removed.
Line 11-14: Authenticates the export request using the script runner’s active OAuth session token.
Line 16-17: Fetches the PDF stream via UrlFetchApp and names the file dynamically.
Line 19-22: Transmits the invoice PDF as an email attachment via GmailApp.

Step-by-Step Setup & Deployment

  1. Open your spreadsheet invoice tab and note the sheet name and email recipient cell coordinates.
  2. Paste the snippet into Extensions > Apps Script.
  3. Run the function once manually to authorize Drive, Sheets, and Gmail OAuth scopes.
  4. Attach this function to a spreadsheet drawing button (Insert > Drawing > Assign script: emailSheetTabAsPdf).

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

  • Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
  • Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
  • OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More Google Sheets Automation Snippets