有没有办法将电子表格的多个选项卡(但不是全部)打印为单个 pdf? [英] Is there a way to print multiple tabs of a spreadsheet (but not all of them) as a single pdf?

问题描述

我做了一些研究，但没有找到任何方法来做到这一点.试图做安德鲁罗伯茨在这里解释的http://www.andrewroberts.net/2017/03/apps-script-create-pdf-multi-sheet-google-sheet/ 将电子表格(或特定标签)转换为 pdf:>

I've done some research but haven't found any way to do this. Tried to do what Andrew Roberts explains here http://www.andrewroberts.net/2017/03/apps-script-create-pdf-multi-sheet-google-sheet/ to convert the spreadsheet (or a specific tab) to pdf:

    function convertSpreadsheetToPdf(email, spreadsheetId, sheetName, pdfName) {

     var spreadsheet = spreadsheetId ? SpreadsheetApp.openById(spreadsheetId) : 
     SpreadsheetApp.getActiveSpreadsheet();
     spreadsheetId = spreadsheetId ? spreadsheetId : spreadsheet.getId()  
     var sheetId = sheetName ? spreadsheet.getSheetByName(sheetName).getSheetId() : null;  
     var pdfName = pdfName ? pdfName : spreadsheet.getName();
     var parents = DriveApp.getFileById(spreadsheetId).getParents();
     var folder = parents.hasNext() ? parents.next() : DriveApp.getRootFolder();
     var url_base = spreadsheet.getUrl().replace(/edit$/,'');

     var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf

      // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
      // following parameters are optional...
      + '&size=A4'      // paper size
      + '&portrait=false'    // orientation, false for landscape
      + '&fitw=true'        // fit to width, false for actual size
      + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }

  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  folder.createFile(blob);

  if (email) {

    var mailOptions = {
      attachments:blob
    }

    MailApp.sendEmail(
      email, 
      "Here is a file named " + pdfName, 
      "Please let me know if you have any questions or comments.", 
      mailOptions);
  }

看评论，他建议添加以下代码，我将其放入另一个函数中:

Looking at the comments, he proposes adding the following code, which I put inside another function:

  function printtwopdfs() {

  var sheetNames = ["Table1,"Table2"]

  sheetNames.forEach(function(sheetName) {
  convertSpreadsheetToPdf(TEST_EMAIL, "xxxxxx", sheetName, 
  "pdfteste")
  })}

但我得到的是每张单独打印为一个pdf.

But what I got was each sheet printed as one pdf separately.

隐藏除我想要的标签之外的所有标签并将电子表格作为一个整体打印对我来说不是一个选择，因为它有太多的标签(大约 15 个).除此之外，我不太可能将选定的工作表复制到新的电子表格中，因为该功能需要很长时间才能运行，除非我能很快做到这一点.关于如何进行的任何想法?

Hidding all tabs except the ones I want and printing the spreadsheet as a whole isn't an option for me, since it has too many tabs (about 15). Besides that, I'm not likely to copy the selected sheets to a new spreadsheet because the function would take too long to run, unless I can do this very quickly. Any ideas of how to proceed?

推荐答案

• 您想通过从 Google 电子表格中选择工作表来创建 PDF 文件.
  • Google 电子表格有 15 张.
  • 例如，您要选择 2 张Table1"和Table2"，并将它们创建为一个 PDF 文件.然后，PDF 文件将作为电子邮件发送.
  • 您希望在 20 秒内完成运行脚本.

  从你的问题和回复中，我可以理解如上.如果我的理解是正确的，这个答案怎么样?请将此视为几种可能的答案之一.

  From your question and replying, I could understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

  在这种情况下，我认为除了要使用的工作表之外隐藏工作表可能是合适的.为此，在这里，我想建议使用 Sheets API 中的电子表格.batchUpdate 方法隐藏工作表.因为我认为在这种情况下，Sheets API 的流程成本可以低于 Spreadsheet 服务.

  In this case, I thought that hiding sheets except for the sheets you want to use might be suitable. For this, here, I would like to propose to hide the sheets using the method of spreadsheets.batchUpdate in Sheets API. Because I thought that in this case, the process cost of Sheets API can be lower than that of Spreadsheet service.

  修改后的脚本流程如下.

  The flow of this modified script is as follows.

  1. 隐藏除要创建为 PDF 文件的工作表之外的工作表.
  2. 从电子表格创建 PDF 文件.
  3. 显示所有工作表.

  修改后的脚本:

  在运行脚本之前，请在 Advanced Google 上启用 Sheets API服务.并请设置sheetNames、email 和spreadsheetId 的变量.并运行printtwopdfs()的函数.

  Modified script:

  Before you run the script, please enable Sheets API at Advanced Google services. And please set the variables of sheetNames, email and spreadsheetId. And run the function of printtwopdfs().

  function convertSpreadsheetToPdf(email, spreadsheetId, pdfName) {
    var spreadsheet = spreadsheetId ? SpreadsheetApp.openById(spreadsheetId) : SpreadsheetApp.getActiveSpreadsheet();
    var spreadsheetId = spreadsheetId ? spreadsheetId : spreadsheet.getId();
    var pdfName = pdfName ? pdfName : spreadsheet.getName();
    var parents = DriveApp.getFileById(spreadsheetId).getParents();
    var folder = parents.hasNext() ? parents.next() : DriveApp.getRootFolder();
    var url_base = spreadsheet.getUrl().replace(/edit$/,'');
    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
    + '&id=' + spreadsheetId
    + '&size=A4'      // paper size
    + '&portrait=false'    // orientation, false for landscape
    + '&fitw=true'        // fit to width, false for actual size
    + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
    + '&gridlines=false'  // hide gridlines
    + '&fzr=false';       // do not repeat row headers (frozen rows) on each page
    var options = {headers: {'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()}}
    var response = UrlFetchApp.fetch(url_base + url_ext, options);
    var blob = response.getBlob().setName(pdfName + '.pdf');
    folder.createFile(blob);
    if (email) {
      var mailOptions = {attachments:blob}
      MailApp.sendEmail(email, "Here is a file named " + pdfName, "Please let me know if you have any questions or comments.", mailOptions);
    }
  }
  
  // Please run this function.
  function printtwopdfs() {
    var sheetNames = ["Table1", "Table2"];
    var email = "###";  // Please set email address.
    var spreadsheetId = "###";  // Please set Spreadsheet ID.
  
    // Hide sheets.
    var allSheets = SpreadsheetApp.openById(spreadsheetId).getSheets();
    var hiddenResource = allSheets.map(function(s) {
      var obj = {updateSheetProperties: {properties: {sheetId: s.getSheetId(), hidden: true}, fields: "hidden"}};
      if (sheetNames.indexOf(s.getSheetName()) != -1) obj.updateSheetProperties.properties.hidden = false;
      return obj;
    });
    Sheets.Spreadsheets.batchUpdate({requests: hiddenResource}, spreadsheetId);
  
    // Create PDF file.
    convertSpreadsheetToPdf(email, spreadsheetId, "pdfteste");
  
    // Show all sheets.
    var showResource = allSheets.map(function(s) {return {updateSheetProperties: {properties: {sheetId: s.getSheetId(), hidden: false}, fields: "hidden"}}});
    Sheets.Spreadsheets.batchUpdate({requests: showResource}, spreadsheetId);
  }
  

  注意:

  • 在我的环境中，当上述脚本用于包含 20 个单元格值的 Google 电子表格时，处理时间约为 5 秒.但是我不确定这是否可以用于您的实际情况.对此我深表歉意.
  • 高级 Google 服务
  • 方法:spreadsheets.batchUpdate立>
  • UpdateSheetPropertiesRequest

  这篇关于有没有办法将电子表格的多个选项卡(但不是全部)打印为单个 pdf?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！