Google Apps脚本:在电子邮件中发送PDF而不是.zip文件 [英] Google Apps Script: Send PDF instead of .zip file in email
问题描述
我有这段代码可以从Google Spreadsheet生成PDF文件,并将其作为电子邮件附件发送.问题是它使用 const zipBlob = Utilities.zip(blobs).setName('$ {ss.getName()}.zip');
.
I have this code that generates a PDF file from a Google Spreadsheet and sends it as an email attachment. The issue is that it zips the file with const zipBlob = Utilities.zip(blobs).setName('${ss.getName()}.zip');
.
我想对其进行更改,以使附件为PDF而不是.zip文件.
I want to change it so that the attached file is a PDF and not a .zip file.
/* Send Spreadsheet in an email as PDF, automatically */
function emailSpreadsheetAsPDF() {
// Send the PDF of the spreadsheet to this email address
const email = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Árajánlat - EKOL Hungary').getRange('E3').getValue().toString();
// Get the currently active spreadsheet URL (link)
// Or use SpreadsheetApp.openByUrl("<>");
const ss = SpreadsheetApp.getActiveSpreadsheet();
// Subject of email message
const subject = `EKOL Hungary - ${ss.getName()}`;
// Email Body can be HTML too with your logo image - see ctrlq.org/html-mail
const body = "Ide majd kell valami szöveg";
// Base URL
const url = 'https://docs.google.com/spreadsheets/d/SS_ID/export?'.replace('SS_ID', ss.getId());
const exportOptions =
'exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
'&size=A4' + // paper size legal / letter / A4
'&portrait=true' + // 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
const token = ScriptApp.getOAuthToken();
const sheets = ss.getSheets();
// make an empty array to hold your fetched blobs
const blobs = [];
for (let i = 0; i < sheets.length; i += 1) {
// Convert individual worksheets to PDF
const response = UrlFetchApp.fetch(url + exportOptions + 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
const zipBlob = Utilities.zip(blobs).setName(`${ss.getName()}.zip`);
// 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]
});
}
我也尝试过这种方法,但是它发送的PDF损坏了.
I have also tried it this way, but it sends a corrupted PDF.
function emailSpreadsheetAsPDF() {
const sheetToPrint = "Árajánlat - EKOL Hungary"; // name of the sheet to print
const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1XTJF_-qYFvE4IVkA77YsOg3yfO_PX46z8z0_AtYg_Go/edit#gid=0");
const ssID = ss.getId();
const email = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Árajánlat - EKOL Hungary').getRange('E3').getValue().toString();
const subject = `EKOL Hungary - ${ss.getName()}`;
const body = "Kicsit szépítettem rajta. Viszont, ide majd kéne valami szöveg. Mi legyen?";
const shID = ss.getSheetByName(sheetToPrint).getSheetId();
const url = 'https://docs.google.com/spreadsheets/d/SS_ID/export?'.replace('SS_ID', ss.getId());
const exportOptions =
'&size=A4'+
'&portrait=true'+
'&fitw=true'+
'&sheetnames=false&printtitle=false'+
'&pagenumbers=false&gridlines=false'+
'&fzr=false'+
'&gid='+shID;
var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments: [{
fileName: `EKOL Hungary - Árajánlat` + ".pdf",
content: response.getBytes(),
mimeType: "application/pdf"
}]
});
}
我真的很感谢您提供的任何帮助,因为我是编码的新手.
I would really appreciate any help you could give me as I am new to coding.
推荐答案
经过几次尝试,下面的代码已成功解决问题.
After several tries, the code below has done the trick.
// Generate a PDF file from a Google spreadsheet and send it to a specified email address
function emailSpreadsheetAsPDF() {
const sheetToPrint = "Árajánlat - EKOL Hungary"; // name of the sheet to print
const ss = SpreadsheetApp.getActiveSpreadsheet(); // the sheets to use
const email = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Árajánlat - EKOL Hungary').getRange('E3').getValue().toString(); // grab the email address from the specified cell
const subject = `EKOL Hungary - ${ss.getName()}`; // the subject of the email
const body = "Kicsit szépítettem rajta. Viszont, ide majd kéne valami szöveg. Mi legyen?"; // body of the email
const shID = ss.getSheetByName(sheetToPrint).getSheetId(); // the ID of the sheet
const url = 'https://docs.google.com/spreadsheets/d/SS_ID/export?'.replace('SS_ID', ss.getId()); // url of the spreadsheet
const exportOptions =
'exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
'&size=A4'+ // size of the PDF (legal / A4 / letter)
'&portrait=true'+ // orientation of the PDF (false for landscape)
'&fitw=true'+ // 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='+shID; // the sheet's Id
var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
// generate the PDF file
var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
// send the email to the specified address with the specified body text and attached PDF file
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments: [{
fileName: `EKOL Hungary - ${ss.getName()}` + ".pdf",
content: response.getBytes(),
mimeType: "application/pdf"
}]
});
}
这篇关于Google Apps脚本:在电子邮件中发送PDF而不是.zip文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!