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