How to Print to PDF Using Apps Script in Google Sheets

Using Google Sheets’ print command makes it easy to print any sheet in PDF format. But there is a distinctive feature in Google Sheets like the Apps Script. This extension allows us to automatically print any datasheet into PDF format and save it to Google Drive. So, we’ll see 4 suitable examples to print to PDF using Apps Script in Google Sheets with clear images and steps. And at the end of the procedure, you’ll find your datasheet printed in PDF format like below.

How to Print to PDF Using Apps Script in Google Sheets


A Sample of Practice Spreadsheet

You can download Google Sheets from here and practice very quickly.


4 Suitable Apps Scripts to Print to PDF in Google Sheets

Let’s get introduced to our dataset first. Here we have some products in Column B and their prices in Column C. Now I want to print this dataset in PDF format using Apps Script. So, I’ll show you 4 suitable methods to print to PDF using Apps Script in Google Sheets with the help of this dataset.

Dataset to Print to PDF Using Apps Script in Google Sheets


1. Printing Current Sheet to PDF

First of all, we want to print the current sheet to PDF format of our dataset. We’ll do this by using the Apps Script extension. I’ll insert a script code which will make a sub-menu under the Extensions menu in our Google Sheets. Under this sub-menu there are 5 options which enable printing to PDF format in different conditions. The first condition is to print the current sheet. After printing, the PDF file will be automatically saved in Google Drive. Let’s see the steps.

Steps:

  • Firstly, go to Extensions > Apps Script.

Going to Apps Script Extension

  • Secondly, enter the following code in the Apps Script window-
var saveToDrive = true
function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Print all sheets', 'exportAsPDF')
    .addItem('Print all sheets to separate PDFs', 'exportAllSheetsAsSeparatePDFs')
    .addItem('Print current sheet', 'exportCurrentSheetAsPDF')
    .addItem('Print selected region', 'exportPartAsPDF')
    .addItem('Print predefined region', 'exportNamedRangesAsPDF')
    .addToUi()
}
function _exportBlob(blob, fileName, spreadsheet) {
  blob = blob.setName(fileName)
  var store = saveToDrive ? DriveApp : DriveApp.getFileById(spreadsheet.getId()).getParents().next()
  var pdf = store.createFile(blob)
  const htmlOutput = HtmlService
    .createHtmlOutput('<p>Open <a href="' + pdf.getUrl() + '" target="_blank">' + fileName + '</a></p>')
    .setWidth(400)
    .setHeight(200)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Printed Successfully')
}
function exportAsPDF() {
  var googlesheet = SpreadsheetApp.getActiveSpreadsheet()
  var dot = _getAsBlob(googlesheet.getUrl())
  _exportBlob(dot, googlesheet.getName(), googlesheet)
}
function _getAsBlob(url, sheet, range) {
  var areaParam = ''
  var dataParam = ''
  if (range) {
    areaParam =
      '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    dataParam = '&gid=' + sheet.getSheetId()
  }
  var printUrl = url.replace(/\/edit.*$/, '')
      + '/export?exportFormat=pdf&format=pdf'
      + '&size=LETTER'
      + '&portrait=true'
      + '&fitw=true'       
      + '&top_margin=1'              
      + '&bottom_margin=1'          
      + '&left_margin=1'             
      + '&right_margin=1'           
      + '&sheetnames=false&printtitle=false'
      + '&pagenum=CENTER'
      + '&gridlines=true'
      + '&fzr=FALSE'      
      + dataParam
      + areaParam  
  Logger.log('printUrl=' + printUrl)
  var result
  var u = 0
  for (; u < 7; u += 1) {
    result = UrlFetchApp.fetch(printUrl, {
      muteHttpExceptions: true,
      headers: { 
        Authorization: 'Bearer ' +  ScriptApp.getOAuthToken(),
      },
    })
    if (result.getResponseCode() === 429) {
      Utilities.sleep(3000)
    } else {
      break
    }
  } 
  if (u === 7) {
    throw new Error('Printing error')
  }  
  return result.getBlob()
}
function exportAllSheetsAsSeparatePDFs() {
  var googlesheet = SpreadsheetApp.getActiveSpreadsheet()
  var datas = []
  var store = saveToDrive ? DriveApp : DriveApp.getFileById(googlesheet.getId()).getParents().next()
  googlesheet.getSheets().forEach(function (sheet) {
    googlesheet.setActiveSheet(sheet)
    
    var dot = _getAsBlob(googlesheet.getUrl(), sheet)
    var fileTitle = sheet.getName()
    dot = dot.setName(fileTitle)
    var pdf = store.createFile(dot)
    
    datas.push({
      url: pdf.getUrl(),
      name: fileTitle,
    })
  })
  const htmlOutput = HtmlService
    .createHtmlOutput('<p>Open PDFs</p>'
      + '<ul>'
      + datas.reduce(function (prev, file) {
        prev += '<li><a href="' + file.url + '" target="_blank">' + file.name + '</a></li>'
        return prev
      }, '')
      + '</ul>')
    .setWidth(400)
    .setHeight(200)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Printed Successfully')
}
function exportCurrentSheetAsPDF() {
  var googlesheet = SpreadsheetApp.getActiveSpreadsheet()
  var presentSheet = SpreadsheetApp.getActiveSheet() 
  var dot = _getAsBlob(googlesheet.getUrl(), presentSheet)
  _exportBlob(dot, presentSheet.getName(), googlesheet)
}
function exportPartAsPDF(predefinedRanges) {
  var ai = SpreadsheetApp.getUi()
  var googlesheet = SpreadsheetApp.getActiveSpreadsheet() 
  var markedRanges
  var dataSuffix
  if (predefinedRanges) {
    markedRanges = predefinedRanges
    dataSuffix = '-predefined'
  } else {
    var activeRangeList = googlesheet.getActiveRangeList()
    if (!activeRangeList) {
      ai.alert('Select a range to export')
      return
    }
    markedRanges = activeRangeList.getRanges()
    dataSuffix = '-selected'
  } 
  if (markedRanges.length === 1) {
    var presentSheet = markedRanges[0].getSheet()
    var dot = _getAsBlob(googlesheet.getUrl(), presentSheet, markedRanges[0])
    
    var fileTitle = googlesheet.getName() + dataSuffix
    _exportBlob(dot, fileTitle, googlesheet)
    return
  } 
  var tempGS = SpreadsheetApp.create(googlesheet.getName() + dataSuffix)
  if (!saveToDrive) {
    DriveApp.getFileById(tempGS.getId()).moveTo(DriveApp.getFileById(googlesheet.getId()).getParents().next())
  }
  var tempSheet = tempGS.getSheets()
  var values1 = tempSheet.length > 0 ? tempSheet[0] : undefined
  SpreadsheetApp.setActiveSpreadsheet(tempGS)
  tempGS.setSpreadsheetTimeZone(googlesheet.getSpreadsheetTimeZone())
  tempGS.setSpreadsheetLocale(googlesheet.getSpreadsheetLocale()) 
  for (var u = 0; u < markedRanges.length; u++) {
    var selectedArea = markedRanges[u]
    var mainSheet = selectedArea.getSheet()
    var mainSheetName = mainSheet.getName()
    
    var allSheet = tempGS.getSheetByName(mainSheetName)
    if (!allSheet) {
      allSheet = tempGS.insertSheet(mainSheetName)
    }
    Logger.log('a1notation=' + selectedArea.getA1Notation())
    var allRange = allSheet.getRange(selectedArea.getA1Notation())
    allRange.setValues(selectedArea.getValues())
    allRange.setTextStyles(selectedArea.getTextStyles())
    allRange.setBackgrounds(selectedArea.getBackgrounds())
    allRange.setFontColors(selectedArea.getFontColors())
    allRange.setFontFamilies(selectedArea.getFontFamilies())
    allRange.setFontLines(selectedArea.getFontLines())
    allRange.setFontStyles(selectedArea.getFontStyles())
    allRange.setFontWeights(selectedArea.getFontWeights())
    allRange.setHorizontalAlignments(selectedArea.getHorizontalAlignments())
    allRange.setNumberFormats(selectedArea.getNumberFormats())
    allRange.setTextDirections(selectedArea.getTextDirections())
    allRange.setTextRotations(selectedArea.getTextRotations())
    allRange.setVerticalAlignments(selectedArea.getVerticalAlignments())
    allRange.setWrapStrategies(selectedArea.getWrapStrategies())
  }
  if (values1) {
    Logger.log('lastcol = ' + values1.getLastColumn() + ',lastrow=' + values1.getLastRow())
    if (values1 && values1.getLastColumn() === 0 && values1.getLastRow() === 0) {
      tempGS.deleteSheet(values1)
    }
  } 
  exportAsPDF()
  SpreadsheetApp.setActiveSpreadsheet(googlesheet)
  DriveApp.getFileById(tempGS.getId()).setTrashed(true)
}
function exportNamedRangesAsPDF() {
  var googlesheet = SpreadsheetApp.getActiveSpreadsheet()
  var allTitledRange = googlesheet.getNamedRanges()
  var toPrintTitledRange = []
  for (var u = 0; u < allTitledRange.length; u++) {
    var titledRange = allTitledRange[u]
    if (/^print_area_.*$/.test(titledRange.getName())) {
      Logger.log('found titled range ' + titledRange.getName())
      toPrintTitledRange.push(titledRange.getRange())
    }
  }
  if (toPrintTitledRange.length === 0) {
    SpreadsheetApp.getUi().alert('No print areas found. Add at least one.')
    return
  } else {
    toPrintTitledRange.sort(function (a, b) {
      return a.getSheet().getIndex() - b.getSheet().getIndex()
    })
    exportPartAsPDF(toPrintTitledRange)
  }
}
  • Then, rename the script as Print to PDF and click on the Save button.
  • Next, select onOpen from the menu as shown in the picture and press the Run button.

Inserting Code in Apps Script Window

  • Now if you go to the Extensions menu again, you’ll find the Print to PDF sub-menu. You’ll notice 5 alternatives under this sub-menu that you can select from to carry out various conditions.
  • As we want to print the current sheet so we’ll choose the Print current sheet option.

Going to Print current sheet Option

  • After that, you’ll find a message like below titled Printed Successfully.
  • Consequently, click on the Open Printing Current Sheet option.

Selecting Open Printing Current Sheet Option

  • Finally, you’ll get your desired PDF file automatically saved in your Google Drive.

Output of Printing Current Sheet to PDF

Read More: How to Print Notes in Google Sheets (with Quick Steps)


2. Exporting All Sheets to PDF

Now, we want to print all the sheets of our Google Sheet to the PDF format. Firstly, we’ll print all the sheets in the same PDF file. Then I’ll print them in separate PDF files. You’ll find the process below.


2.1 Same PDF File

Let’s see the steps of printing all sheets of a Google Sheets in the same PDF file.

Steps:

  • At first, follow the first 4 steps of method 1 to insert the script code into Apps Script.
  • Next, go to Extensions > Print to PDF > Print all sheets.

Going to Print all sheets Option

  • Then, you’ll see a notice with the headline “Printed Successfully” like the one below.
  • Therefore, select the option Open google sheets script print to pdf.

Selecting Open Google Sheets Script Print to PDF Option

  • At last, your preferred PDF file will be instantly saved to your Google Drive.
  • This PDF document has 5 pages because the five sheets were produced all at once.

Output of Exporting All Sheets to Same PDF File


2.2 Separate PDF File

Now, see the process to print all sheets in separate PDF files below.

Steps:

  • First, to insert the script code into Apps Script, adhere to the first 4 steps of method 1.
  • Next, select Extensions.
  • Then, go to Print to PDF > Print all sheets to separate PDFs.

Going to Print All Sheets to Separate PDFs Option

  • Next, the Printed Successfully window will open.
  • You’ll see 5 PDF files in a list because all 5 sheets are printed separately. We have a total of 5 sheets in our Google Sheets. All of them will print separately in PDF format and saved in your Google Drive.
  • Now, we will open only one PDF file to see the output. You can also open the rest of the PDF files from your Google Drive.
  • After that, click on Exporting All Sheets to open this PDF file in your Google Drive.

Choosing Exporting All Sheets Option

  • In the end, you’ll automatically save the desired PDF files to your Google Drive account.
  • Then, we opened one of those files to show here.

Output of Exporting All Sheets to Different PDF Files

Read More: How to Print Google Sheets on One Page (2 Distinct Scenarios)


Similar Readings


3. Printing Specific Area to PDF

At this moment we want to print only the first 4 products and their prices in PDF format. So, our printing area is from Cell B2 to C8 as you can see in the picture. Let’s see how to print this specific area automatically by the Apps Script method.

Dataset to Printing Specific Area to PDF

Steps:

  • First of all, the first 4 steps of method 1 should be followed in order to enter the script code into Apps Script.
  • Then, select the cells from Cell B2 to C8 and then go to Extensions.
  • Next, select the Print selected region under the Print to PDF sub-menu.

Going to Print Selected Region Option

  • After that, you’ll see a window called Printed Successfully.
  • Consequently, click on the Open google sheets script print to pdf-selected button.

Selecting Open Google Sheets Script Print to PDF-Selected Option

  • Ultimately, you’ll get your desired PDF file where only a specific area is printed in your Google Drive. The area is the first 5 rows of our dataset including the title as you can see in the picture.

Output of Printing Specific Area to PDF

Read More: How to Print Row Numbers in Google Sheets (4 Useful Methods)


4. Exporting Predefined Range to PDF

Apart from the previous method, we’ll define a range early in our Google Sheets. Then we’ll print this to PDF format. Now, we want to print the products list which is in Column B of our dataset. So, we’ll redefine the range of cells from Cell B4 to B13 and then print it to PDF format.

Dataset to Exporting Predefined Range to PDF

Steps:

  • Before all, stick to the first 4 steps of method 1 to insert the script code into Apps Script.
  • Next, select all the cells of Column B from Cell B4 to B13.
  • Then, select the Named Ranges option under the Data menu.

Choosing Named Ranges Option

  • Next, the Named ranges window will open.
  • After that, give the ranges a name as print_area_1. We choose this format because it is recognizable for our script code. You must use this format to name your preferred range. Otherwise the script code won’t recognize it and you won’t get any output.
  • Consequently, press the Done button.

Giving Range A Name

  • Moreover, you’ll see that a range is defined. Now we’ll print this predefined region in PDF format.

Showing Predefined Range

  • For that purpose, go to Extensions > Print to PDF > Print predefined region.

Going to Print Predefined Region Option

  • After that, select the Open google sheets script print to pdf-predefined option under the Printed Successfully window.

Choosing Open Google Sheets Script Print to PDF-Predefined Option

  • In the end, you’ll see that only Column B is printed in PDF format and this PDF file is saved in your Google Drive.

Output of Exporting Predefined Range to PDFRead More: How to Print Only Certain Columns in Google Sheets (3 Ways)


Conclusion

That’s all for now. Thank you for reading this article. In this article, I have discussed 4 suitable methods to print to PDF using Apps Script in Google Sheets. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our officewheel.com. Visit the site and explore more.


Related Articles

Sajid Ahmed

Sajid Ahmed

Hi! This is Sajid Ahmed. At present, I am working as an Executive, Content Development at OfficeWheel.com. Here I will be posting articles related to different problems of Google Sheets. I have completed my graduation from Bangladesh University of Engineering and Technology in Naval Architecture and Marine Engineering. Despite a different background, I have a great interest in the research and development of data science. I try to make innovative solutions to different problems regarding Google Sheets. Please follow my blog at Officewheel.com to get different solutions regarding Google Sheets.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo