Sajid Ahmed
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.
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.
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:
- 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.
- 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.
- After that, you’ll find a message like below titled Printed Successfully.
- Consequently, click on the Open Printing Current Sheet option.
- Finally, you’ll get your desired PDF file automatically saved in your Google Drive.
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.
- 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.
- 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.
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.
- 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.
- 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.
Read More: How to Print Google Sheets on One Page (2 Distinct Scenarios)
Similar Readings
- How to Print Header on Each Page in Google Sheets
- Print Preview in Google Sheets (2 Easy Examples)
- How to Print Mailing Labels from Google Sheets (With Easy Steps)
- Print in Landscape in Google Sheets (2 Simple Ways)
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.
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.
- After that, you’ll see a window called Printed Successfully.
- Consequently, click on the Open google sheets script print to pdf-selected button.
- 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.
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.
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.
- 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.
- Moreover, you’ll see that a range is defined. Now we’ll print this predefined region in PDF format.
- For that purpose, go to Extensions > Print to PDF > Print predefined region.
- After that, select the Open google sheets script print to pdf-predefined option under the Printed Successfully window.
- 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.
Read 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.
