pdfgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api

Export Single Sheet to PDF in Apps Script


I have many sheets in a Spreadsheet. I have 2 sheets that I would like to export every time. Sheet One is a sheet with the information. Sheet Two is the sheet I need to give to customers and it takes references from Sheet One.

Currently, I create a new temporary spreadsheet, copy Sheet One to the new spreadsheet, then copy Sheet Two to the temporary spreadsheet. After, I convert the temporary spreadsheet to a pdf. Then I delete the temporary spreadsheet and save the pdf into a folder in Google Drive.

The PDF created contains 2 pages of both sheets. I only need Sheet Two. If I only transfer Sheet Two, the sheet is left with many #REF errors since Sheet One isn't there. Is there any way to export only Sheet Two without have the #REF errors?

Here is my code below:

//create a temporary spreadsheet, copy both files onto it
var newSpreadsheet = SpreadsheetApp.create(nameOfSheet);
var d = ss.getSheetByName('Data').copyTo(newSpreadsheet); //the sheet     with all the information
d.setName('Data');

sheetToCopy.copyTo(newSpreadsheet); //the sheet that uses references from the data sheet
newSpreadsheet.deleteSheet(newSpreadsheet.getSheetByName("Sheet1")); //delete the original sheet of the new spreadsheet
var blobOfNewSpreadsheet = newSpreadsheet.getBlob(); //create pdf
folder.createFile(blobOfNewSpreadsheet); //add to folder


//delete the temporary spreadsheet2
var deleteSpreadsheet = DriveApp.getFileById(newSpreadsheet.getId());
deleteSpreadsheet.setTrashed(true);

Solution

  • Hidden sheets are not included when a spreadsheet is exported via getBlob. So you can temporarily hide any unwanted sheets prior to exporting.

    function export() {    
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName('Unwanted Sheet');
      sheet.hideSheet();
      DriveApp.createFile(ss.getBlob());
      sheet.showSheet();
    }
    

    The above only hides one sheet, which is enough in the context of your question. Here is a version that hides everything but one.

    function exportSheet(sheetName) {    
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheets = ss.getSheets();
      for (var i = 0; i < sheets.length; i++) {
        if (sheets[i].getSheetName() !== sheetName) {
          sheets[i].hideSheet()
        }
      }
      DriveApp.createFile(ss.getBlob());
      for (var i = 0; i < sheets.length; i++) {
        sheets[i].showSheet()
      }
    }