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". |
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?
Hi,
I am getting this error "You cannot delete a sheet with a linked form. Please unlink the form first."
Kindly help.
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?
@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
@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);
}
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!
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 :
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);
// 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 :
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