Sending Email of Multiple Google Sheets as Excel Attachment (As 3 x Sheets or 1 with Sub Sheets) fro-CodePudding

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)?

CodePudding user response：

The main problem with your code is that the UrlFetchApp.fetch() method doesn't take multiple URLs so you have to export each sheet separately.

There's probably a more elegant solution to combine the blobs into a single spreadsheet file but a quick fix to your code to send the three files separately would be the following:

  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, params).getBlob().setName("Orders.xlsx");
  var blob1 = UrlFetchApp.fetch(url1, params).getBlob().setName("Orders 2.xlsx");
  var blob2 = UrlFetchApp.fetch(url2, params).getBlob().setName("Orders 3.xlsx");
  var message = {
    to: "[email protected]",
    cc: "[email protected]",
    subject: "My Email Subject",
    body: "Hi Team, details....",
    name: "My Name",
    attachments: [blob, blob1, blob2]
  }
  MailApp.sendEmail(message);
}

CodePudding user response：

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);
}