google-apps-script - 将 Google 工作表导出为 PDF 设置参数，例如边距

问题描述

我正在尝试将电子表格中的特定工作表导出到 PDF 文件，该文件将根据单元格值设置一个名称，我想设置导出设置，以便所有边距都设置为 0

我在线获得了以下代码并对其进行了修改以满足我的需要，但导出并没有像我想要的那样通过文件名，它也没有将导出边距设置为 0 并适合页面：

有没有人知道我如何通过应用程序脚本来做到这一点（如果有人有更好的脚本我愿意尝试:)）

function exportPDF() { 
  var sourceSpreadsheet = SpreadsheetApp.getActive();

  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName("Agreement");

  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Agreement");
  var sheet = ss.getSheetByName("Input");
  var name = sheet.getRange("C3").getValue();
  var company = sheet.getRange("C4").getValue();
  var order = sheet.getRange("C11").getValue();
  var gAcc = sheet.getRange("C12").getValue();
  var pdfName = "Agreement_" & name;

  if(company != "")
  {
    pdfName = pdfName + "_" & company & "_" & order & "_" & gAcc;
  }
  else
  {
    pdfName = pdfName + "_" & order & "_" & gAcc;
  }

  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }

  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

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

  var destSheet = destSpreadsheet.getSheets()[0];

  var sourceRange = sourceSheet.getRange("A1:I57");
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);

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

  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

标签： google-apps-scriptgoogle-sheets

解决方案

文件名是通过重新排列代码并正确连接一些字符串来实现的。

我在几个地方修改了你的脚本，如下：

function exportPDF() { 

  var sourceSpreadsheet = SpreadsheetApp.getActive();

  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName("Agreement");

  var name = sourceSheet.getRange("C3").getValue();
  var company = sourceSheet.getRange("C4").getValue();
  var order = sourceSheet.getRange("C11").getValue();
  var gAcc = sourceSheet.getRange("C12").getValue();
  var pdfName = "Agreement_" + name;

  if(company != "")
  {
    pdfName = pdfName + "_" + company + "_" + order + "_" + gAcc;
  }
  else
  {
    pdfName = pdfName + "_" + order + "_" + gAcc;
  }

  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  var folder;
  if (parents.hasNext()) {
     folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }

  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy(pdfName, folder))

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

  var destSheet = destSpreadsheet.getSheets()[0];

  var sourceRange = sourceSheet.getDataRange();
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.setActiveSelection(sourceRange.getA1Notation());
  destRange.setValues(sourcevalues);

  var theBlob = getBlob();
  var newFile = folder.createFile(theBlob).setName(pdfName);

  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

到目前为止，您已经创建了一个包含所需数据和名称的 pdf。但利润仍然不是你想要的。

设置 pdf 边距：

使用获取 blobUrlFetchApp

function getBlob(){
  var url = 'https://docs.google.com/spreadsheets/d/';
  var id = '<YOUR-FILE-ID>';
  var url_ext = '/export?'
  +'format=pdf'
  +'&size=a4'                      //A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
  +'&portrait=true'                //true= Potrait / false= Landscape
  +'&scale=1'                      //1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
  +'&top_margin=0.00'              //All four margins must be set!
  +'&bottom_margin=0.00'           //All four margins must be set!
  +'&left_margin=0.00'             //All four margins must be set!
  +'&right_margin=0.00'            //All four margins must be set!
  +'&gridlines=true'               //true/false
  +'&printnotes=false'             //true/false
  +'&pageorder=2'                  //1= Down, then over / 2= Over, then down
  +'&horizontal_alignment=LEFT'  //LEFT/CENTER/RIGHT
  +'&vertical_alignment=TOP'       //TOP/MIDDLE/BOTTOM
  +'&printtitle=false'             //true/false
  +'&sheetnames=false'             //true/false
  +'&fzr=false'                    //true/false
  +'&fzc=false'                    //true/false
  +'&attachment=false'
  +'&gid=0';
  // console.log(url+id+url_ext);
  var blob = UrlFetchApp.fetch(url+id+url_ext).getBlob().getAs('application/pdf');
  return blob;
}

你有它。从工作表导出的没有边距的 pdf。