Google 应用程序脚本 getAs('application/pdf') 布局 [英] Google apps script getAs('application/pdf') layout

问题描述

我正在使用在此处找到的应用脚本从电子表格生成 PDF 文件.这个用的好旧方法 getAs('application/pdf') 并且效果很好.

I am generating a PDF file from a spreadsheet using an app script I found here. This one uses the good old Method getAs('application/pdf') and works great.

问题在于，以这种方式生成的 PDF 文档具有不需要的不对称边距(右侧较大，左侧较窄).我只是想让页面居中.奇怪的是，当我从 Google 菜单 File -> Print(或 Ctrl + P)打印时，文档显示正确居中.

The problem is that the PDF document generated in this way has unwanted asymmetric margins (larger on the right, narrow on the left). I just wanted the page to be centered. The weird thing is that when I print from the Google menu File -> Print (or Ctrl + P) the document appears centered correctly.

我的代码如下:

function CreaPDF() {
  //The function prints an invoice to PDF. First it copies spreadsheet to a new document.
  //Deletes all sheet except the one to print. Saves it to PDF. 
  //It overwrites any existing doc with same name.

  var sourceSpreadsheet = SpreadsheetApp.getActive();          
  var sheetName = "Factura";
  var folderID = getParentFolder(); // Folder id to save in a folder.
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID); 
  var numf = sourceSpreadsheet.getRangeByName("NumeroFactura").getValue();
  var anof = numf.split("/",2);   // Seeks number and year -> filename 

  var pdfName = anof[1] +"_Factura_" + anof[0]+ "_Dra_Salazar"; // Nombre del documento;

  //Copy whole spreadsheet 2 temporary sheet
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  //delete redundant sheets
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName){
    destSpreadsheet.deleteSheet(sheets[i]);
    }
  }

  //Deletes pdf if already exists
  var files = DriveApp.getFilesByName(pdfName);
  while (files.hasNext()) {
   files.next().setTrashed(true);
   }
  var destSheet = destSpreadsheet.getSheets()[0];
  //repace cell values with text (to avoid broken references)
  var sourceRange = sourceSheet.getRange(1, 1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());

  var sourcevalues = sourceRange.getDisplayValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);

  SpreadsheetApp.getActiveSpreadsheet().toast('Creando PDF');

  //save to pdf
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);

  //Delete the temporary sheet
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
  return true;
}

我打印时的设置是:

• 纸张尺寸:A4"
• 比例:正常"(我也尝试过:适合宽度"和适合页面")
• 方向:肖像"
• 边距:正常"

正如在 Google 帮助论坛中看到的那样谷歌打印方式的一个老问题.总之，似乎没有保存打印设置，也无法将任何参数传递给 getAs('application/pdf') 方法.所以我假设该方法(和菜单打印选项)使用无法修改的默认参数.有什么解决办法吗?打印或更改页面设置"帮助页面没有太大帮助.

As seen in the Google Help Forums it seems like an old issue with the way google prints. In summary, it seems that print settings are not saved and there is no way to pass any parameter to the getAs('application/pdf') method either. So i assume that the method (and menu print options) use default parameters that can not be modified. Any solution for this? The "Print or change page setup" help page does not help too much.

非常感谢

推荐答案

尝试基于 https://ctrlq.org/code/19869-email-google-spreadsheets-pdf

使用导出 url 参数，您可以为结果 pdf 设置所需的选项.您还可以设置要导出的工作表的特定 id，这样您就不再需要复制整个电子表格了.

Using export url parameters you can set needed options for result pdf. Also you can set specific id of sheet to export, so you don't need to make duplicate of your whole spreadsheet anymore.

function CreaPDF() {
  //The function prints an invoice to PDF. First it copies spreadsheet to a new document.
  //Deletes all sheet except the one to print. Saves it to PDF. 
  //It overwrites any existing doc with same name.

  var sourceSpreadsheet = SpreadsheetApp.getActive();          
  var sheetName = "Factura";
  var folderID = getParentFolder(); // Folder id to save in a folder.
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID); 
  var numf = sourceSpreadsheet.getRangeByName("NumeroFactura").getValue();
  var anof = numf.split("/",2);   // Seeks number and year -> filename 

  var pdfName = anof[1] +"_Factura_" + anof[0]+ "_Dra_Salazar"; // Nombre del documento;

  SpreadsheetApp.getActiveSpreadsheet().toast('Creando PDF');

  // export url
  var url = 'https://docs.google.com/spreadsheets/d/'+sourceSpreadsheet.getId()+'/export?exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
  + '&size=A4'                           // paper size legal / letter / A4
  + '&portrait=true'                     // orientation, false for landscape
  + '&fitw=false'                        // fit to page width, false for actual size
  + '&sheetnames=false&printtitle=false' // hide optional headers and footers
  + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
  + '&fzr=false'                         // do not repeat row headers (frozen rows) on each page
  + '&gid='+sourceSheet.getSheetId();    // the sheet's Id

  var token = ScriptApp.getOAuthToken();

  // request export url
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  var theBlob = response.getBlob().setName(pdfName+'.pdf');

  // delete pdf if already exists
  var files = folder.getFilesByName(pdfName);
  while (files.hasNext())
  {
    files.next().setTrashed(true);
  }

  // create pdf
  var newFile = folder.createFile(theBlob);

  return true;
}

这篇关于Google 应用程序脚本 getAs('application/pdf') 布局的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！