Google电子表格脚本仅将活动工作表导出为PDF [英] Google spreadsheet script export active sheet only to PDF
本文介绍了Google电子表格脚本仅将活动工作表导出为PDF的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我创建了循环脚本,用于发送带有PDF附件的电子邮件.循环功能正常工作,没有任何错误.但是带有附件PDF文件发送的电子邮件会显示所有工作表的所有数据.我只想创建活动表的PDF文件,而不能创建其他文件.
I created loop script for send email with PDF attachment. Loop function works correctly without any error. But email sent with attached PDF file shows all data of all sheets. I want to create PDF file only of ACTIVE sheet and not others.
function SendInvoiceNew4() {
var sheet = SpreadsheetApp.getActiveSheet();
// Loop from CELL Number Value to CELL Number Value EQUAL
for(i=sheet.getRange("H11").getValue();i<=sheet.getRange("I11").getValue();i++) {// *************** Enter Start Invoice Serial No Cell Reference & Last Serial No Cell is Auto
sheet.getRange("H11").setValue(i); //Auto Enter Next Loop Serail Number
var InvDate = Utilities.formatDate(new sheet.getRange("H13").getValue(), "GMT+1", "MMM-yyyy") //Set invoice Date Format = MONTH & YEAR
var emailTo = sheet.getRange("B12").getValue(); //Get Email Address from Data
var message = 'Dear' + "\n\n" + 'See attached your attached invoice in PDF format.' + "\n\n" + 'Thanking you' + "\n" + 'www.xyz.in' + "\n" + '[DO NOT REPLY to this Email.]'; //Enter Custom Messagen ************************************************** Message Body
var subject = 'Invoice for Month ' + InvDate; // ************* Enter Cell Reference for Date of Invoice for Subject
// Convert Invoice Sheet to PDF
var originalSpreadsheet = SpreadsheetApp.getActive(); // Set original invoice sheet
var pdf = DriveApp.getFileById(originalSpreadsheet.getId()).getAs('application/pdf').getBytes(); // Convert PDF file
var attach = {fileName:'Invoice',content:pdf, mimeType:'application/pdf'}; //Set File Name
// Send Email with attached PDF file
MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
//MailApp.sendEmail(emailTo, subject, message);
SpreadsheetApp.flush(); // Make sure the cell is updated right away in case the script is interrupted
}
}
推荐答案
You will have to adapt your script to send the PDF attachment using @James D function.
// ...
// Send Email with attached PDF file
// FROM:
// MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
// TO:
emailSpreadsheetAsPDF(emailTo, subject, message, sheetName);
// ...
我将James函数的签名更改为使用您的循环参数.
I changed the signature of James' function to use your loop parameters.
function emailSpreadsheetAsPDF(email, subject, body, sheetName) {
var email = email; // Enter the required email address here
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName); // Enter the name of the sheet here
var subject = subject;
var body = body;
// Base URL
var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());
/* Specify PDF export parameters
From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
*/
var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
+ '&size=letter' // paper size legal / letter / A4
+ '&portrait=false' // orientation, false for landscape
+ '&fitw=true&source=labnol' // fit to page width, false for actual size
+ '&sheetnames=false&printtitle=false' // hide optional headers and footers
+ '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
+ '&fzr=false' // do not repeat row headers (frozen rows) on each page
+ '&gid='; // the sheet's Id
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url + url_ext + sheet.getSheetId(), {
headers : {
'Authorization' : 'Bearer ' + token
}
}).getBlob().setName(sheet.getName() + ".pdf");
// Uncomment the line below to save the PDF to the root of your drive.
// var newFile = DriveApp.createFile(response).setName(sheet.getName() + ".pdf")
if (MailApp.getRemainingDailyQuota() > 0)
GmailApp.sendEmail(email, subject, body, {
htmlBody : body,
attachments : [response]
});
}
这篇关于Google电子表格脚本仅将活动工作表导出为PDF的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文