Exporting Specific Range of Cells to PDF

Question

I use google spreadsheets to create invoices. I want a script through which I can export a selected range (F1:M44) in PDF format with file name Invoice Number(cell B2) + "invoice".
I have tried codes from stack and from youtube. They were all for exporting sheets, not specific ranges.

Answer 1

You have to add parameter referring to that range. This example will download range b2:e46 :

https://docs.google.com/spreadsheets/d/{spreadsheet_id}/export?exportFormat=pdf&format=pdf&gridlines=false&size=A4&gid={gid_id}&range=b2:e46

Below are dummy ids:

The {spreadsheet_id} is a long string representing the spreadsheet e.g. 3qT8h59XF67dac7dBL694VLSRPTCQ0_isnP--BBBpnrQ

{gid_id} is the id of the sheet e.g. 2563688945

also change the range as needed e.g. B2:E46 or B:E or 2:46

Answer 2

This answer is very specific to this question. It includes the range "F1:M44" that is asked for in the question for the range needed to export. Also, the name of the PDF file comes from cell in B2.

It is possible to export a range from your Google Sheet without hiding sheet tabs, rows and columns, or creating a temporary new spreadsheet.

function exportRngToPdf(range,shTabName) {
  var blob,exportUrl,name,options,pdfFile,response,sheetTabId,ss,ssID,url_base;

  range = range ? range : "F1:M44";//Set the default to whatever you want

  shTabName = "Sheet1";//Replace the name with the sheet tab name for your situation
  ss = SpreadsheetApp.getActiveSpreadsheet();//This assumes that the Apps Script project is bound to a G-Sheet
  ssID = ss.getId();
  sh = ss.getSheetByName(shTabName);
  sheetTabId = sh.getSheetId();
  url_base = ss.getUrl().replace(/edit$/,'');
  name = sh.getRange("B2").getValue();
  name = name + "invoice"

  //Logger.log('url_base: ' + url_base)

  exportUrl = url_base + 'export?exportFormat=pdf&format=pdf' +

    '&gid=' + sheetTabId + '&id=' + ssID +
    '&range=' + range + 
    //'&range=NamedRange +
    '&size=A4' +     // paper size
    '&portrait=true' +   // orientation, false for landscape
    '&fitw=true' +       // fit to width, false for actual size
    '&sheetnames=true&printtitle=false&pagenumbers=true' + //hide optional headers and footers
    '&gridlines=false' + // hide gridlines
    '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  //Logger.log('exportUrl: ' + exportUrl)

  options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }

  options.muteHttpExceptions = true;//Make sure this is always set

  response = UrlFetchApp.fetch(exportUrl, options);

  //Logger.log(response.getResponseCode())

  if (response.getResponseCode() !== 200) {
    console.log("Error exporting Sheet to PDF!  Response Code: " + response.getResponseCode());
    return;

  }
  
  blob = response.getBlob();
  blob.setName(name + '.pdf')

  pdfFile = DriveApp.createFile(blob);//Create the PDF file
  //Logger.log('pdfFile ID: ' +pdfFile.getId())
}

There are various strategies that have been used to export part of a Google Sheet to a PDF.

• Create a new spreadsheet file with just the wanted content
• Create a new sheet tab, put the content in the sheet tab and hide any other sheet tabs, hide unwanted rows and columns
• Build a download URL with a range setting
  • Use range address
  • Use a named range - &gid=sheetId&range=NamedRange
• Export content to a Google Doc and create a PDF from the Google Doc
• Merge G-Sheet content with a template file, and create the PDF from the template file.
• Get a range of content from the Sheet, convert it to HTML and use the HTML to create the PDF file

I would use the method of building a download URL with the range included in the download URL.

NOTE: Apps Script runs on Google's servers, and has no access to the user's local device. So, Apps Script can't download a PDF file to the user's local device.

Answer 3

Solution

To be able to export a specfic range to PDF, as there is no specific tool in Apps Script to do this you will need execute workaround.

The following workaround will automatically copy the range we want to export to a new blank spreadsheet to then be able to export only the exact range.

Why do we need a new spreadsheet?

Well for exporting a range and not getting in the way all the blank cells we will need to hide all the non relevant rows and columns. To achieve that, the easiest way is to paste our desired range in the first cell (A1) of our sheet.

Moreover, by default this will export all the sheets of the spreadsheet so we can only have the sheet of this new range arranged in the first cell to avoid other data to get in between. To avoid deleting the original sheet, the best way to achieve this is to create a new spreadsheet and after the process is done delete it.

Here is the script that solves this problem. It is self explained with comments:

function myFunction() {
  // Get the range of cells you want to export 
  var rangeValues = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('D10:F13').getValues();
  // Get the Drive folder you want to store your PDF to. Otherwise use root folder (if you dont mind about this)
  var folder = DriveApp.getFolderById('FOLDERID');
  
  // Create a blank spreadsheet to be able to export just the range
  var destSpreadsheet = SpreadsheetApp.create('PDF');
  
  // Check first if the sheet you are going to create exists. Otherwsie create it and copy paste the range
  // we want to export as PDF in the first values of the sheet. I.e if our range is 5 rows and 6 columns we want
  // it to be copied from A1 to F5 for example. Then we can hide the rest of columns and rows and export 
  // what we have left
  var sheet2 = destSpreadsheet.getSheetByName('sheet2');
  if(!sheet2){
    destSpreadsheet.insertSheet('sheet2').getRange('A1:C4').setValues(rangeValues);
    var sheet2 = destSpreadsheet.getSheetByName('sheet2');
  }
  
  // Hide all the rows and columns that do not have content 
  sheet2.hideRows(sheet2.getLastRow()+1, sheet2.getMaxRows()-sheet2.getLastRow());
  sheet2.hideColumns(sheet2.getLastColumn()+1, sheet2.getMaxColumns()-sheet2.getLastColumn());
  // Delete the first sheet that is automatically created when you create a new spreadsheet
  destSpreadsheet.deleteSheet(destSpreadsheet.getSheetByName('Sheet1'));
  
  // Export our new spreadsheet to PDF
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName('pdf');
  var newFile = folder.createFile(theBlob);
  
  //Delete the spreadsheet we created to export this range. 
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

Answer 4

I found this solution for Windows very convenient: How to Print sheet/range using .gs script in Google Sheets? It does not need to create a new spreadsheet. Rather, you select the cells on the sheet first, then click a "Print" button, and the selected range is passed to the command that creates the PDF. I will however create a new temporary sheet in the spreadsheet, not a complete new spreadsheet, copy into it the data from various places in the spreadsheet, then print it, then delete it.

Answer 5

I am using the above parameters in combination with JavaScript.

The parameters look like this:

export?format=pdf&size=A4&fzr=true&portrait=true&fitw=true&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&gid=1215564098&ir=false&ic=false&r1=0&c1=6&r2=44&c2=20&attachment=true

Combined with the JavaScript, this is the result. I have created a bookmark and placed the following JavaScript as the URL.

javascript:var winURL = window.location.href;if(winURL.indexOf('/edit#gid=') > 0)%7Bwindow.location.assign(winURL.replace('/edit#gid=', '/export?format=pdf&size=A4&fzr=true&portrait=true&fitw=true&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&ir=false&ic=false&r1=0&c1=6&r2=44&c2=20&attachment=true&gid='));%7Delse%7Balert('Incorrect URL format');%7D

Every time I need to export a Google Sheet I just click on the bookmark and the export is done. I hope this might help you as well.

I am still missing one parameter and this is the possibility to rename the exported PDF.