将工作簿中的特定工作表保存为PDF。

huangapple go评论62阅读模式
英文:

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&amp;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(&quot;Tracking&quot;);
  var fldr = DriveApp.getFolderById(&quot;folder id&quot;);
  if (sh.getRange(&quot;C6&quot;).getValue() == &quot;Yes&quot;) {

    const sheetName = &quot;Sheet1&quot;; // Please set the sheet name you want to export as PDF format.
    var sheets = ss.getSheets();
    for (var i = 0; i &lt; 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(&quot;mypdf&quot;);
    fldr.createFile(theBlob);
    for (var i = 0; i &lt; 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(&#39;application/pdf&#39;).setName(&quot;mypdf&quot;); can be replaced with var theBlob = ss.getBlob().setName(&quot;mypdf&quot;);.

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(&quot;Tracking&quot;);
      var fldr = DriveApp.getFolderById(&quot;folder id&quot;);
      if (sh.getRange(&quot;C6&quot;).getValue() == &quot;Yes&quot;) {
    
        const sheetName = &quot;Sheet1&quot;; // 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&amp;gid=${ss.getSheetByName(sheetName).getSheetId()}`;
        const blob = UrlFetchApp.fetch(url, { headers: { authorization: &quot;Bearer &quot; + ScriptApp.getOAuthToken() } }).getBlob();
        fldr.createFile(blob.setName(&quot;mypdf&quot;));
    
      }
    }
    

huangapple
  • 本文由 发表于 2023年2月6日 07:26:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/75356217.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定