Create Google Script that saves sheet as pdf to specified folder in google drive

Question

So thanks to other members I was able to piece together a script that does what I need except for 1 small hiccup. Rather than saving the single sheet as a pdf in the designated folder, it saves the entire workbook. I've tried multiple variations to get it to save just the sheet I want (named JSA) but it either gives me an error message or continues to download the whole spreadsheet. I'm sure it's something simple I'm missing, but I'm not as versed with Script as I am with writing Macros, so I'm stuck. The first part of the code, downloading it as a PDF works like a charm, it's the last section where I want it to save the PDF to a specified folder that's the issue. Any assistance would be appreciated.

function downloadPdf() {
  var ss = SpreadsheetApp.getActive(),
    id = ss.getId(),
    sht = ss.getActiveSheet(),
    shtId = sht.getSheetId(),
    url =
      'https://docs.google.com/spreadsheets/d/' +
      id +
      '/export' +
      '?format=pdf&gid=' +
      shtId
    + '&size=letter' // paper size legal / letter / A4
    + '&portrait=false' // orientation, false for landscape
    + '&scale=2'  // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
    + '&fitw=true'                   // fit to width, false for actual size
    + '&top_margin=0.25'              // All four margins must be set!
    + '&bottom_margin=0.25'           // All four margins must be set!
    + '&left_margin=0.25'             // All four margins must be set!
    + '&right_margin=0.25'            // All four margins must be set!
    + '&sheetnames=false&printtitle=false' // hide optional headers and footers
    + '&pagenum=false&gridlines=false' // hide page numbers and gridlines
    + '&fzr=false' // do not repeat row headers (frozen rows) on each page
    + '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
    + '&vertical_alignment=TOP'; //TOP/MIDDLE/
  
  var val = SpreadsheetApp.getActive().getRange('H2').getValues();//custom pdf name here 
  var val2= Utilities.formatDate(SpreadsheetApp.getActive().getRange("N2").getValue(), ss.getSpreadsheetTimeZone(), "MM/dd/YY");
  var val3= " - "
  val += val3 += val2 += '.pdf';
  //can't download with a different filename directly from server
  //download and remove content-disposition header and serve as a dataURI
  //Use anchor tag's download attribute to provide a custom filename
  var res = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
  });
  SpreadsheetApp.getUi().showModelessDialog(
    HtmlService.createHtmlOutput(
      '<a target ="_blank" download="' +
        val +
        '" href = "data:application/pdf;base64,' +
        Utilities.base64Encode(res.getContent()) +
        '">Click here</a> to download, if download did not start automatically' +
        '<script> \
        var a = document.querySelector("a"); \
        a.addEventListener("click",()=>{setTimeout(google.script.host.close,10)}); \
        a.click(); \
        </script>'
    ).setHeight(50),
    'Downloading PDF..'
  );

  const folderName = `Test`;
  const fileNamePrefix =  val += val3 += val2 += '.pdf';
  var JSA = SpreadsheetApp.getActiveSpreadsheet();
  var s = JSA.getSheetByName("JSA");

  DriveApp.getFoldersByName(folderName)
    .next()
    .createFile(SpreadsheetApp.getActiveSpreadsheet()
                  .getBlob()
                  .getAs(`application/pdf`)
                  .setName(`${fileNamePrefix}`));                    
}

Answer 1

I believe your goal is as follows.

• You want to create a PDF file, which is downloaded using the first part of your script, in the specific folder on Google Drive.

In this case, how about the following modification? I thought that res might be able to be used.

From:

var JSA = SpreadsheetApp.getActiveSpreadsheet();
var s = JSA.getSheetByName("JSA");

DriveApp.getFoldersByName(folderName)
  .next()
  .createFile(SpreadsheetApp.getActiveSpreadsheet()
                .getBlob()
                .getAs(`application/pdf`)
                .setName(`${fileNamePrefix}`)); 

To:

DriveApp.getFoldersByName(folderName)
  .next()
  .createFile(res.getBlob().setName(`${fileNamePrefix}`));

• In this modification, it supposes that sht = ss.getActiveSheet(), is JSA sheet. If the active sheet is not JSA sheet, please modify as follows.

  • To:

      const url2 =
        'https://docs.google.com/spreadsheets/d/' +
        id +
        '/export' +
        '?format=pdf&gid=' +
        ss.getSheetByName("JSA").getSheetId()
        + '&size=letter' // paper size legal / letter / A4
        + '&portrait=false' // orientation, false for landscape
        + '&scale=2'  // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
        + '&fitw=true'                   // fit to width, false for actual size
        + '&top_margin=0.25'              // All four margins must be set!
        + '&bottom_margin=0.25'           // All four margins must be set!
        + '&left_margin=0.25'             // All four margins must be set!
        + '&right_margin=0.25'            // All four margins must be set!
        + '&sheetnames=false&printtitle=false' // hide optional headers and footers
        + '&pagenum=false&gridlines=false' // hide page numbers and gridlines
        + '&fzr=false' // do not repeat row headers (frozen rows) on each page
        + '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
        + '&vertical_alignment=TOP'; //TOP/MIDDLE/
      const res2 = UrlFetchApp.fetch(url2, {
        headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
      });
      DriveApp.getFoldersByName(folderName)
        .next()
        .createFile(res2.getBlob().setName(`${fileNamePrefix}`));
    

Reference:

• fetch(url, params)