Export a Google Drive spreadsheet to PDF in Google Drive in the same folder.

ascreenshot.PNG

Code.gs

// Simple function to add a menu option to the spreadsheet "Export PDF", for 
// saving a PDF of the spreadsheet directly to Google Drive.
// The exported file will be named with C5 content plus CompanyName variable plus B2 
// plus the FileExt variable and saved in the same folder as the spreadsheet.
// To change the filename, just set pdfName inside generatePdf() to something else.

// Running this saves the sheet as a PDF document

function onOpen() {
  var submenu = [{name:"Create PDF", functionName:"generatePdf"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Export', submenu);  
}

function generatePdf() {
  // Get active spreadsheet. Not sure if this line is even needed. 
  var sourceSpreadsheet = SpreadsheetApp.getActive();

  // Get first sheet... or set the number to another sheet other than the first. 
  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sheets[0].getName();  // set this to the number of the sheet you want to pdf where the first is 0
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var CompanyName = '-Your-Company-Name-';  // Set this to whatever, it will be part of the file name
  var FileExt = '.pdf';  // this sets the ext, it was missing in the first version of this script
  

  // This next line sets the file name from a couple of cells and a couple of vars previously set. 
  // C5 for this sheet is the invoice number. B2 is the client name. CompanyName and FileExt were set above.  
  // Set these to whatever makes sense for your sheet
  
  var pdfName = sourceSheet.getRange("C5").getValue() + CompanyName + sourceSheet.getRange("B2").getValue() + FileExt;

  // 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))

   var destSheet = destSpreadsheet.getSheets()[0];

  // Replace cell values with text (to avoid broken references).
  // In the original version this part was after the Delete redundant part, it created the ref errors
  
  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]);
    }
  }

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

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. You will have to click through a bunch warnings, don't worry it is all good. 
5. Reload your spreadsheet.

You should now see a new file menu option, "Export".

I made a few changes and added more notes to the code.