CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Atif Qayyum
Atif Qayyum

Reputation: 57

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

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

Upvotes: 0

Views: 1091

Answers (3)

Arkkad3
Arkkad3

Reputation: 11

This one works with scheduler.

function sendMonthlyEmailWithAttachments() {
  var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheets = ["Sheet1", "Sheet2", "Sheet3"];  // Add or remove sheet names as needed

  var attachments = [];
  sheets.forEach(function(sheetName, index) {
    var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export" + "?format=xlsx&" + "gid=" + SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getSheetId();
    var params = { method: "GET", headers: { "authorization": "Bearer " + ScriptApp.getOAuthToken() } };
    var blob = UrlFetchApp.fetch(url, params).getBlob().setName(`${sheetName}_${Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM")}.xlsx`);
    attachments.push(blob);
  });

  var message = {
    to: "[email protected]",
    cc: "[email protected]",
    subject: "Monthly Export",
    body: "Hi Team, attached are the monthly exports.",
    name: "Your Name",
    attachments: attachments
  };

  MailApp.sendEmail(message);
}

// Trigger to run the function on the 1st day of each month at 7 AM Philippine time
function createTrigger() {
  ScriptApp.newTrigger('sendMonthlyEmailWithAttachments')
      .timeBased()
      .onMonthDay(1)
      .atHour(7)
      .inTimezone('Asia/Manila') // Set the timezone to Philippine time
      .create();
}

Upvotes: 1

Daniel
Daniel

Reputation: 3725

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

Upvotes: 2

Cooper
Cooper

Reputation: 64062

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

Upvotes: 2

Related Questions