getSheetByName function not working when creating PDF-CodePudding

I've written a function that takes five inputs from a user to create an automatic email, the email address to have the email sent to, the email subject, email body, the sheet to be converted into a PDF, and the name that will be assigned to the newly created PDF. Below is the code I've written so far:

function sendEmail(emailAddress, emailSubject, emailBody, sheetName, pdfName){
    MailApp.sendEmail(emailAddress, emailSubject, emailBody, {
      attachments: [SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getAs(MimeType.PDF).setName(pdfName)]
    })
}

When I try and send an email, however, I get the error saying that the code after 'attachments' is not a function, and I'm unsure as to why this is. Is it because I'm not able to get the sheet by its name and then convert it into a PDF?

CodePudding user response：

You can't use the .getAs() to convert sheet to PDF. You need to use UrlFetch() with URL of the Spreadsheet as https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export

Please check this snippet :

function sendEmail(emailAddress, emailSubject, emailBody, sheetName, pdfName) {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheet_id = spreadsheet.getId();
  var sheet_id = spreadsheet.getSheetByName(sheetName).getSheetId();

  let response = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/"   spreadsheet_id   "/export?format=pdf&gid="   sheet_id, {
      muteHttpExceptions: true,
      headers: {
        Authorization: 'Bearer '    ScriptApp.getOAuthToken(),
      },
    }).getBlob();


  MailApp.sendEmail(emailAddress, emailSubject, emailBody, {attachments: [response.setName(pdfName)]});
}

There is several more options you can add as parameter in the URL (format, grid,..) in order to fit your need.

You can find some relevant information here: https://spreadsheet.dev/comprehensive-guide-export-google-sheets-to-pdf-excel-csv-apps-script