使用 Google Apps Script 将所有工作表转换为 PDF [英] Convert all sheets to PDF with Google Apps Script
本文介绍了使用 Google Apps Script 将所有工作表转换为 PDF的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将包含多张工作表的 Google 电子表格转换为 PDF 文件.下面的脚本可以工作,但它只能创建包含电子表格最后一页的 PDF.
I'm trying to convert a Google spreadsheet with multiple sheets to a PDF file. The script below works, but it only creates a PDF with the last page of the spreadsheet.
function savePDFs() {
SpreadsheetApp.flush();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var url = ss.getUrl();
//remove the trailing 'edit' from the url
url = url.replace(/edit$/,'');
//additional parameters for exporting the sheet as a pdf
var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
//below parameters are optional...
'&size=letter' + //paper size
'&portrait=false' + //orientation, false for landscape
'&fitw=true' + //fit to width, false for actual size
'&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
'&gridlines=false' + //false = hide gridlines
'&fzr=false' + //do not repeat row headers (frozen rows) on each page
'&gid='; //leave ID empty for now, this will be populated in the FOR loop
var token = ScriptApp.getOAuthToken();
//make an empty array to hold your fetched blobs
var blobs = [];
//.fetch is called for each sheet, the response is stored in var blobs[]
for(var i = 0; i < sheets.length; i++) {
var sheetname = sheets[i].getName();
//if the sheet is one that you don't want to process,
//continue' tells the for loop to skip this iteration of the loop
if(sheetname == "Team Member Numbers")
continue;
//grab the blob for the sheet
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.push(response.getBlob().setName(sheets[i].getName() + '.pdf'));
var array_blob = response.getBlob().setName(sheets[i].getName() + '.pdf');
}
//from here you should be able to use and manipulate the blob to send and
//email or create a file per usual.
// send email
var subject = "Enter Subject"
var message = "See attached PDF"
MailApp.sendEmail("email addy here", subject, message,{attachments:[array_blob]});
}
推荐答案
我稍微调整了@Mogsdad 代码,将整个电子表格打印为一个 PDF.关键是调整导出参数.基本替换
I've tweaked @Mogsdad code slightly to print the entire spreadsheet as one PDF. The key is tweaking the export parameter. Basically replace
'&gid=' + sheet.getSheetId() //the sheet's Id
与
(optSheetId ? ('&gid=' + sheet.getSheetId()) : ('&id=' + ss.getId())) // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
所以上面的代码减去循环看起来像:
So the code above minus the looping looks like:
function savePDFs( optSSId, optSheetId ) {
// If a sheet ID was provided, open that sheet, otherwise assume script is
// sheet-bound, and open the active spreadsheet.
var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();
// Get folder containing spreadsheet, for later export
var parents = DriveApp.getFileById(ss.getId()).getParents();
if (parents.hasNext()) {
var folder = parents.next();
}
else {
folder = DriveApp.getRootFolder();
}
//additional parameters for exporting the sheet as a pdf
var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf
// Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
+ (optSheetId ? ('&gid=' + sheet.getSheetId()) : ('&id=' + ss.getId()))
// following parameters are optional...
+ '&size=letter' // paper size
+ '&portrait=true' // orientation, false for landscape
+ '&fitw=true' // fit to width, false for actual size
+ '&sheetnames=false&printtitle=false&pagenumbers=false' //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("https://docs.google.com/spreadsheets/" + url_ext, options);
var blob = response.getBlob().setName(ss.getName() + '.pdf');
//from here you should be able to use and manipulate the blob to send and email or create a file per usual.
//In this example, I save the pdf to drive
folder.createFile(blob);
}
顺便说一句,谢谢 - 我一直在寻找解决方案很长时间!
Btw, thank you -- I've been looking for a solution for this for a long time!
这篇关于使用 Google Apps Script 将所有工作表转换为 PDF的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文