Saving a specific sheet from the workbook as a PDF

Question

I have 3 sheets in my workbook and I want to save a specific sheet as a PDF to a specific folder in my Google Drive. I have this code below. However, it is saving the entire workbook as PDF and not that specific sheet.

function checkSheet() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Tracking");
  var fldr = DriveApp.getFolderById("folder id");
  if(sh.getRange("C6").getValue() == "Yes") {
    var theBlob = ss.getBlob().getAs('application/pdf').setName("mypdf");
    fldr.createFile(theBlob);
  }
}

Tracking - is the sheet that I want to save as PDF folder id - is the id taken from the url of the folder where I want to save the PDF file

I tried looking for different syntax for getBlob and I am not able to get that specific sheet saved as a PDF. Here are is a link that I referenced.

Export Single Sheet to PDF in Apps Script

Tanaike · Accepted Answer · 2023-02-05T23:43:21.127

I thought that your referenced thread will be useful. But, from your question, I couldn't understand your tested script. So, in this case, I would like to introduce the modified script using your referenced thread.

Modified script:

function checkSheet() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Tracking");
  var fldr = DriveApp.getFolderById("folder id");
  if (sh.getRange("C6").getValue() == "Yes") {

    const sheetName = "Sheet1"; // Please set the sheet name you want to export as PDF format.
    var sheets = ss.getSheets();
    for (var i = 0; i < sheets.length; i++) {
      if (sheets[i].getSheetName() !== sheetName) {
        sheets[i].hideSheet();
      }
    }
    SpreadsheetApp.flush(); // This might not be required to be used.
    var theBlob = ss.getBlob().setName("mypdf");
    fldr.createFile(theBlob);
    for (var i = 0; i < sheets.length; i++) {
      sheets[i].showSheet();
    }

  }
}

When this script is run, only "Sheet1" is included in the exported PDF file.
By the way, in this case, when the blob is retrieved from Spreadsheet, the Spreadsheet is automatically converted to PDF format. So, in this case, var theBlob = ss.getBlob().getAs('application/pdf').setName("mypdf"); can be replaced with var theBlob = ss.getBlob().setName("mypdf");.

Note:

As another approach, in this case, when the endpoint for exporting the Spreadsheet as PDF format is used, the modified script is as follows.

function checkSheet() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Tracking");
  var fldr = DriveApp.getFolderById("folder id");
  if (sh.getRange("C6").getValue() == "Yes") {

    const sheetName = "Sheet1"; // Please set the sheet name you want to export as PDF format.
    const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&gid=${ss.getSheetByName(sheetName).getSheetId()}`;
    const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
    fldr.createFile(blob.setName("mypdf"));

  }
}

@Shan Jose In this case, if the sheet name, which is not included in the active spreadsheet, is set to `sheetName`, an error occurs for both scripts. Please be careful about this. — Tanaike, Feb 06 '23 at 00:10
| Thanks a tone. The second approach worked just fine. Really appreciate your help. — Shan Jose, Feb 06 '23 at 14:59
I have run into a 'Returned code 500' error with the script. It worked once and then it started giving me this error. I have raised another question as I did not not how to follow up on this question. The link to the new question is this : https://stackoverflow.com/questions/75388063/converting-a-specific-sheet-in-a-workbook-to-a-pdf — Shan Jose, Feb 08 '23 at 15:31

Saving a specific sheet from the workbook as a PDF

1 Answers1

Modified script:

Note: