如何在Google表格中使用.gs脚本打印表格/范围? [英] How to Print sheet/range using .gs script in Google Sheets?

问题描述

我正在尝试在Google表格中创建一个脚本，以选择一个范围并进行打印.我正在尝试根据某些参数打印一些信息.我有以下用于设置所需范围的脚本，但是我看不到使用脚本打印该范围的方法.

I am trying to create a script in Google Sheets that select a range and print it. I am trying to print some information based on some parameters. I have the following script that sets the desired range, but I do not see a way to print it using script.

function printInvoice() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("A1:H46");

  range.activate();
}

有什么建议吗?谢谢！

推荐答案

您可以使用以下脚本:

var PRINT_OPTIONS = {
  'size': 7,               // paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B
  'fzr': false,            // repeat row headers
  'portrait': true,        // false=landscape
  'fitw': true,            // fit window or actual size
  'gridlines': false,      // show gridlines
  'printtitle': false,
  'sheetnames': false,
  'pagenum': 'UNDEFINED',  // CENTER = show page numbers / UNDEFINED = do not show
  'attachment': false
}

var PDF_OPTS = objectToQueryString(PRINT_OPTIONS);

function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('Print...').addItem('Print selected range', 'printSelectedRange').addToUi();
}

function printSelectedRange() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getActiveRange();

  var gid = sheet.getSheetId();
  var printRange = objectToQueryString({
    'c1': range.getColumn() - 1,
    'r1': range.getRow() - 1,
    'c2': range.getColumn() + range.getWidth() - 1,
    'r2': range.getRow() + range.getHeight() - 1
  });
  var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + PDF_OPTS + printRange + "&gid=" + gid;

  var htmlTemplate = HtmlService.createTemplateFromFile('js');
  htmlTemplate.url = url;
  SpreadsheetApp.getUi().showModalDialog(htmlTemplate.evaluate().setHeight(10).setWidth(100), 'Print range');
}

function objectToQueryString(obj) {
  return Object.keys(obj).map(function(key) {
    return Utilities.formatString('&%s=%s', key, obj[key]);
  }).join('');
}

您还需要在项目(File>New>HTML File)中创建一个名称为js的html文件，并粘贴以下代码:

You will also need to create an html file in your project (File>New>HTML File) with the name js, and paste in the following code:

<script>
  window.open('<?=url?>', '_blank', 'width=800, height=600');
  google.script.host.close();
</script>

这将在工作表"菜单中创建一个按钮，该按钮将打开具有选定范围的PDF.您可以修改一些设置，例如打印方向，其大小或是否在脚本顶部显示网格线.如果您仍然希望自动打印范围而不必手动通过 print 对话框，则可以:

This will create a button in your Sheets menu that will open a PDF with the selected range. You can modify some settings such as the print orientation, its size, or whether to show the gridlines or not on top of the script. If you still want to automatically print the ranges without having to manually go through the print dialog, you can either:

• 如果打印机支持此类功能，请使用GmailApp API类将文档发送到打印机.
• 使用 Google云打印.以下博客文章可能会帮助您: https://ctrlq.org/code/20061-google-cloud-print-with-apps-script

• Send the document to your printer using GmailApp API class, if your printer supports such functionality.
• Use Google Cloud Print. The following blog post may help you with that: https://ctrlq.org/code/20061-google-cloud-print-with-apps-script

这篇关于如何在Google表格中使用.gs脚本打印表格/范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！