将Google表格转换为单独的PDF并通过电子邮件发送给他们 [英] Convert Google Sheets into Individual PDFs and Email Them
问题描述
我有一个电子表格，该电子表格中始终有一个名为时间表"的工作表，填写该表的人可以根据需要创建任意数量的其他笔记工作表.
I have a spreadsheet that always has sheet named "Timesheet" and the person filling it out can create as many additional notes sheets as they need.
除了时间表"工作表之外，我还需要将所有工作表转换为单独的PDF并通过电子邮件发送到地址.
I need to have all sheets besides the "Timesheet" sheet converted to individual PDFs and emailed to an address.
我在创建一个脚本时遇到了麻烦，该脚本无法将它们汇总为一个PDF.因此，它还在PDF中包括时间表"表.
I am having trouble creating a script that doesn't roll them into a single PDF. Because of this it also includes the "Timesheet" Sheet in the PDF.
我需要将每个便签纸转换为单个PDF，然后将所有PDF作为单独的附件通过电子邮件发送出去.填写该文件的人还可以将便签重命名为他们想要的任何名称，因此我无法按名称获取该便签纸.
I need each of the notes sheets to be converted to a single PDF, then all PDFs emailed as separate attachments.The person filling it out can also rename the notes to whatever they want so I cannot get the sheet by name.
我有一些代码可以合并所有工作表，并将其重命名以用于其他目的，如果有帮助，我将在下面提供.
I have some code that combines all sheets and renames it that I use for a different purpose, I will include it below if it is of any help.
在此先感谢您的帮助
function emailGoogleSpreadsheetAsPDF() {
// Send the PDF of the spreadsheet to this email address
var email = "email@gmail.com";
// Get the currently active spreadsheet URL (link)
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Custom
var name = ss.getRange("Timesheet!J6:K6").getValue();
var agency = ss.getRange("Timesheet!B4:C4").getValue();
// Date
var today = new Date();
var dd = today.getDate();
var mm = today.getMonth() + 1; //January is 0!
var yyyy = today.getFullYear();
if (dd < 10) {
dd = '0' + dd;
}
if (mm < 10) {
mm = '0' + mm;
}
today = mm + '/' + dd + '/' + yyyy;
// Subject of email message
var subject = name + " has Submitted Their Timesheet and Notes";
// Email Body can be HTML too
var body = "This was submitted on " + today;
var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");
blob.setName(name + "_" + agency + "_" + today + "_" + "timesheet_notes.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]
});
}
推荐答案
将电子表格的表格作为单独的PDF发送给电子邮件
我不能仅仅通过创建blob并将它们放入数组来做到这一点.因此，我为每个页面创建了单独的PDF文件，然后在最后将它们丢弃.我还添加和排除数组，以便您可以从整个过程中排除某些文件，只发送您想要的工作表.
I could not do this by just creating the blobs and putting them in an array. So I created separate PDF files for each page and then trashed them at the end. I also added and exclusion array so that you could exlude certain files from the entire process and only send the sheets that you wish.
当前版本包括对话框提示，这些提示会在程序创建文件并发送电子邮件时跟踪程序的进度.因此，它与您的程序不完全一样.
The current version includes dialog prompts that follow the progress of the program as it creates files and sends the email. So it's not exactly like your program.
这些行也是您的程序，我很难理解，因为您有2个单元格的数组，但是您使用的是getValue()而不是getValues();
Also these lines is your program are difficult for me to understand because you have a 2 cell array but you using getValue() instead of getValues();
var name = ss.getRange("Timesheet!J6:K6").getValue();
var agency = ss.getRange("Timesheet!B4:C4").getValue();
这是我的代码:
function savePDFFiles1() {
var ss=SpreadsheetApp.getActive();
var exclA=['Summary','Images','Globals','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21'];
var fA=[];
var html="";
var shts=ss.getSheets();
var pdfFldr=DriveApp.getFolderById('FolderId');//folder where I stored the files temporarily
for(var i=0;i<shts.length;i++) {
var sh=shts[i];
var name=sh.getName();
if(exclA.indexOf(name)==-1) {
sh.showSheet();
for(var j=0;j<shts.length;j++) {
if(shts[j].getName()!=name) {
shts[j].hideSheet();
}
}
SpreadsheetApp.flush();//I dont know if this is required
var file=pdfFldr.createFile(ss.getBlob().getAs('application/pdf').setName(Utilities.formatString('%s_%s.pdf',ss.getName(),name)));
html+=Utilities.formatString('<br />File: %s Created',file.getName());
var userInterface=HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Files Created')
fA.push(file);
}
}
GmailApp.sendEmail('recipient email', 'Plot Reports', 'Plot Reports Attached', {attachments:fA})
html+='<br />Email Sent';
var userInterface=HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Files Created');
for(var i=0;i<fA.length;i++ ) {
fA[i].setTrashed(true);
}
html+='<br />Files Trashed and Process Complete';
html+='<script>window.onload=function(){google.script.host.close();}</script>';
var userInterface=HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Files Created');
}
在我的示例中，我使用的工作表中有一些用数字命名的工作表，并且通常将某些工作表用作哈希表，因此我通常一直将其隐藏.
In my example, I was using a sheet that had some sheets named with numbers and I typically use certain sheets as hash tables so I typically keep them hidden all of the time.
我将回过头来看看现在如何更新脚本.
I'll go back and take a look at updating your script now.
好的，这就是我认为您的脚本应该是这样的:
Okay so this is what I think your script would look like:
function emailGoogleSpreadsheetAsPDF() {
var email="email@gmail.com";
var ss=SpreadsheetApp.getActive();
var name=ss.getRange("Timesheet!J6").getValue();//trimmed the range down to match the getValue();
var agency=ss.getRange("Timesheet!B4").getValue();//same here
var fldr=DriveApp.getFolderById('folderId');
var fA=[];
var today=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy");
var subject=Utilities.formatString('%s has Submitted Their Timesheet and Notes',name);
var body=Utilities.formatString('This was submitted on %s',today);
var shts=ss.getSheets();
for(var i=0;i<shts.length;i++) {
var sh=shts[i];
var name=sh.getName();
sh.showSheet();
for(var j=0;j<shts.length;j++) {
if(shts[j].getName()!=name) {
shts[j].hideSheet();
}
}
SpreadsheetApp.flush();//this may not be necessary...not sure
var file=fldr.createFile(ss.getBlob().getAs('application/pdf')).setName(Utilities.formatString('%s_%s_%s_timesheet_notes.pdf', name,agency,today));
fA.push(file);
}
GmailApp.sendEmail(email,subject,body, {attachments:fA});
for(var i=0;i<fA.length;i++) {
fA[i].setTrashed(true);
}
}
注意:我尚未测试最后一个，因此可能需要进行一些调试，但与其他示例基本相同.经过测试的.
Note: I have not tested this last one so a little debugging may be required but it's basically the same idea as the other example. Which was tested.
还有另一种使用UrlFetchApp进行此操作的方法，在此处进行了讨论就我个人而言，我只想创建文件并在最后将它们丢弃.
There's another way to do this using UrlFetchApp that's discussed here Personally, I'd rather just create the files and trash them at the end.
有请求的更改:
function emailGoogleSpreadsheetAsPDF() {
var email="email@gmail.com";
var exclA=['TimeSheet'];//and others
var ss=SpreadsheetApp.getActive();
var name=ss.getRange("Timesheet!J6").getValue();//trimmed the range down to match the getValue();
var agency=ss.getRange("Timesheet!B4").getValue();//same here
var fldr=DriveApp.getFolderById('folderId');
var fA=[];
var today=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy");
var subject=Utilities.formatString('%s has Submitted Their Timesheet and Notes',name);
var body=Utilities.formatString('This was submitted on %s',today);
var shts=ss.getSheets();
for(var i=0;i<shts.length;i++) {
var sh=shts[i];
var name=sh.getName();
if(exclA.indexOf(name)==-1) {
sh.showSheet();
for(var j=0;j<shts.length;j++) {
if(shts[j].getName()!=name) {
shts[j].hideSheet();
}
}
SpreadsheetApp.flush();//this may not be necessary...not sure
var file=fldr.createFile(ss.getBlob().getAs('application/pdf')).setName(Utilities.formatString('%s_%s_%s_timesheet_notes.pdf', name,agency,today));
fA.push(file);
}
}
GmailApp.sendEmail(email,subject,body, {attachments:fA});
for(var i=0;i<fA.length;i++) {
fA[i].setTrashed(true);
}
for(var i=0;i<shts.length;i++) {
if(exclA.indexOf(shts[i].getName())==-1) {
shts[i].showSheet();
}
}
}
添加整个电子表格的PDF(排除的隐藏工作表除外):
Adding a PDF of the Entire Spreadsheet(except for excluded hidden sheets):
function emailGoogleSpreadsheetAsPDF() {
var email="email@gmail.com";
var exclA=['TimeSheet'];//and others
var ss=SpreadsheetApp.getActive();
var name=ss.getRange("Timesheet!J6").getValue();//trimmed the range down to match the getValue();
var agency=ss.getRange("Timesheet!B4").getValue();//same here
var fldr=DriveApp.getFolderById('folderId');
var fA=[];
var today=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy");
var subject=Utilities.formatString('%s has Submitted Their Timesheet and Notes',name);
var body=Utilities.formatString('This was submitted on %s',today);
var shts=ss.getSheets();
for(var i=0;i<shts.length;i++) {
var sh=shts[i];
var name=sh.getName();
if(exclA.indexOf(name)==-1) {
sh.showSheet();
for(var j=0;j<shts.length;j++) {
if(shts[j].getName()!=name) {
shts[j].hideSheet();
}
}
SpreadsheetApp.flush();//this may not be necessary...not sure
var file=fldr.createFile(ss.getBlob().getAs('application/pdf')).setName(Utilities.formatString('%s_%s_%s_timesheet_notes.pdf', name,agency,today));
fA.push(file);
}
}
for(var i=0;i<shts.length;i++) {
if(exclA.indexOf(shts[i].getName())==-1) {
shts[i].showSheet();
}
}
SpreadsheetApp.flush();//this may not be necessary...not sure
var file=fldr.createFile(ss.getBlob().getAs('application/pdf')).setName(Utilities.formatString('%s.pdf',ss.getName()));
fA.push(file)
GmailApp.sendEmail(email,subject,body, {attachments:fA});
for(var i=0;i<fA.length;i++) {
fA[i].setTrashed(true);
}
}
这篇关于将Google表格转换为单独的PDF并通过电子邮件发送给他们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
