英文:
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")); } } 
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论