To use this script in Google Drive:
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". |
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?
@LeGueuxFoudroyant : thx for your response!
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);
}`
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]);
}
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. 🙏
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]);
}
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
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.
Hi, is this script working? I used it on July, buy since August it save a .pdf that is an HTML inside. Same script.
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
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();
}
}
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
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();
}
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!
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);
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.
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!
@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);
}