Simply copy the script into the script editor (Tools > Script editor…) of the GSheet in question, save it, refresh the sheet and use the new custom menu option (Create PDF > Create PDF).
It is also possible to call the script for another spreadsheet, specify just the one tab to be exported and specify an email address to send it to.
Here is a list of all the options I could find for exporting a PDF from a Google spreadsheet.
Further to user requests, I have created many variations on the PDF creation theme.
Get in touch now if you need any help with this or any other GAS scripts or have a look through some other free scripts and snippets.
This is such a wonderful script! Is there a way to narrow it down even further by having a PDF generated only up to where cells contain value, instead of the whole sheet which may contain an ungodly amount of blank rows?
This script just does a straight conversion of what it finds. You’d need another script to open the sheet and remove all the blank lines if you didn’t want to see them in the PDF.
Can you explain what lines 34 through 36 do? Thanks.
They define an object that is sent to the sheet’s URL in line 40 to provide authorisation for the conversion.
Hi – I think this script is amazing – I’m a bit of a app-script newcomer! How would you tweak it to include some of the sheets – but not all?
Hi Diane, Glad you found it useful. To just send individual sheets you’d need to create an array of sheet names and pass them to the function one by one, I’ve not tested this but try:
var sheetNames = [‘Sheet Name 1’, ‘Sheet Name 4’, ‘Sheet Name 5’]
sheetNames.forEach(function(sheetName) {
convertSpreadsheetToPdf(TEST_EMAIL, null, sheetName)
})
How would I send email to multiple recipients, using cc and/or bcc?
Either would work.
Hi! If someone is also looking for a solution how to get multiple sheets into one pdf file then anwer is hiding the sheets.
You just hide the sheets not needed in pdf and run the script (without specifing sheetName variable)
Keep only needed sheets visible:
function hideSheets() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var visible = [“Sheet 1”, “Sheet 2”]; //sheet names
sheets.forEach(function(sheet) {
if (visible.indexOf(sheet.getName()) == -1) {
sheet.hideSheet();
}
})
};
Show all sheets:
function showSheets() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
sheet.showSheet()
})
};
Hey! Is there a way of saving a multi-sheet workbook as individual PDFs with a consistent name format which includes the sheet name?
I’m afraid this is the only script I’ve got multi-sheet workbooks.
This is great – thanks!
I’m wondering if there’s any way I can tweak the script so that the column width is kept on the PDF? Mine seems to be resetting all columns to equal width 🙁
Thanks again!
Not that I know of I’m afraid.
Great script – thanks. Stupid question, but where/how do I define the parameters ie email address, pdfName etc?
If you take a look at the bottom of the script there are examples of specifying these.
Is there a way to exclude some workbooks when converting the entire spreadsheet to pdf?
Not that I know of I’m afraid.
Hey, How I can repeat row headers (frozen rows) on each page?
How I cansome save some PDF file to multi-folder.
Hi Andrew great script just wondering using your answer to Diane to send
multiple pages to the PDF using this code
var sheetNames = [‘Sheet Name 1’, ‘Sheet Name 4’, ‘Sheet Name 5’]
sheetNames.forEach(function(sheetName) {
convertSpreadsheetToPdf(TEST_EMAIL, null, sheetName)
})
I get an error
Cannot call method “getSheetId” of null. (line 16, file “test”)
any thought on how it could be fix
It’s saying you are not passing in an existing list of tab names.
I have the same issue, trying to send two tabs but Iget is error: TypeError: Cannot call method “getSheetId” of null. (line 16, file “newEmail”)
Hello Andrew – this seems like the very script I need to use but I am lost as to which bits I edit/change to make it work for my spreadsheet. Would you be able please to give me some pointers
There’s a test() function at the bottom of the Gist you can copy and add your own ID. You shouldn’t need to change anything in convertSpreadsheetToPdf() itself.
Andrew, thanks you for sharing the script. How to define the specific range from the sheet to be printed to pdf?
The best script to start with for that would probably be https://www.andrewroberts.net/2016/01/google-apps-script-to-create-and-email-a-pdf/. This converts a whole row, so you’d need to replace the relevant code with a getRange().
Hi Andrew,
I’m getting the following error when I try out your script:
You do not have permission to call DriveApp.getFileById. Required permissions: https://www.googleapis.com/auth/drive
Could you please point me in the right direction for troubleshooting this?
Check the ID of the template GDoc that you are using and that you actually have access to it.