javascript - 将 google 工作表中的一系列单元格作为 PDF 下载到本地计算机和其他自动化脚本的脚本？

Question

我在g表中制作了如下发票格式:

在单元格 B8:C8(发票收件人下方)中，有一个从客户表中获取数据的下拉列表。

需要什么？

1. To make PDF of selected cells. I want to later on add some buttons and assign macros/script to them and g sheet print action prints everything on the sheet. So need to print only the invoice section (range-A1:G46). Sheet name to be client name + Invoice Date(F8).

我使用了 stack over flow 中的一些脚本，但它们增加了边距。我想要的是一个完整的流血 pdf，如果我使用打印 (ctrl+p) 按钮，我就能得到它。

2. To save the invoice data to 'Invoice Register' sheet to track the invoice and payments against them. This is also achieved.

我可以通过使用相对引用录制宏来实现这一点。

    function Untitledmacro1() {
  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Invoice Register'), true);
  spreadsheet.getRange('A1').activate();  
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();  
  spreadsheet.getCurrentCell().offset(1, 0).activate();
  spreadsheet.getCurrentCell().setFormulaR1C1('=R[-1]C[0]+1');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Invoice Register'), true);
  spreadsheet.getRange('Invoice!E8').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Invoice Register'), true);
  spreadsheet.getRange('Invoice!F8').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Invoice Register'), true);
  spreadsheet.getRange('Invoice!B8:C8').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  spreadsheet.getCurrentCell().offset(0, 2).activate();

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Invoice Register'), true);
  spreadsheet.getRange('Invoice!F16').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

3. Once the invoice data is saved and pdf is generated with specific cell range, I want to clear the data in certain fields. This is also achieved.

function clearRange() {
  //replace 'Sheet1' with your actual sheet name
  var sheet = SpreadsheetApp.getActive().getSheetByName('Invoice');

  sheet.getRange('B8:C8').clearContent(); // client name

  sheet.getRange('C12:F15').clearContent(); // Invoice items - description, date/period, amount
}

4. Add 1 number to invoice number. The invoice number format is today's date+current Month+invoice number so far/year (eg: 06111/1920). So we need to add 1 to 5th character for next invoice.

我使用了下面的公式并实现了这个:

=CONCATENATE(0, DAY(F8),MONTH(F8), COUNTA('Invoice Register'!B2:B99)+1,"/",21920)

但也很想为此制作一个脚本。

基本上我已经使用 VBA 在 excel 中制作了一个发票工具，现在最大的问题是每当我从 excel 导出 PDF 时，无论是否进行故障排除，它都会在右侧显示白色边距。

我想这些按钮很明显，每当我按下“新发票”时，发票编号就会增加 1。

对于这篇冗长的帖子，我深表歉意，因为我必须尽可能清楚地说明这一点，而且我对谷歌应用程序脚本完全陌生。非常感谢任何帮助。

Answer 1

我创建了一个函数，可以将范围导出为无边框的 pdf。

请记住，这不会将数据居中放置在页面中，因此如果您的数据太小，您将无法“删除”空白区域。

这是脚本:

function downloadRangeToPdf() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A1:E20");

  //Create temporary Spreadsheet
  var tempSpreadsheet = SpreadsheetApp.create("tempSheetInvoiceExport", range.getValues().length, range.getValues()[0].length);
  var tempSheet = tempSpreadsheet.getSheets()[0];
  var tempRange = tempSheet.getRange("A1:E20");

  tempRange.setValues(range.getDisplayValues());
  tempRange.setTextStyles(range.getTextStyles());
  tempRange.setBackgrounds(range.getBackgrounds());
  tempRange.setFontColors(range.getFontColors());
  tempRange.setFontFamilies(range.getFontFamilies());
  tempRange.setFontLines(range.getFontLines());
  tempRange.setFontStyles(range.getFontStyles());
  tempRange.setFontWeights(range.getFontWeights());
  tempRange.setHorizontalAlignments(range.getHorizontalAlignments());
  tempRange.setNumberFormats(range.getNumberFormats());
  tempRange.setTextDirections(range.getTextDirections());
  tempRange.setTextRotations(range.getTextRotations());
  tempRange.setVerticalAlignments(range.getVerticalAlignments());
  tempRange.setWrapStrategies(range.getWrapStrategies());

  SpreadsheetApp.flush(); //Force changes to be written before proceeding.

  //Generate Download As PDF Link

  var url = 'https://docs.google.com/spreadsheets/d/{ID}/export?'.replace('{ID}', tempSpreadsheet.getId());
  var exportOptions = 'exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
    '&size=letter' + // paper size legal / letter / A4
    '&portrait=true' + // orientation, false for landscape
    '&fitw=true&source=labnol' + // 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
    '&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=false' + //true/false
    '&gid=' + tempSheet.getSheetId(); // the sheet's Id
  var token = ScriptApp.getOAuthToken();

  var blob = UrlFetchApp.fetch(url + exportOptions, {
    headers: {
                               Authorization: 'Bearer '+token
    }
  }).getBlob().setName(tempSpreadsheet.getName()+".pdf");

  var pdfFile = DriveApp.createFile(blob);

  var downloadLink = HtmlService
    .createHtmlOutput('<p>Download your file <a href="' + pdfFile.getUrl() + '" target="_blank">here</a>.</p>')
    .setWidth(200)
    .setHeight(100);

  SpreadsheetApp.getUi().showModalDialog(downloadLink, "Download PDF");

  DriveApp.getFileById(tempSpreadsheet.getId()).setTrashed(true); //Place temporary sheet on trash

}

我正在使用在 this answer 上解释的 urlParameters创建导出，然后将其设置为云端硬盘文件，以便稍后下载。

如果您希望以更合理的方式命名您的文件，您始终可以将发票编号附加到临时表，这样文件将被相应地命名。