在Apps脚本中将单张导出为PDF [英] Export Single Sheet to PDF in Apps Script

问题描述

电子表格中有很多工作表。我有两张纸，每次我都想输出。 Sheet One是一张带有信息的表格。第二张是我需要提供给客户的纸张，它需要来自Sheet One的参考。

目前，我创建了一个新的临时电子表格，将Sheet One复制到新的电子表格中，然后将Sheet Two复制到临时电子表格中。之后，我将临时电子表格转换为pdf。然后，我删除临时电子表格，并将PDF保存到Google云端硬盘中的文件夹中。

创建的PDF包含两页两页。我只需要第二张。如果我仅转印第二张纸张，由于第一张纸张不存在，该张纸张会留下许多#REF错误。有没有什么办法可以导出只有Sheet 2而没有#REF错误？

以下是我的代码：

  //创建一个临时电子表格，将两个文件复制到它上面
 var newSpreadsheet = SpreadsheetApp.create（nameOfSheet）; 
 var d = ss.getSheetByName（'Data'）。copyTo（newSpreadsheet）; //包含所有信息的表单
 d.setName（'Data'）; 
 
 sheetToCopy.copyTo（newSpreadsheet）; //使用数据表引用的工作表
 newSpreadsheet.deleteSheet（newSpreadsheet.getSheetByName（Sheet1））; //删除新电子表格的原始表格
 var blobOfNewSpreadsheet = newSpreadsheet.getBlob（）; //创建pdf 
 folder.createFile（blobOfNewSpreadsheet）; //添加到文件夹
 
 
 //删除临时电子表格2 
 var deleteSpreadsheet = DriveApp.getFileById（newSpreadsheet.getId（））; 
 deleteSpreadsheet.setTrashed（true）; 
  

解决方案

getBlob。因此，您可以在导出之前临时隐藏不需要的纸张。

  function export（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = ss.getSheetByName（'Unwanted Sheet'）; 
 sheet.hideSheet（）; 
 DriveApp.createFile（ss.getBlob（））; 
 sheet.showSheet（）; 
 
 
 
 $ b $ p 
 $ b 
以上只隐藏了一张表，这在你的问题的上下文中是足够的。这是一个隐藏除一个之外的所有内容的版本。 
  function export（sheetName）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheets = ss.getSheets; 
 for（var i = 0; i< sheets.length; i ++）{
 if（sheets [i] .getSheetName（）！= sheetName）{
 sheet [i] .hideSheet （）; 
} 
} 
 DriveApp.createFile（ss.getBlob（））; 
 for（var i = 0; i< sheets.length; i ++）{
 sheet [i] .showSheet（）; 
} 
} 
  
 
I have many sheets in a Spreadsheet. I have 2 sheets that I would like to export every time. Sheet One is a sheet with the information. Sheet Two is the sheet I need to give to customers and it takes references from Sheet One. 

Currently, I create a new temporary spreadsheet, copy Sheet One to the new spreadsheet, then copy Sheet Two to the temporary spreadsheet. After, I convert the temporary spreadsheet to a pdf. Then I delete the temporary spreadsheet and save the pdf into a folder in Google Drive.

The PDF created contains 2 pages of both sheets. I only need Sheet Two. If I only transfer Sheet Two, the sheet is left with many #REF errors since Sheet One isn't there. Is there any way to export only Sheet Two without have the #REF errors?

Here is my code below:
//create a temporary spreadsheet, copy both files onto it
var newSpreadsheet = SpreadsheetApp.create(nameOfSheet);
var d = ss.getSheetByName('Data').copyTo(newSpreadsheet); //the sheet     with all the information
d.setName('Data');

sheetToCopy.copyTo(newSpreadsheet); //the sheet that uses references from the data sheet
newSpreadsheet.deleteSheet(newSpreadsheet.getSheetByName("Sheet1")); //delete the original sheet of the new spreadsheet
var blobOfNewSpreadsheet = newSpreadsheet.getBlob(); //create pdf
folder.createFile(blobOfNewSpreadsheet); //add to folder


//delete the temporary spreadsheet2
var deleteSpreadsheet = DriveApp.getFileById(newSpreadsheet.getId());
deleteSpreadsheet.setTrashed(true);

解决方案
Hidden sheets are not included when a spreadsheet is exported via getBlob. So you can temporarily hide any unwanted sheets prior to exporting. 
function export() {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Unwanted Sheet');
  sheet.hideSheet();
  DriveApp.createFile(ss.getBlob());
  sheet.showSheet();
}
The above only hides one sheet, which is enough in the context of your question. Here is a version that hides everything but one. 
function export(sheetName) {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets;
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName) {
      sheet[i].hideSheet();
    }
  }
  DriveApp.createFile(ss.getBlob());
  for (var i = 0; i < sheets.length; i++) {
    sheet[i].showSheet();
  }
}


                        
这篇关于在Apps脚本中将单张导出为PDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！