英文:
Saving a specific sheet from the workbook as a PDF
问题
我在我的工作簿中有3个工作表,我想将特定工作表保存为PDF到我的Google Drive中的特定文件夹。我有下面的代码。但是,它将整个工作簿保存为PDF而不是特定工作表。
function checkSheet() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Tracking");
var fldr = DriveApp.getFolderById("文件夹ID");
if(sh.getRange("C6").getValue() == "Yes") {
var theBlob = ss.getBlob().getAs('application/pdf').setName("mypdf");
fldr.createFile(theBlob);
}
}
"Tracking" - 是我想要保存为PDF的工作表
"文件夹ID" - 是我想要保存PDF文件的文件夹URL中的ID
我尝试寻找不同的getBlob语法,但无法将特定工作表保存为PDF。以下是我参考的链接。
https://stackoverflow.com/questions/38335143/export-single-sheet-to-pdf-in-apps-script
英文:
I have 3 sheets in my workbook and I want to save a specific sheet as a PDF to a specific folder in my Google Drive. I have this code below. However, it is saving the entire workbook as PDF and not that specific sheet.
function checkSheet() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Tracking");
var fldr = DriveApp.getFolderById("folder id");
if(sh.getRange("C6").getValue() == "Yes") {
var theBlob = ss.getBlob().getAs('application/pdf').setName("mypdf");
fldr.createFile(theBlob);
}
}
Tracking - is the sheet that I want to save as PDF
folder id - is the id taken from the url of the folder where I want to save the PDF file
I tried looking for different syntax for getBlob and I am not able to get that specific sheet saved as a PDF. Here are is a link that I referenced.
https://stackoverflow.com/questions/38335143/export-single-sheet-to-pdf-in-apps-script
答案1
得分: 0
我认为你提供的帖子链接会很有用。但是,从你的问题中,我无法理解你测试的脚本。所以,在这种情况下,我想介绍一下使用你提供的帖子链接修改过的脚本。
修改后的脚本:
function checkSheet() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Tracking");
var fldr = DriveApp.getFolderById("文件夹ID");
if (sh.getRange("C6").getValue() == "Yes") {
const sheetName = "Sheet1"; // 请设置要导出为PDF格式的工作表名称。
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() !== sheetName) {
sheets[i].hideSheet();
}
}
SpreadsheetApp.flush(); // 可能不需要使用这个。
var theBlob = ss.getBlob().setName("mypdf");
fldr.createFile(theBlob);
for (var i = 0; i < sheets.length; i++) {
sheets[i].showSheet();
}
}
}
-
运行此脚本时,只有 "Sheet1" 包含在导出的PDF文件中。
-
顺便说一下,在这种情况下,当从电子表格中检索到Blob时,电子表格会自动转换为PDF格式。因此,在这种情况下,
var theBlob = ss.getBlob().getAs('application/pdf').setName("mypdf");
可以替换为var theBlob = ss.getBlob().setName("mypdf");
。
注意:
- 作为另一种方法,在这种情况下,当用于将电子表格导出为PDF格式的端点时,修改后的脚本如下。
function checkSheet() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Tracking");
var fldr = DriveApp.getFolderById("文件夹ID");
if (sh.getRange("C6").getValue() == "Yes") {
const sheetName = "Sheet1"; // 请设置要导出为PDF格式的工作表名称。
const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&gid=${ss.getSheetByName(sheetName).getSheetId()}`;
const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
fldr.createFile(blob.setName("mypdf"));
}
}
英文:
I thought that your referenced thread will be useful. But, from your question, I couldn't understand your tested script. So, in this case, I would like to introduce the modified script using your referenced thread.
Modified script:
function checkSheet() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Tracking");
var fldr = DriveApp.getFolderById("folder id");
if (sh.getRange("C6").getValue() == "Yes") {
const sheetName = "Sheet1"; // Please set the sheet name you want to export as PDF format.
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() !== sheetName) {
sheets[i].hideSheet();
}
}
SpreadsheetApp.flush(); // This might not be required to be used.
var theBlob = ss.getBlob().setName("mypdf");
fldr.createFile(theBlob);
for (var i = 0; i < sheets.length; i++) {
sheets[i].showSheet();
}
}
}
-
When this script is run, only "Sheet1" is included in the exported PDF file.
-
By the way, in this case, when the blob is retrieved from Spreadsheet, the Spreadsheet is automatically converted to PDF format. So, in this case,
var theBlob = ss.getBlob().getAs('application/pdf').setName("mypdf");
can be replaced withvar theBlob = ss.getBlob().setName("mypdf");
.
Note:
-
As another approach, in this case, when the endpoint for exporting the Spreadsheet as PDF format is used, the modified script is as follows.
function checkSheet() { const ss = SpreadsheetApp.getActive(); const sh = ss.getSheetByName("Tracking"); var fldr = DriveApp.getFolderById("folder id"); if (sh.getRange("C6").getValue() == "Yes") { const sheetName = "Sheet1"; // Please set the sheet name you want to export as PDF format. const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&gid=${ss.getSheetByName(sheetName).getSheetId()}`; const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob(); fldr.createFile(blob.setName("mypdf")); } }
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论