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