CODEWITHSUNDEEP
pdfgoogle-apps-scriptgoogle-sheets
Eric K.
Eric K.

Reputation: 131

Google Apps Script: How to save (Just one) Google Sheet to Drive Folder as PDF?

Working to save just my open Google Sheet named 'POTemplate' as a PDF version of itself to a folder named 'Eric' on my Google Drive. Getting snagged on the 2nd to last line that brings an error stating the folder Iterator cannot be found. 

    function checkSheet() {
      var sheetName = "POTemplate";
      var folder = "Eric";
      var pdfName = "Sample PO "
      var sourceSpreadsheet = SpreadsheetApp.getActive();
      var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
      var folder = DriveApp.getFoldersByName(folder);
      var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy(folder));
      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];
      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);
      var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
      var newFile = folder.createFile(theBlob);
      DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

Upvotes: 1

Views: 981

Answers (1)

Jack Brown
Jack Brown

Reputation: 5892

The following function return returns a FolderIterator of folders which have the namefolder

var folder = DriveApp.getFoldersByName(folder);

To create a file in the first folder in the FolderIterator, you will have to define it specifically like so:

var newFile = folder.next().createFile(theBlob);

You can also check if the folderIterator has any folders, to begin with, like so:

if(folder.hasNext()){
  var newFile = folder.next().createFile(theBlob);
} else {
  DriveApp.createFolder("Eric").createFile(theBlob)
}

Code Improvements:(Optional) The following lines code, doesn't seem to have any purpose

      var destSheet = destSpreadsheet.getSheets()[0];
      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);

Since you are making a copy of your original spreadsheet, your sheet "POTemplate" will be exactly the same. So, there seems to be no point copying it again.

Upvotes: 1

Related Questions