google-sheetsgoogle-apps-scriptgoogle-drive-apipdf-generation

Downloading a PDF version of the open spreadsheet in Google Drive via scripts


I've been reading up on how to save a spreadsheet to PDF via Google Docs Scripting. Most suggestions I've come across reference using something like:

theOutputFile.saveAndClose();
DocsList.createFile(theOutputFile.getAs('application/pdf')).rename(theOutputName+".pdf");

That is, they reference the saveAndClose() function. I don't want to save or close my spreadsheet - but I do want to download the current sheet as a PDF.


Solution

  • For saving the current sheet as a PDF, you can hide all the other sheets, save the current, & then show all sheets again. The pdf creation might start before the end of the sheets' hiding and then will include 2 sheets - the current & the last sheets - in the pdf file. Adding a sleep or a confirmation msgbox, between showOneSheet & createPdf eliminated the problem.

    This answer is a variation of Marco Zoqui's answer: "To send a single sheet you may hide all other before sending" in Google Apps Script to Email Active Spreadsheet

    var sheet = SpreadsheetApp.getActiveSheet();
    var sheetToSave = sheet.getName();
    
    showOneSheet(sheetToSave);
    Utilities.sleep(2000);
    createPdf("TestFolder", "TestPDF");
    showAllSheets();  
    
    function showOneSheet(SheetToShow) {
      var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
      for(var i in sheets){
        if (sheets[i].getName()==SheetToShow){
          sheets[i].showSheet();
        }
        else {
          sheets[i].hideSheet();
        }
      }
    }
    
    function showAllSheets() {
      var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
      for(var i in sheets){
          sheets[i].showSheet();
      }
    }
    
    function createPdf(saveToFolder, fileName){
    
      var ssa = SpreadsheetApp.getActiveSpreadsheet();
      var pdf = ssa.getAs("application/pdf"); 
      try {
        var folder = DocsList.getFolder(saveToFolder);
      }
      //Create Folder if not exists
      catch(error){
        folder = DocsList.createFolder(saveToFolder);
      }
      var file = folder.createFile(pdf);
      file.rename(fileName);
    
      return file;
    }