pdf - Google Sheet Script (Email to PDF) 突然发送损坏的 PDF
问题描述
我有一个谷歌表格脚本,它将页面作为 PDF 发送到电子邮件,直到昨天它一直运行良好。突然它开始发送无法打开的损坏的 PDF。
脚本运行得很好,如果只是无法打开 PDF 文件,因为它显示“无法显示 - 格式无效”。
关于为什么它可能停止工作的任何想法?
function sendSheetToPdfwithA1MailAdress(){ // this is the function to call
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheets()[0]; // it will send sheet 0 which is the first sheet in the spreadsheet.
// if you change the number, change it also in the parameters below
var shName = sh.getName()
// This function uses a cell in the spreadsheet that names the file that is being saved as getfilename(). using this function will pull from a certain Cell (G4 in this case)
function getFilename() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName('N1944E'); // Edit the sheet name as necessary
var cell = sheet.getRange('C8'); //Cell to pull file name from.
var filename = cell.getValue();
return filename;
}
sendSpreadsheetToPdf(0, shName, sh.getRange('C6').getValue(),"Air Attack Daily Fire Sheet " + getFilename() );
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheetId = spreadsheet.getId()
var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;
var url_base = spreadsheet.getUrl().replace(/edit$/,'');
var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf
+ (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))
// following parameters are optional...
+ '&size=A4' // paper size
+ '&portrait=true' // orientation, false for landscape
+ '&fitw=true' // fit to width, false for actual size
+ '&sheetnames=true&printtitle=false&pagenumbers=true' //hide optional headers and footers
+ '&gridlines=false' // hide gridlines
+ '&fzr=false'; // do not repeat row headers (frozen rows) on each page
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);
MailApp.sendEmail(
Session.getActiveUser().getEmail(),
" "+subject+" (" + pdfName +")",
"html content only",
mailOptions);
}
}
解决方案
我有完全相同的问题,但我只是想通了。问题在这里:
var url_base = ss.getUrl().replace(/edit$/,'') + "export?";
getUrl() 似乎返回的 url 版本与以前不同。它现在在 url 上附加以下内容:“ouid=###########&urlBuilderDomain=YOURDOMAIN” 使用记录器自行检查。
这导致pdf导出出现问题。因此,我通过将该行替换为以下内容来构建自己的 url 地址:
var url_base = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/" + "export?";
它现在似乎工作了!这是生成我的 blob 的完整代码:
function generatePDF(pdfName, sheet, portrait){
var token = ScriptApp.getOAuthToken();
var params = {
headers: {
'Authorization': 'Bearer ' + token,
},
'muteHttpExceptions' : true
};
var sheetId = sheet.getSheetId();
var ss = sheet.getParent();
// var url_base = ss.getUrl().replace(/edit$/,'') + "export?";
var url_base = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/" + "export?";
var url_ext = 'exportFormat=pdf' //export as pdf
+ '&format=pdf' //export as pdf
+ '&gid=' + sheetId
+ '&size=letter' // paper size
+ '&portrait=' + portrait // orientation, false for landscape
+ '&fitw=true' // fit to width, false for actual size
+ '&sheetnames=false' //optional headers and footers
+ '&printtitle=false' //optional headers and footers
+ '&pagenumbers=true' //page numbers
+ '&gridlines=true' // gridlines
+ '&fzr=true' // repeat row headers (frozen rows) on each page
var response = UrlFetchApp.fetch(url_base + url_ext, params);
var blob = response.getBlob().setName(pdfName + ".pdf");
return blob;
}
推荐阅读
- java - 反应组件隐藏在网站页面后面
- android - java.lang.RuntimeException:无法创建类 ViewModel [Kotlin] 的实例
- angular - 如何在 Angular 10 中使用 HTML 锚链接#id inside dialog (MatDialog)
- python - 生成器函数中返回的原因是什么?
- python - 如何停止一个类在python中将属性设置为其文字名称?
- oracle - 创建显式游标时未声明绑定变量 *
- c++ - 如何在 Visual Studio 上设置 .h 路径?
- python - 如何使用 Django 捕获选中复选框中的值
- android - 如何以编程方式获取应用程序的估计功耗?
- python - 如何根据熊猫中的排名分配最小值?