将活动工作表作为 PDF 发送到单元格中列出的电子邮件 [英] Send active sheet as PDF to email listed in cell

问题描述

我正在尝试使用下面的脚本将 Google 表格文档中的第一张表格以 PDF 格式发送到电子邮件.要发送到的电子邮件列在单元格 A1 中.

I'm trying to use the script below to send the first sheets in a Google Sheets document to an email as PDF. The email to send to, is listed in cell A1.

但是，此脚本将整个电子表格作为 PDF 发送，而不仅仅是第一张工作表.我一直在尝试使用 Stack Overflow 中的一些其他脚本，但这是唯一真正发送电子邮件的脚本.

However, this script send the entire spreadsheet as an PDF and not just the first sheet. I have been trying to use some of the other scripts from Stack Overflow, but this is the only one that actually sends an email.

        /* Email Google Spreadsheet as PDF */
function emailGoogleSpreadsheetAsPDF() {
  
  // Send the PDF of the spreadsheet to this email address
  var email = "[e-mail]"; 
  
  // Get the currently active spreadsheet URL (link)
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Subject of email message
  var subject = "PDF generated from spreadsheet " + ss.getName(); 

  // Email Body can  be HTML too 
  var body = "Install the <a href='http://www.labnol.org/email-sheet'>Email Spreadsheet add-on</a> for one-click conversion.";
  
  var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");
  
  blob.setName(ss.getName() + ".pdf");
  
  // If allowed to send emails, send the email with the PDF attachment
  if (MailApp.getRemainingDailyQuota() > 0) 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments:[blob]     
    });  
}

推荐答案

以下是一个工作版本，其中包含一些您可能想要使用的有用参数.

below is a working version with a few useful parameters you may want to use.

function sendSheetToPdfwithA1MailAdress(){ // this is the function to call
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[0]; // it will send sheet 0 wich is the first sheet in the spreadsheet.
  // if you change the number, change it also in the parameters below
  var shName = sh.getName()
  sendSpreadsheetToPdf(0, shName, sh.getRange('A1').getValue(),"test email with the adress in cell A1 ", "This is it !");
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId()  
  var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;  
  var url_base = spreadsheet.getUrl().replace(/edit$/,'');

  var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf

      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
      // following parameters are optional...
      + '&size=A4'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw=true'        // fit to width, false for actual size
      + '&sheetnames=true&printtitle=false&pagenumbers=true'  //hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }

  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  if (email) {
    var mailOptions = {
      attachments:blob, htmlBody:htmlbody
    }
MailApp.sendEmail(
      email, 
      subject+" (" + pdfName +")", 
      "html content only", 
      mailOptions);

MailApp.sendEmail(
      Session.getActiveUser().getEmail(), 
      "FRWD "+subject+" (" + pdfName +")", 
      "html content only", 
      mailOptions);
  }
}

这篇关于将活动工作表作为 PDF 发送到单元格中列出的电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！