Sending Email of Multiple Google Sheets as Excel Attachment (As 3 x Sheets or 1 with Sub Sheets) from App Script

Question

I am trying to send 3 x Sheets from a Google Sheet using App Script. Following code is working perfectly for 1 Sheet, but when i try to sent 3 Sheets, it not working.

function sendExcel() {
  var url = "https://docs.google.com/spreadsheets/d/"+SpreadsheetApp.getActiveSpreadsheet().getId()+"/export"+"?format=xlsx&"+"gid="+SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getSheetId();
  var url1 = "https://docs.google.com/spreadsheets/d/"+SpreadsheetApp.getActiveSpreadsheet().getId()+"/export"+"?format=xlsx&"+"gid="+SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getSheetId();
  var url2 = "https://docs.google.com/spreadsheets/d/"+SpreadsheetApp.getActiveSpreadsheet().getId()+"/export"+"?format=xlsx&"+"gid="+SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3").getSheetId();

  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  var blob = UrlFetchApp.fetch(url, url1, url2, params).getBlob().setName("Orders.xlsx");
  var message = {
    to: "[email protected]",
    cc: "[email protected]",
    subject: "My Email Subject",
    body: "Hi Team, details....",
    name: "My Name",
    attachments: [blob]
  }
  MailApp.sendEmail(message);
}

Can someone please guide me on correct code for sending 3 / multiple sheets as attachment (As 1 Excel file or 3x Separate Attached Excel Files)?

Answer 1

I think this will send 3 separate messages each with one attachment:

function sendThree() {
  var url0 = "https://docs.google.com/spreadsheets/d/" + SpreadsheetApp.getActiveSpreadsheet().getId() + "/export" + "?format=xlsx&" + "gid=" + SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getSheetId();
  var url1 = "https://docs.google.com/spreadsheets/d/" + SpreadsheetApp.getActiveSpreadsheet().getId() + "/export" + "?format=xlsx&" + "gid=" + SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getSheetId();
  var url2 = "https://docs.google.com/spreadsheets/d/" + SpreadsheetApp.getActiveSpreadsheet().getId() + "/export" + "?format=xlsx&" + "gid=" + SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3").getSheetId();
  
  [url0, url1, url2].forEach((url,i) => {
    var params = { method: "GET", headers: { "authorization": "Bearer " + ScriptApp.getOAuthToken() } };
    var blob = UrlFetchApp.fetch(url,params).getBlob().setName(`Orders${i+1}.xlsx`);
    var message = {
      to: "[email protected]",
      cc: "[email protected]",
      subject: "My Email Subject",
      body: "Hi Team, details....",
      name: "My Name",
      attachments: [blob]
    }
    MailApp.sendEmail(message);
  });
}

I think this will send one email with three attachmentments:

function sendOneEmailWithThreeAttachments() {
  var url0 = "https://docs.google.com/spreadsheets/d/" + SpreadsheetApp.getActiveSpreadsheet().getId() + "/export" + "?format=xlsx&" + "gid=" + SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getSheetId();
  var url1 = "https://docs.google.com/spreadsheets/d/" + SpreadsheetApp.getActiveSpreadsheet().getId() + "/export" + "?format=xlsx&" + "gid=" + SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getSheetId();
  var url2 = "https://docs.google.com/spreadsheets/d/" + SpreadsheetApp.getActiveSpreadsheet().getId() + "/export" + "?format=xlsx&" + "gid=" + SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3").getSheetId();
  var urlA = [];
  [url0, url1, url2].forEach((url, i) => {
    var params = { method: "GET", headers: { "authorization": "Bearer " + ScriptApp.getOAuthToken() } };
    urlA.push(UrlFetchApp.fetch(url, params).getBlob().setName(`Orders${i + 1}.xlsx`));
  });
  var message = {
    to: "[email protected]",
    cc: "[email protected]",
    subject: "My Email Subject",
    body: "Hi Team, details....",
    name: "My Name",
    attachments: urlA
  }
  MailApp.sendEmail(message);
}