Google App Script - Save Spreadsheet to PDF saved on Google Drive

Question

I'm trying to save all of the sheets on my spreadsheet to google drive as one PDF (ultimately I would like to have them email as well). I'm having trouble saving more than just one of the sheets. I've tried multiple way of doing it. The code below is the best way I've found so far. Again the problem is that it only saves the first page as a PDF, I cant figure out how to get around the delete redundant sheets. All the posts I have seen only want to save 1 page, I have over 24 pages that need to be saved as one PDF. Thanks in advance for your help!

function PDF() {
  var sheetName = SpreadsheetApp.getActiveSpreadsheet();
  var folderID = "*** Google Drive ID***"; // Folder id to save in a Drive folder.
  var ss = SpreadsheetApp.openByUrl(
     'https://docs.google.com/spreadsheets/d/***Spreadsheet ID***'); 
  var pdfName = "MAR -  " + ss.getRange("A1:A1").getValue(); //Need to set the values to another sheet

  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID);

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

Answer 1

Here is the function that creates PDF file from google sheet and function that moves new created file into the folder that id you give to the parameter of the function.

function downloadPDF(fileId, folderId) {
  var file = Drive.Files.get(fileId);
  var url = file.exportLinks[MimeType.PDF];

  var options = {
    headers: {
      Authorization:"Bearer " + ScriptApp.getOAuthToken()
    },
    muteHttpExceptions : true 
  }

  var response = UrlFetchApp.fetch(url, options);
  var status = response.getResponseCode();
  var result = response.getContentText();
  if (status != 200) {
    // Get additional error message info, depending on format
    if (result.toUpperCase().indexOf("<HTML") !== -1) {
      var message = strip_tags(result);
    }
    else if (result.indexOf('errors') != -1) {
      message = JSON.parse(result).error.message;
    }
    throw new Error('Error (' + status + ") " + message );
  }

  var doc = response.getBlob();
  var newFileid = DriveApp.createFile(doc).setName(file.title + '.pdf').getId();
  let id = moveFileTo(newFileid, folderId);
 
  return id;
}


function moveFileTo(sourceId, folderId){
  let file = DriveApp.getFileById(sourceId)
  let blob = file.getBlob();
  let id = DriveApp.getFolderById(folderId).createFile(blob).getId();
      file.setTrashed(true)
      return id;
}

Answer 2

By using Drive API, you can convert from a spreadsheet to a PDF which has all sheets in the spreadsheet. In order to use this, so please enable Drive API on Google API Console as follows.

1. In the script editor, select Resources > Cloud Platform Project

2. At the bottom of the dialog, click the link for the Google API Console.

3. In the console, click into the filter box and type part of the name of the API "Drive API", then click the name once you see it.

4. On the next screen, click Enable API.

5. Close the Developers Console and return to the script editor. Click OK in the dialog.

I prepared a sample script for creating PDF file from spreadsheet. Please use this to your script.

Script :

var spreadsheetId = "#####";
var folderId = "#####";
var outputFilename = "#####";

var url = "https://www.googleapis.com/drive/v3/files/" + spreadsheetId + "/export?mimeType=application/pdf";
var options = {
    method: "GET",
    headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
};
var response = UrlFetchApp.fetch(url, options).getBlob();
DriveApp.getFolderById(folderId).createFile(response).setName(outputFilename);

About this script, although I confirmed this works fine, if it doesn't work at your environment, please tell me. And if I misunderstand your question, I'm sorry.

Added 1 :

function PDF() {
  var sheetName = SpreadsheetApp.getActiveSpreadsheet();
  var folderID = "*** Google Drive ID***"; // Folder id to save in a Drive folder.
  var ss = SpreadsheetApp.openByUrl(
     'https://docs.google.com/spreadsheets/d/***Spreadsheet ID***'); 
  var pdfName = "MAR -  " + ss.getRange("A1:A1").getValue(); //Need to set the values to another sheet

  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID);

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


  // A sample script was added here.
  var url = "https://www.googleapis.com/drive/v3/files/" + destSpreadsheet.getId() + "/export?mimeType=application/pdf";
  var options = {
      method: "GET",
      headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
  };
  var response = UrlFetchApp.fetch(url, options).getBlob();
  DriveApp.getFolderById(folderID).createFile(response).setName(pdfName);


  //Delete the temporary sheet
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

Added 2 :

function PDF() {
  var folderID = "*** Google Drive ID***"; // Folder id to save in a Drive folder.
  var ss = SpreadsheetApp.openByUrl(
     'https://docs.google.com/spreadsheets/d/***Spreadsheet ID***'); 
  var pdfName = "MAR -  " + ss.getRange("A1:A1").getValue(); //Need to set the values to another sheet
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var folder = DriveApp.getFolderById(folderID);

  // A sample script was added here.
  var url = "https://www.googleapis.com/drive/v3/files/" + sourceSpreadsheet.getId() + "/export?mimeType=application/pdf";
  var options = {
      method: "GET",
      headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
  };
  var response = UrlFetchApp.fetch(url, options).getBlob();
  DriveApp.getFolderById(folderID).createFile(response).setName(pdfName);
}