Script to download a range of cells in google sheet as PDF to local computer and other automation scripts?

Question

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

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

What is requierd?

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).

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.

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

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

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.

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.

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.

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

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.

Answer 1

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.

Here is the script:

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

}

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.

Answer 2

To add to ZektorH's great answer, it is possible to download a range as a PDF directly through URL options. You don't have to create a temporary sheet.

The URL options seem to be:

&r1 // first row to print: 0-indexed 
&r2 // last row to print: 1-indexed 
    // this seems inconsistent; maybe it is technically the first row that is not printed, 0-indexed

&c1 // first column to print: 0-indexed 
&c2 // last column to print: 1-indexed (see above)

These URL options are included in the code on this page

Here are two examples of how to append these parameters:

&r1=0&r2=1&c1=0&c2=3 // cells A1:C1
&r1=2&r2=3&c1=3&c2=6 // cells D3:F4

These seem to behave oddly if there are hidden rows/columns within the search path, extra cells seem to be shown.