Email Excel File as Attached for a Specified Google Sheet

Question

I am facing an issue in this code while sending a specific google sheet (Email) as Excel attachment.

function sendReport() {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").hideSheet();

  var message = {
    to: "email@domain.com",
    subject: "Weekly Reports",
    body: "Hi Team,\n\nPlease find attached summary reports, Thanks!\n\nThank you,\nMyNameHere",
    name: "MyNameHere",
    attachments: [SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EMAIL").getAs(MimeType.xlsx).setName("Weekly Report")]
  }
  MailApp.sendEmail(message);
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EMAIL").activate();
}

The Error I am facing is:

8:36:26 PM   Notice  Execution started
8:36:27 PM   Error   TypeError: SpreadsheetApp.getActiveSpreadsheet(...).getSheetByName(...).getAs is not a function
             sendReport   @ script.gs:10

after removing

.getSheetByName("EMAIL")

from row number 10 error goes as following:

8:38:43 PM   Notice   Execution started
8:38:43 PM   Error    Exception: Invalid argument
             sendReport   @ script.gs:10

Answer 1

The reason why you encountered such error is because there is no getAs() in Sheet object.

You can refer to this sample code on how to attached a specific sheet as an excel file in your email:

  var url = "https://docs.google.com/spreadsheets/d/"+SpreadsheetApp.getActiveSpreadsheet().getId()+"/export"+
            "?format=xlsx&"+
            "gid="+SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EMAIL").getSheetId();
                                                        
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  var blob = UrlFetchApp.fetch(url, params).getBlob().setName("Weekly Report.xlsx");

  var message = {
    to: "email@domain.com",
    subject: "Weekly Reports",
    body: "Hi Team,\n\nPlease find attached summary reports, Thanks!\n\nThank you,\nMyNameHere",
    name: "MyNameHere",
    attachments: [blob]
  }
  MailApp.sendEmail(message);

What it does?

• Export a specific sheet using an export url with xlsx as its format.
• Fetch the export url and get its blob using getBlob(), use setName(name) to rename the file. Include the file extension in the name.
• Attach the blob in your email.

Output: