通过电子邮件以PDF格式发送Google表格 [英] Emailing Google Sheet as PDF
本文介绍了通过电子邮件以PDF格式发送Google表格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有这个脚本，可以通过电子邮件向我发送Google电子表格的PDF。我只希望它给我发送第一个制表符，如果可能的话，以电子邮件的形式发送给我一个zip文件。
I have this script that emails me a PDF of the Google Spreadsheet. I only want it to email me the first 'Tab' and if possible as a single PDF vice a zip file.
很想知道是否有人可以提供帮助。另外，其中一个标签也被隐藏了，所以我不知道这是否有影响。他是指向表的链接。
Wondered if anyone could help. Also one of the 'Tabs' are hidden, so I don't know if that has an impact. He is a link to the sample sheet.
代码如下所示：
/* Send Spreadsheet in an email as PDF, automatically */
function emailSpreadsheetAsPDF() {
// Send the PDF of the spreadsheet to this email address
var email = "xxxxx@gmail.com";
// Get the currently active spreadsheet URL (link)
// Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>");
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Subject of email message
var subject = "Sample Sheet " + ss.getName();
// Email Body can be HTML too with your logo image - see ctrlq.org/html-mail
var body = "Install the <a href='http://www.labnol.org/email-sheet'>Email Spreadsheet add-on</a> for one-click conversion.";
// 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 sheets = ss.getSheets();
//make an empty array to hold your fetched blobs
var blobs = [];
for (var i=0; i<sheets.length; i++) {
// Convert individual worksheets to PDF
var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
headers: {
'Authorization': 'Bearer ' + token
}
});
//convert the response to a blob and store in our array
blobs[i] = response.getBlob().setName(sheets[i].getName() + '.pdf');
}
//create new blob that is a zip file containing our blob array
var zipBlob = Utilities.zip(blobs).setName(ss.getName() + '.zip');
//optional: save the file to the root folder of Google Drive
DriveApp.createFile(zipBlob);
// Define the scope
Logger.log("Storage Space used: " + DriveApp.getStorageUsed());
// If allowed to send emails, send the email with the PDF attachment
if (MailApp.getRemainingDailyQuota() > 0)
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments:[zipBlob]
});
}
//**************************************************************************
// Revised Code
//**************************************************************************
/* Send Spreadsheet in an email as PDF, automatically */
function emailSpreadsheetAsPDF() {
// Send the PDF of the spreadsheet to this email address
var email = "xxxxx@gmail.com";
// Get the currently active spreadsheet URL (link)
// Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>");
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Subject of email message
var subject = "PDF generated from spreadsheet " + ss.getName();
// Email Body can be HTML too with your logo image - see ctrlq.org/html-mail
var body = "Install the <a href='http://www.labnol.org/email-sheet'>Email Spreadsheet add-on</a> for one-click conversion.";
// 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 sheets = ss.getSheets();
//make an empty array to hold your fetched blobs
var blobs = [];
//for (var i=0; i<sheets.length; i++) {
for (var i=0; i<1; i++) {
// Convert individual worksheets to PDF
var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
headers: {
'Authorization': 'Bearer ' + token
}
});
//convert the response to a blob and store in our array
// blobs[i] = response.getBlob().setName(sheets[i].getName() + '.pdf');
}
//create new blob that is a zip file containing our blob array
// var zipBlob = Utilities.zip(blobs).setName(ss.getName() + '.zip');
//optional: save the file to the root folder of Google Drive
//DriveApp.createFile(zipBlob);
// Define the scope
Logger.log("Storage Space used: " + DriveApp.getStorageUsed());
// If allowed to send emails, send the email with the PDF attachment
if (MailApp.getRemainingDailyQuota() > 0)
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
//attachments:[zipBlob]
});
}
亲切问候
Al
Kind Regards Al
推荐答案
在您的代码中，您将两个工作表都以blob的形式进行压缩并将其压缩？？
In your code, you are getting both sheets as blobs and zipping them??
最简单的解决方法它是将 for（var i = 0; i< sheets.length; i ++）{更改为 for（var i = 0; i< 1 ; i ++）{，因此它只会获取第一个标签并进行压缩。
The easiest fix it is to change for (var i=0; i<sheets.length; i++) { to for (var i=0; i<1; i++) { so it only gets the first tab and zips it.
更新基于评论。
function emailSpreadsheetAsPDF() {
var email = ""; // Enter the required email address here
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("**********"); // Enter the name of the sheet here
var subject = "PDF generated from spreadsheet " + ss.getName();
var body = "\n Attached is a PDF copy of the sheet " + sheet.getName() + " in the " + ss.getName() + " spreadsheet.";
// 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]
});
}
这篇关于通过电子邮件以PDF格式发送Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
