将Google表格中的一系列单元格以PDF格式下载到本地计算机和其他自动化脚本的脚本? [英] Script to download a range of cells in google sheet as PDF to local computer and other automation scripts?

问题描述

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

I have made a invoice format in g sheet as below:

在B8:C8单元格中(在发票到"下方)中，有一个下拉列表，可从客户表中获取数据.

In the cell B8:C8 (below Invoice To), there is a dropdown which gets data from client sheet.

需要什么?

1. 制作选定单元格的PDF.稍后我想添加一些按钮，并为它们分配宏/脚本，然后g工作表打印操作将工作表上的所有内容打印出来.因此只需要打印发票部分(范围-A1:G46).工作表名称为客户名称+发票日期(F8).

我从堆栈上使用了一些脚本，但是它们增加了边距.我想要的是一张完整的出血pdf文件，如果我使用print(ctrl + p)按钮，我可以得到它.

I have used some script from stack over flow, but they are adding margins. What I want is a full bleed pdf which I am able to get if I use print (ctrl+p) button.

1. 要将发票数据保存到发票注册"表中，以跟踪发票和针对它们的付款.这也可以实现.

我可以通过使用相对引用记录宏来实现这一目标.

I am able to achieve this by recording a macro using relative references.

    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);
};

1. 保存发票数据并使用特定的单元格范围生成pdf后，我想清除某些字段中的数据.这也可以实现.

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
}

1. 在发票号上添加1个数字.发票编号格式为今天的日期+当前月份+到目前为止/年份的发票编号(例如:06111/1920).因此，我们需要在下一个发票上添加1至5个字符.

我使用下面的公式并实现了这一目标:

I have used below formula and achieved this:

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

但也希望为此编写脚本.

But would love to make a script for this also.

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

Basically I had made a invoice tool in excel using VBA and now biggest problem is whenever I export PDF from excel it is showing white margins on right irrespective of troubleshooting.

我想按钮很明显，每当我按新发票"时，发票号都会增加1.

I guess the buttons are obvious and whenever I press 'New Invoice', Invoice No. is increased by 1.

很抱歉，我必须尽可能清楚地指出冗长的帖子，而我对Google应用程序脚本是完全陌生的.非常感谢您的任何帮助.

Sorry for the lengthy post as I had to make it as clear as possible and I am totally new to google app scripts. Any help is greatly appreciated and thanks.

推荐答案

我创建了一个将范围导出到pdf且没有边界的函数.

I've created a function that exports a range to pdf without borders.

请记住，这不会使数据居中显示在页面上，因此，如果数据太小，将无法删除"空白.

Keep in mind that this will not center the data to the page, so if you have data that is too small you won't be able to "delete" the white-space.

这是脚本:

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

}

我正在使用此答案中说明的urlParameter创建导出，然后将其设置为驱动器文件，以便您可以以后再下载.

I am using the urlParameters explained on this answer to create the export then setting it to a Drive File so you can download it later.

如果您希望以更明智的方式命名文件，则始终可以将发票编号附加到临时工作表上，这样便可以相应地命名文件.

If you wish to name your files in a more sensible way, you can always append the invoice number to the temporary sheet, that way the file will be named accordingly.

这篇关于将Google表格中的一系列单元格以PDF格式下载到本地计算机和其他自动化脚本的脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！