Google表格:在指定范围内生成PDF文件并将其发送到电子邮件 [英] Google Sheet: Generate a PDF file within a named range and sent it to email

问题描述

我正在尝试通过电子邮件发送PDF文件.我在此处上看到了有关如何发送电子邮件的代码，该代码虽然有效，但它会生成所有可见的工作表(隐藏工作表)不包含在PDF文件中).我已经对其进行了修改以生成活动工作表，但是该工作表上的所有数据仍然可见.我有一个命名范围，它称为"print_area_1".如果我只想显示"print_area_1"，是否可以部分包含在PDF文件中?

I'm trying to send a PDF file via email. I saw a code here on how to send an email and it kinda works, but it generates all the visible sheets (hidden sheet is not included) in the PDF file. I already modified it to generate the active sheet but all the data on that sheet is still visible. I have a named range and it's called "print_area_1". Is it possible if I only want to show the "print_area_1" part on the PDF file?

这是我修改过的代码.

function sendSheetToPdfwithA1MailAdress(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var shName = sh.getName()
  sendSpreadsheetToPdf(0, shName, sh.getRange('Q12').getValue(), sh.getRange('D12').getValue() , "This is it !");
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody, url, sheet, range) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var spreadsheetId = ss.getId()  
  var url_base = ss.getUrl().replace(/edit$/,'');

  var rangeParam = ''
  var sheetParam = ''
  if (range) {
    rangeParam = 
      + '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    sheetParam = '&gid=' + sheet.getSheetId()
  }
 
  var url_ext = 'export?exportFormat=pdf&format=pdf'
      + '&size=folio'
      + '&portrait=true'
      + '&fitw=true'
      + '&top_margin=0.5'              
      + '&bottom_margin=0.5'          
      + '&left_margin=0.5'             
      + '&right_margin=0.5'        
      + '&sheetnames=false&printtitle=false&pagenumbers=false'
      + '&gridlines=true'
      + '&fzr=false'
      + sheetParam
      + rangeParam;

  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);
  }
}

推荐答案

我刚刚添加了此脚本/变量以使其正常工作.

I just added this script / variable to make it work.

var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('print_area_1');

这是完整的代码:

function sendSheetToPdfwithA1MailAdress(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var shName = sh.getName()
  sendSpreadsheetToPdf(0, shName, sh.getRange('Q12').getValue(), sh.getRange('D12').getValue() , "This is it !");
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody, url, sheet, range) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var spreadsheetId = ss.getId()  
  var url_base = ss.getUrl().replace(/edit$/,'');

  var rangeParam = ''
  var sheetParam = ''
  if (range) {
    rangeParam = 
      + '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    sheetParam = '&gid=' + sheet.getSheetId()
  }
 
  var url_ext = 'export?exportFormat=pdf&format=pdf'
      + '&size=folio'
      + '&portrait=true'
      + '&fitw=true'
      + '&top_margin=0.5'              
      + '&bottom_margin=0.5'          
      + '&left_margin=0.5'             
      + '&right_margin=0.5'        
      + '&sheetnames=false&printtitle=false&pagenumbers=false'
      + '&gridlines=true'
      + '&fzr=false'
      + sheetParam
      + rangeParam;

  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);
  }
}

这篇关于Google表格:在指定范围内生成PDF文件并将其发送到电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！