Skip to content

Instantly share code, notes, and snippets.

@primaryobjects
Last active January 16, 2023 16:48
Show Gist options
  • Save primaryobjects/6370689c6f5fd3799ea53f89551eced7 to your computer and use it in GitHub Desktop.
Save primaryobjects/6370689c6f5fd3799ea53f89551eced7 to your computer and use it in GitHub Desktop.
Export a Google Drive spreadsheet to PDF in Google Drive in the same folder.

Export a Google Drive spreadsheet to PDF in Google Drive in the same folder

❤️ Sponsor This Project

Quick Start

To use this script in Google Drive:

  1. In your spreadsheet, click Tools->Script Editor.
  2. Paste the contents of Code.gs into the editor.
  3. Save.
  4. Select "Allow" when Google asks about permissions.
  5. Reload your spreadsheet.

You should now see a new file menu option, "Export".

// Simple function to add a menu option to the spreadsheet "Export", for saving a PDF of the spreadsheet directly to Google Drive.
// The exported file will be named: SheetName and saved in the same folder as the spreadsheet.
// To change the filename, just set pdfName inside generatePdf() to something else.
// Running this, sends the currently open sheet, as a PDF attachment
function onOpen() {
var submenu = [{name:"Save PDF", functionName:"generatePdf"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu('Export', submenu);
}
function generatePdf() {
// Get active spreadsheet.
var sourceSpreadsheet = SpreadsheetApp.getActive();
// Get active sheet.
var sheets = sourceSpreadsheet.getSheets();
var sheetName = sourceSpreadsheet.getActiveSheet().getName();
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
// Set the output filename as SheetName.
var pdfName = sheetName;
// Get folder containing spreadsheet to save pdf in.
var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
if (parents.hasNext()) {
var folder = parents.next();
}
else {
folder = DriveApp.getRootFolder();
}
// Copy whole spreadsheet.
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))
// Delete redundant sheets.
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() != sheetName){
destSpreadsheet.deleteSheet(sheets[i]);
}
}
var destSheet = destSpreadsheet.getSheets()[0];
// Repace cell values with text (to avoid broken references).
var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
var sourcevalues = sourceRange.getValues();
var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
destRange.setValues(sourcevalues);
// Save to pdf.
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);
// Delete the temporary sheet.
DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}
To use this script in Google Drive:
1. In your spreadsheet, click Tools->Script Editor.
2. Paste the contents of Code.gs into the editor.
3. Save.
4. Select "Allow" when Google asks about permissions.
5. Reload your spreadsheet.
You should now see a new file menu option, "Export".
@WILLIAMLINN
Copy link

The PDF file is saved in the google drive without folder.
I want to save PDF in drive folder.Please help me with this.Thank!!

@J5WYATT
Copy link

J5WYATT commented Mar 29, 2019

Hi Guys,

I have tried to read all the comments to fix the code - but still getting #REF, what have I missed

// Simple function to add a menu option to the spreadsheet "Export", for saving a PDF of the spreadsheet directly to Google Drive.
// The exported file will be named: SheetName and saved in the same folder as the spreadsheet.
// To change the filename, just set pdfName inside generatePdf() to something else.

// Running this, sends the currently open sheet, as a PDF attachment
function onOpen() {
var submenu = [{name:"Save PDF", functionName:"generatePdf"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu('Export', submenu);
}

function generatePdf() {
// Get active spreadsheet.
var sourceSpreadsheet = SpreadsheetApp.getActive();

// Get active sheet.
var sheets = sourceSpreadsheet.getSheets();
var sheetName = sourceSpreadsheet.getActiveSheet().getName();
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);

// Set the output filename as SheetName.
var ssA = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ssA.getSheets()[0];
var pdfName = sheet.getRange("A1");

// Get folder containing spreadsheet to save pdf in.
var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
if (parents.hasNext()) {
var folder = parents.next();
}
else {
folder = DriveApp.getRootFolder();
}

// Copy whole spreadsheet.
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

// I tried to move this section below replace cell section as recommended - but it kept bugging on undefined variables
// Delete redundant sheets.
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() != sheetName){
destSpreadsheet.deleteSheet(sheets[i]);
}
}

var destSheet = destSpreadsheet.getSheets()[0];

// Repace cell values with text (to avoid broken references).
var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
var sourcevalues = sourceRange.getDisplayValues();
var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
destRange.setValues(destRange.getDisplayValues());

// Save to pdf.
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);

// Delete the temporary sheet.
DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

@polartar
Copy link

polartar commented Apr 1, 2019

It is useful.
But I have a question.
When I export goolesheet to pdf, can I export 50 rows per a page of pdf?.
If you have a solution, please teach me

@LeGueuxFoudroyant
Copy link

LeGueuxFoudroyant commented Apr 2, 2019

Hi,

I have a problem related to multiple pdf generation within my script.
I can generate and register a pdf file from my sheet on my drive using the following code lines :


var theBlob = Document.getBlob().getAs('application/pdf').setName("MyDocument.pdf");
var folder = DriveApp.getFolderById("FolderID");
var newFile = folder.createFile(theBlob);

These lines create a pdf file from my sheet "as it is" at the moment I start my script.

Then I took this code and puted it in a loop to generate multiple pdf files for different customer using the same sheet
Between every pdf file generation my script modifies one cell ("A1")
In other cells I have formulas refering to "A1" that should update their values.


// Access the sheet from which I generate PDF FILE
var FFACGEN= document.getSheetByName("FACGEN")

// Browsing threw a list of customer
for (var i=0;i<10;i++){
// I put the number of the customer into cell A1
FFACGEN.getRange("A1").setValue(i);

//hoping that all the formulas refering to A1 are updating

// Generate and register pdf file using FFACGEN sheet
var theBlob = Document.getBlob().getAs('application/pdf').setName("MyDocument.pdf");
var folder = DriveApp.getFolderById("FolderID");
var newFile = folder.createFile(theBlob);
}


The problem is that the sheet does not update its values before each pdf file is generate.
Result is : All my pdf file are exactly the same :

  • A1 has not been updated
  • All other formulas related to A1 have not been updated either

On the other end, i can see that A1 has been updated on my sheet.
It just does not change the content of the pdf file

Do you know anyway to "force" googlesheet to recalculate all values on a sheet before I generate the pdf file ?

Thank you for your help.
Regards.

LeGueuxF

@haiphusy
Copy link

haiphusy commented Apr 8, 2019

It is useful.
But I have two question
How to export PDF file to a specific folder and use Google spread file name instead of folder name?

@chirag1855
Copy link

Hi,

I am getting this error "You cannot delete a sheet with a linked form. Please unlink the form first."
Kindly help.

@controllerdiego
Copy link

controllerdiego commented May 8, 2019

Hello! When I save a spreadsheet that contains formulas, it saves in blank, without the content generated by the formulas. How can I solve this?

@romainb75
Copy link

@LeGueuxFoudroyant : +1: > Do you know anyway to "force" googlesheet to recalculate all values on a sheet before I generate the pdf file ?

I face the same problem... Did you succeed to solve it ?
I would be very interested!
Thx

@LeGueuxFoudroyant
Copy link

@romainb75
Hi,

Yes I finally succeeded but I forgot to post the answer, thank you for you heads-up.
Before I generate each PDF file, I force googlesheet to update by making a new getrange on the whole sheet.

Using the same previous example I have the following code :


// Access the sheet from which I generate PDF FILE
var FFACGEN= document.getSheetByName("FACGEN")

// Browsing threw a list of customer
for (var i=0;i<10;i++){
// I put the number of the customer into cell A1
FFACGEN.getRange("A1").setValue(i);

// Force the data in the sheet to update before generating the PDF file :
FFACGEN.getRange(1,1,FFACGEN.getMaxRows(),FFACGEN.getMaxColumns()).getValues();

// Generate and register pdf file using FFACGEN sheet
var theBlob = Document.getBlob().getAs('application/pdf').setName("MyDocument.pdf");
var folder = DriveApp.getFolderById("FolderID");
var newFile = folder.createFile(theBlob);
}


@rcarrolldegcv
Copy link

This is so helpful.
Is there a way to have the script take all the sheets and export them to individual PDFs in the folder, rather than doing all of them separately?

@romainb75
Copy link

@LeGueuxFoudroyant : thx for your response!

@Wgalter1
Copy link

Wgalter1 commented Oct 7, 2019

Hello,
I've taking the liberty to modify @primaryobjects script(very nice work indeed) a little, in order to avoid the REF issues mentioned.
Just an idea!
`function generatePdf() {
var ss, source, newTab, newSheet;

source = SpreadsheetApp.openById("10wh47mXtvXEdfgdfgdfgdfgdfgdfg").getSheetByName('PrintFATURAFINAL');
ss = SpreadsheetApp.openById("10wh47mXtvXEVUHdfgdfgdfgdfgdfgdfgd");
var pdfName = source.getRange('E10').getDisplayValue();
/*By using the value on a cell as the name for the pdf file you can dynamically change it to be display as a distinct list with no repetition.
ei: client A.pdf
client B.pdf
,etc...
*/
newTab = source.copyTo(ss) // copy sheet to the same spreadsheet
newTab.getDataRange().setValues(newTab.getDataRange().getDisplayValues())
newSheet = SpreadsheetApp.create('Temp');
newTab.copyTo(newSheet);
newSheet.deleteSheet(newSheet.getSheets()[0]);

var folderId = '16ZABtdfgdfgdfgdfgdfgdfgdfgdfg';
var folder = DriveApp.getFolderById(folderId);

// Save to pdf.
var theBlob = newSheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);

var delet = ss.deleteSheet(newTab);
DriveApp.getFileById(newSheet.getId()).setTrashed(true);

}`

@Morecoffee
Copy link

For those getting the reference error as previously mentioned you need to move the 'Replace values with text section' to be before the ' Delete redundant sheets' section... because if the sheets are deleted prior to the 'getDisplayvalues' the references will be broken. I just thought I would clarify this as it took a bit for the previous reference to it to sink into my brain.

// Replace cell values with text (to avoid broken references).
var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
var sourcevalues = sourceRange.getDisplayValues();
var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
destRange.setValues(sourcevalues);

// Delete redundant sheets.
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() != sheetName){
destSpreadsheet.deleteSheet(sheets[i]);
}

@jadieljunior
Copy link

Hello, good night, guys.

I'm using Google Translate, sorry if you leave somewhere, it won't be my fault.

I would like to know how do I save the PDF file to a specific Google Drive folder. This spreadsheet is shared in my work. I think the file would be saved only in the respective folders of each account - separately (am I right?). I need to avoid this. PDFs must be saved to a folder in my account. The folder in Drive is already properly "open" for the link owner.

So how do I save the PDF file to a specific folder in Google Drive, with everyone using the spreadsheet also saving to that same folder?

Thanks if you can help me. 🙏

@ImranKasuri55
Copy link

ImranKasuri55 commented Jan 14, 2020

May Sheet is Connact with google form so Msg is Show( plz unlink form firest for Delete redundant sheets.) how to solve this problem
// Delete redundant sheets.
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() != sheetName){
destSpreadsheet.deleteSheet(sheets[i]);
}

@doubleup99
Copy link

I run this code and an error message " We're sorry, a server error occured. Please wait a bit and try again.(line 14,file "Code")"

Thanks in advance if you can help

@joussenmatias
Copy link

Hello guys!
Thanks for the code, updates and commits.

In my case, I cannot run the script because there are several cells with dropdown list values. How could I change the script in order to fix this issue? I tried, but I didn't find a solution.

Thanks in advance for your help.

@gastonkrasny
Copy link

Hi, is this script working? I used it on July, buy since August it save a .pdf that is an HTML inside. Same script.

@ray595
Copy link

ray595 commented Mar 4, 2021

Hello Guys,
I tried this script but always get this error message.
Exception: Unexpected error while getting the method or property getFolderById on object DriveApp.
generatePdf @ pdf.gs:19

I am trying to convert a single sheet from my spreadsheet to pdf in my google drive folder.
Is there any solution, Please Help

My script as follows

unction generatePdf() {
var ss, source, newTab, newSheet;

source = SpreadsheetApp.openById("1Osheet ID").getSheetByName('Orderform');
ss = SpreadsheetApp.openById("1OMy sheet ID");
var pdfName = source.getRange('G3').getDisplayValue();
/*By using the value on a cell as the name for the pdf file you can dynamically change it to be display as a distinct list with no repetition.
ei: client A.pdf
client B.pdf
,etc...
*/
newTab = source.copyTo(ss) // copy sheet to the same spreadsheet
newTab.getDataRange().setValues(newTab.getDataRange().getDisplayValues())
newSheet = SpreadsheetApp.create('Temp');
newTab.copyTo(newSheet);
newSheet.deleteSheet(newSheet.getSheets()[0]);

var folderId = 'https://drive.google.com/drive/folders/MY folder ID?usp=sharing';
var folder = DriveApp.getFolderById(folderId);

// Save to pdf.
var theBlob = newSheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);

var delet = ss.deleteSheet(newTab);
DriveApp.getFileById(newSheet.getId()).setTrashed(true);

}

regards

@heckmac
Copy link

heckmac commented Mar 17, 2021

Hi, very nice script! i have a small question. I have a spreadsheet with 7 sheets. I want to print the 2nd.
Two things happens. first, the file comes out without a .pdf suffix, secondly i get #REF! errors on all lines referencing another sheet..

Is it because its deleting the scripts deletes reduntant sheets before replacing cell values or am i doing something wrong?

Much appreciated!

Yes indeed. I suggest using hideSheet() instead of deleting it. In this way, you avoid having issues with charts that make use of data in other sheets!

// Hide redundant sheets.
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() != sheetName){
sheets[i].hideSheet();
}
}

@klk1996
Copy link

klk1996 commented May 9, 2021

can it be written for automatic download sheet as pdf into pc as series of time frame like ex: daily at 9:30 downloading data as a pdf file . plz help

@primaryobjects
Copy link
Author

primaryobjects commented May 9, 2021

You could try setting a time-scheduled task to run the script at date/time intervals.

function createTimeDrivenTriggers() {
  // Trigger every 6 hours.
  ScriptApp.newTrigger('getEmails')
      .timeBased()
      .everyHours(1)
      .create();
}

@pratikshaM2
Copy link

pratikshaM2 commented May 19, 2021

In my case the pdf is not generated only spreadsheet is generated and the file name is temp_convert_to_pdf
What went wrong?

Sorry, there was a data validation error in my sheet.
It is resolved and now its working

Thanks a lot!

@Landracebio
Copy link

Finally a PDF menu button. Thank You!!!

Two questions. How can I save the PDF by the name of a value in a cell. For example, a sales order number that changes. I have attached a tally code to change the number of the SO#in the cell (G5) every time the "create PDF" is pressed and I would like to save each PDF by that SO#. Which brings me to the second question. How can I set this tally code up in a different spread sheet (a data sheet) and the script reference it to change (G5). For example, G5 (sheet 1) = C1(data spreadsheet) where C1 is the tally. Here is what I am currently using.

SpreadsheetApp.getActiveSheet().getRange('G5').setValue(SpreadsheetApp.getActiveSheet().getRange('G5').getValue() + 1);

@Landracebio
Copy link

Landracebio commented Sep 8, 2021

Finally a PDF menu button. Thank You!!!

Two questions. How can I save the PDF by the name of a value in a cell. For example, a sales order number that changes. I have attached a tally code to change the number of the SO#in the cell (G5) every time the "create PDF" is pressed and I would like to save each PDF by that SO#. Which brings me to the second question. How can I set this tally code up in a different spread sheet (a data sheet) and the script reference it to change (G5). For example, G5 (sheet 1) = C1(data spreadsheet) where C1 is the tally. Here is what I am currently using.

SpreadsheetApp.getActiveSheet().getRange('G5').setValue(SpreadsheetApp.getActiveSheet().getRange('G5').getValue() + 1);

Figured out the tally from another spreadsheet but still trying to figure out how to save the PDF based on information in a cell on the same sheet and not the sheet name.

@Ram59-canidium
Copy link

Thank you for the code! I want to save a specific range of my visible sheet as PDF. Is there anything that I can add to this code to get a pdf of the desired range? Please help!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment