Skip to content

Instantly share code, notes, and snippets.

@primaryobjects

primaryobjects/Code.gs

Last active January 16, 2023 16:48
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save primaryobjects/6370689c6f5fd3799ea53f89551eced7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save primaryobjects/6370689c6f5fd3799ea53f89551eced7 to your computer and use it in GitHub Desktop.
Download ZIP
Export a Google Drive spreadsheet to PDF in Google Drive in the same folder.
Raw
readme.md

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".

Raw
Code.gs
// 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);
}
Raw
install.txt
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".
@derekantrican
Copy link

derekantrican commented Jan 31, 2019

If you add the code below at line 50 (after replacing cell values with text) then it will also remove all the notes on the temporary spreadsheet (otherwise the notes will show up on a separate page of the pdf)

//Replace all notes (otherwise they will show up on the pdf)
var emptyArray = [];
for (var i = 0; i < sheet.getMaxRows(); i++){
  var rowArray = [];
  for (var j = 0; j < sheet.getMaxColumns(); j++)
    rowArray.push("");
    
  emptyArray.push(rowArray);
}
    
destRange.setNotes(emptyArray);

@Sarrah-Bny
Copy link

Sarrah-Bny commented Feb 21, 2019

Hi all,
thanks a lot for the script and additional info! I am new to script and would appreciate your help in adapting this script to create the PDF from selected cells as opposed to the whole sheet. Many thanks!!

@WILLIAMLINN
Copy link

WILLIAMLINN commented Mar 26, 2019

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 •
edited
Loading

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);
}

@aristidesgpg
Copy link

aristidesgpg 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 •
edited
Loading

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 •
edited
Loading

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

chirag1855 commented Apr 13, 2019

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 •
edited
Loading

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

romainb75 commented Aug 14, 2019

@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

LeGueuxFoudroyant commented Aug 14, 2019

@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

rcarrolldegcv commented Aug 17, 2019

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

romainb75 commented Sep 17, 2019

@LeGueuxFoudroyant : thx for your response!

@Wgalter1
Copy link

Wgalter1 commented Oct 7, 2019 •
edited
Loading

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

Morecoffee commented Oct 10, 2019

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]);
}

Copy link

ghost commented Nov 10, 2019

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 •
edited
Loading

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

doubleup99 commented Apr 27, 2020

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

joussenmatias commented May 5, 2020

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

gastonkrasny commented Oct 15, 2020

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 •
edited
Loading

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 •
edited
Loading

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

Landracebio commented Sep 3, 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);

@Landracebio
Copy link

Landracebio commented Sep 8, 2021 •
edited
Loading

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

Ram59-canidium commented Dec 15, 2021

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