在 Apps Script 中将单张纸导出为 PDF

问题描述：

我在电子表格中有很多工作表.我有 2 张我想每次都导出.Sheet One 是包含信息的表单.第二张纸是我需要给客户的一张纸，它从第一张纸中获得了参考.

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.

目前，我创建了一个新的临时电子表格，将工作表一复制到新电子表格中，然后将工作表二复制到临时电子表格中.之后，我将临时电子表格转换为 pdf.然后我删除临时电子表格并将 pdf 保存到 Google Drive 中的一个文件夹中.

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.

创建的 PDF 包含两页的 2 页.我只需要第二张.如果我只传输第二张纸，由于没有第一张纸，这张纸会留下许多 #REF 错误.有没有办法只导出第二张表而不会出现 #REF 错误?

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?

这是我的代码如下:

//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);

答

通过 getBlob 导出电子表格时不包括隐藏的工作表.因此，您可以在导出之前暂时隐藏任何不需要的工作表.

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 exportSheet(sheetName) {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() !== sheetName) {
      sheets[i].hideSheet()
    }
  }
  DriveApp.createFile(ss.getBlob());
  for (var i = 0; i < sheets.length; i++) {
    sheets[i].showSheet()
  }
}

在 Apps Script 中将单张纸导出为 PDF

相关推荐