Mail Google sheet as excel attachment by Google Apps Script

Question

I have a Google Spreadsheet with some different sheets. I want to send my Google Spreadsheet as excel attachment to some specific addresses in everyday morning. How can I do this easily? I have heard that I can do it by GAS

But I am very new in using Google Spreadsheet and Google Apps Script as well.

Answer 1

Hi you can try the following script

function onOpen() { 
  // Try New Google Sheets method
  try{
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('SendMail')
    .addItem('Send Report', 'getGoogleSpreadsheetAsExcel')
    .addToUi(); 
  }

  // Log the error
  catch (e){Logger.log(e)}

}

function getGoogleSpreadsheetAsExcel(){

  try {

    var ss = SpreadsheetApp.getActive();

    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";

    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob();

    blob.setName(ss.getName() + ".xlsx");

    MailApp.sendEmail("[email protected]", "Stock report of today", "The XLSX file is attached", {attachments: [blob]});

  } catch (f) {
    Logger.log(f.toString());
  }
}