如何将Google Sheets新建工资单工作表单独导出为PDF并邮件发送？

安全阿岳

2026-8-7

Google Sheets工资单：单独导出指定工作表为PDF并邮件发送

我开发了一个基于Google Sheets的工资单工具，点击「Run Payroll」按钮会生成对应发薪日的新工资单工作表。现在需要实现：将新生成的工作表单独导出为PDF，存入指定的Google Drive文件夹，并自动通过邮件发送。尝试用Zapier实现时，会导出整个表格而非仅新建的工作表，无法满足需求。

解决方案

通过扩展现有Google Apps Script代码，添加自定义函数实现以下功能：

单独导出指定工作表为PDF
将PDF保存到指定Drive文件夹
自动发送包含PDF附件的邮件

步骤1：添加导出与邮件发送函数

新增exportSheetToPDFAndEmail函数，接收目标工作表对象、邮件收件人等参数，完成PDF导出、保存和邮件发送：

function exportSheetToPDFAndEmail(sheet, recipientEmail, folderId) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const spreadsheetId = spreadsheet.getId();
  const sheetId = sheet.getSheetId();
  const sheetName = sheet.getName();
  
  // 构建PDF导出链接（仅导出指定工作表）
  const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/export?` +
              `format=pdf` +
              `&gid=${sheetId}` +
              `&portrait=true` +
              `&fitw=true` +
              `&sheetnames=false` +
              `&printtitle=false` +
              `&pagenumbers=false` +
              `&gridlines=false` +
              `&fzr=false` +
              `&top_margin=0.5` +
              `&bottom_margin=0.5` +
              `&left_margin=0.5` +
              `&right_margin=0.5`;

  // 获取OAuth令牌用于请求
  const token = ScriptApp.getOAuthToken();
  const response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': `Bearer ${token}`
    }
  });
  
  // 将PDF保存到指定Drive文件夹
  const folder = DriveApp.getFolderById(folderId);
  const pdfFile = folder.createFile(response.getBlob()).setName(`Paystub_${sheetName}.pdf`);
  
  // 发送邮件
  MailApp.sendEmail({
    to: recipientEmail,
    subject: `工资单：${sheetName}`,
    body: `附件是您的工资单PDF文件。`,
    attachments: [pdfFile.getAs(MimeType.PDF)]
  });
}

步骤2：配置参数

替换以下参数为实际值：

recipientEmail：接收邮件的邮箱地址
folderId：保存PDF的Google Drive文件夹ID（可从文件夹URL中获取）

步骤3：在工资单生成后调用函数

修改runPayroll函数，在末尾添加调用代码，确保新工作表生成后自动触发导出和邮件发送：
在runPayroll函数的最后（dumpLog()注释下方）添加：

// 导出新工资单为PDF并发送邮件
const recipientEmail = 'your-recipient@example.com'; // 替换为实际收件邮箱
const targetFolderId = 'your-folder-id'; // 替换为实际Drive文件夹ID
exportSheetToPDFAndEmail(paystubSheet, recipientEmail, targetFolderId);

修改后的完整代码

// Add menu items.
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Run Payroll', functionName: 'runPayroll'},
    {name: 'Delete Paystub', functionName: 'deletePayroll'}, 
    {name: 'Rebuild YTD from Stubs', functionName: 'rebuildYTD'}
  ];
  spreadsheet.addMenu('Payroll', menuItems);
}

function runPayroll() {
  var spreadsheet = SpreadsheetApp.getActive();
  var templateSheet = spreadsheet.getSheetByName('Template');
  
  // Validate weekly input
  if(!validateInput()) {
    return;
  }
    
  // Create new sheet for pay date
  var sheetName = Utilities.formatDate(getNamedValue('TempPayDate'), "PST", "MM/dd/yyyy");
  var paystubSheet = spreadsheet.getSheetByName(sheetName);
  if (paystubSheet) {
    // we already have a paystub for this date. remove that stub's values from YTD before we create a new one.
    removePayrollFromYTD(paystubSheet);
    paystubSheet.clear();
    SpreadsheetApp.flush();  // ran into a race issue here with YTD not getting updated fast enough for the new stub creation -- flush() seems to fix it
  } else {
    paystubSheet = spreadsheet.insertSheet(sheetName, spreadsheet.getNumSheets());
  }  

  // Copy pay data from Template to new sheet 
  var dataRange = templateSheet.getRange(1, 1, 45, 6);
  dataRange.copyValuesToRange(paystubSheet, 1, 6, 1, 45);
  dataRange.copyFormatToRange(paystubSheet, 1, 6, 1, 45);
  paystubSheet.setRowHeight(1, 10);
  paystubSheet.setRowHeight(2, 10);
  paystubSheet.setRowHeight(43, 10);
  paystubSheet.setRowHeight(44, 10);
    
  // Update YTD sheet. Grab current values from the template before we clean it up
  updateYTDFromCurrent();
    
  // Reset template data
  cleanup();
  
  // Activate new paystub and pre-set selection for printing
  paystubSheet.activate();
  paystubSheet.setActiveSelection(paystubSheet.getRange(1, 1, 45, 6));

//  Logger.log(sheetName);
//  dumpLog();

  // 导出新工资单为PDF并发送邮件
  const recipientEmail = 'your-recipient@example.com'; // 替换为实际收件邮箱
  const targetFolderId = 'your-folder-id'; // 替换为实际Drive文件夹ID
  exportSheetToPDFAndEmail(paystubSheet, recipientEmail, targetFolderId);
}

function validateInput() {
  // hours
  var hrsReg = getNamedValue('TempHoursReg');
  var hrsOT = getNamedValue('TempHoursOT');
  if(hrsReg + hrsOT <= 0) {
    Browser.msgBox('Error', 'Work harder. Enter some hours.', Browser.Buttons.OK);
    return 0;
  }

  // taxes
  var taxFed = getNamedValue('TempFedTax');
  var taxCa = getNamedValue('TempCaTax');
  if(taxFed <= 0 || taxCa <= 0) {
    Browser.msgBox('Error', 'Pay your fair share. Enter some taxes.', Browser.Buttons.OK);
    return 0;
  }
    
  // dates
  // very basic sanity on pay period
  var begin = getNamedValue('TempPeriodBegin');
  var end = getNamedValue('TempPeriodEnd');
  if(end < begin) {
    Browser.msgBox('Error', 'Pay period looks funky.', Browser.Buttons.OK);
    return 0;
  }

  // warn on duplicate payroll
  var payDate = getNamedValue('TempPayDate');
  var sheetName = Utilities.formatDate(payDate, "PST", "MM/dd/yyyy");
  var paystubSheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  if (paystubSheet) {
    var resp = Browser.msgBox('Do Over?', 'You already have a paycheck for this date -- are you sure you want to overwrite it?', Browser.Buttons.YES_NO);
    if(resp == 'no') {
       return 0;
    }
  }
  
  return 1;
}

function deletePayroll() {
  // prompt for the date 
  var strDel = Browser.inputBox('Delete Payroll', 'Please enter the date of the paystub to remove (mm/dd/yyyy):', Browser.Buttons.OK_CANCEL);
  if (strDel == 'cancel') {
    return;
  }

  // search for sheet and confirm deletion
  var spreadsheet = SpreadsheetApp.getActive();
  var deleteSheet = spreadsheet.getSheetByName(strDel);
  if(deleteSheet) {
    var resp = Browser.msgBox('Are you sure?', Utilities.formatString("Located the payroll for %s -- continue with deletion?", strDel), Browser.Buttons.YES_NO);
    if(resp == 'no') {
       return 0;
    }
  } else {
    Browser.msgBox('Error', Utilities.formatString("Couldn't find a paystub for %s. Nothing to delete. Hint: make sure you are using the form '01/09/2017' rather than '1/9/17'.", strDel), Browser.Buttons.OK);
    //todo: make this friendier
    return;
  }
  
  // Subtract from YTD sheet
  removePayrollFromYTD(deleteSheet);
  
  // Delete tab and return to template
  spreadsheet.deleteSheet(deleteSheet);
  SpreadsheetApp.flush();
  spreadsheet.getSheetByName('Template').activate();
  
  // todo: is it possible to trap the Delete Sheet action from UI and trigger this function? or at least ask if we should adjust YTD before deleting
}

function rebuildYTD() {
  // todo: 
  // copy current YTD to backup column or sheet
  // zero out current YTD values
  // cycle through all paystubs to sum new values
  Browser.msgBox('Payroll', "Haven't gotten around to this feature yet...", Browser.Buttons.OK);
}

function updateYTDFromCurrent() {
  incrementNamedValue('YTDFedTax', getNamedValue('CurFedTax'));
  incrementNamedValue('YTDSocSec', getNamedValue('CurSocSec'));
  incrementNamedValue('YTDMedicare', getNamedValue('CurMedicare'));
  incrementNamedValue('YTDCaTax', getNamedValue('CurCaTax'));
  incrementNamedValue('YTDCaSdi', getNamedValue('CurCaSdi'));
  incrementNamedValue('YTDWagesReg', getNamedValue('CurWagesReg'));
  incrementNamedValue('YTDWagesOT', getNamedValue('CurWagesOT'));
}

function cleanup() {
  clearNamedValue('TempHoursReg');
  clearNamedValue('TempHoursOT');
  clearNamedValue('TempFedTax');
  clearNamedValue('TempCaTax');
/*  
  clearNamedValue('TempPeriodBegin');
  clearNamedValue('TempPeriodEnd');
  clearNamedValue('TempPayDate');
*/
}

function getNamedValue(valueName) {
  var nr = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(valueName);
  return nr.getValue();
}

function incrementNamedValue(valueName, valueToAdd) {
  var nr = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(valueName);
  nr.setValue(nr.getValue() + valueToAdd);
}

function clearNamedValue(valueName) {
  var nr = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(valueName);
  if(nr) {
    nr.clearContent();
  }
}

function removePayrollFromYTD(payrollSheet) {
  // note: this will be fragile, we don't have named ranges for the individual payroll sheets so it's using specific cell IDs instead
  var wagesReg = payrollSheet.getRange('E30');
  incrementNamedValue('YTDWagesReg', -wagesReg.getValue());
  
  var wagesOT = payrollSheet.getRange('E31');
  incrementNamedValue('YTDWagesOT', -wagesOT.getValue());
  
  var fedTax = payrollSheet.getRange('E38');
  incrementNamedValue('YTDFedTax', -fedTax.getValue());

  var caTax = payrollSheet.getRange('E41');
  incrementNamedValue('YTDCaTax', -caTax.getValue());

  var caSdi = payrollSheet.getRange('E42');
  incrementNamedValue('YTDCaSdi', -caSdi.getValue());

  var socSec = payrollSheet.getRange('E39');
  incrementNamedValue('YTDSocSec', -socSec.getValue());

  var medicare = payrollSheet.getRange('E40');
  incrementNamedValue('YTDMedicare', -medicare.getValue());
}

function dumpLog() {
  var logSheet = SpreadsheetApp.getActive().getSheetByName('devlog');
  if(!logSheet) {
    logSheet = SpreadsheetApp.getActive().insertSheet('devlog', 0); 
  }

  var logData = Logger.getLog();
  var a1 = logSheet.getRange(1,1);
  a1.setValue(logData);
}

function exportSheetToPDFAndEmail(sheet, recipientEmail, folderId) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const spreadsheetId = spreadsheet.getId();
  const sheetId = sheet.getSheetId();
  const sheetName = sheet.getName();
  
  // 构建PDF导出链接（仅导出指定工作表）
  const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/export?` +
              `format=pdf` +
              `&gid=${sheetId}` +
              `&portrait=true` +
              `&fitw=true` +
              `&sheetnames=false` +
              `&printtitle=false` +
              `&pagenumbers=false` +
              `&gridlines=false` +
              `&fzr=false` +
              `&top_margin=0.5` +
              `&bottom_margin=0.5` +
              `&left_margin=0.5` +
              `&right_margin=0.5`;

  // 获取OAuth令牌用于请求
  const token = ScriptApp.getOAuthToken();
  const response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': `Bearer ${token}`
    }
  });
  
  // 将PDF保存到指定Drive文件夹
  const folder = DriveApp.getFolderById(folderId);
  const pdfFile = folder.createFile(response.getBlob()).setName(`Paystub_${sheetName}.pdf`);
  
  // 发送邮件
  MailApp.sendEmail({
    to: recipientEmail,
    subject: `工资单：${sheetName}`,
    body: `附件是您的工资单PDF文件。`,
    attachments: [pdfFile.getAs(MimeType.PDF)]
  });
}

注意事项

首次运行脚本时，需要授权访问Google Drive和Gmail的权限
确保指定的Drive文件夹ID正确，且脚本有访问该文件夹的权限
可根据需求调整PDF导出参数（如页面方向、边距、是否显示网格线等）

内容的提问来源于stack exchange，提问作者Samantha A

如何将Google Sheets新建工资单工作表单独导出为PDF并邮件发送？

解决方案 #

步骤1：添加导出与邮件发送函数 #

步骤2：配置参数 #

步骤3：在工资单生成后调用函数 #

修改后的完整代码 #