Facing Error while sending email with Multiple Excel Attachments-CodePudding

I am trying to send 6 x Excel Sheets as Attachment from 1 Google Sheet as Attachment, sometimes this code run's perfectly but sometime it gives the error.

The Original Code

///*** SEND 6 X ATTACHMENTS IN 1 EMAIL FROM GOOGLE SHEET USING APP SCRIPT ***///

function sendExcelAttachmentsInOneEmail() {
  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 url3 = "https://docs.google.com/spreadsheets/d/" SpreadsheetApp.getActiveSpreadsheet().getId() "/export" "?format=xlsx&" "gid=" SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet4").getSheetId();
  var url4 = "https://docs.google.com/spreadsheets/d/" SpreadsheetApp.getActiveSpreadsheet().getId() "/export" "?format=xlsx&" "gid=" SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet5").getSheetId();
  var url5 = "https://docs.google.com/spreadsheets/d/" SpreadsheetApp.getActiveSpreadsheet().getId() "/export" "?format=xlsx&" "gid=" SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet6").getSheetId();

  var ss3 = SpreadsheetApp.getActiveSpreadsheet();
  var sheet3 = ss3.getSheetByName("Sheet7");
  var value4 = sheet3.getRange("J1").getValue();

  var params = {method:"GET",headers:{"authorization":"Bearer "  ScriptApp.getOAuthToken()}};
  var blob = UrlFetchApp.fetch(url, params).getBlob().setName("Sheet1 XYZ Reports.xlsx");
  var blob1 = UrlFetchApp.fetch(url1, params).getBlob().setName("Sheet2 TXT Data.xlsx");
  var blob2 = UrlFetchApp.fetch(url2, params).getBlob().setName("Sheet3 RAW FILES.xlsx");
  var blob3 = UrlFetchApp.fetch(url3, params).getBlob().setName("Sheet4 SYS DATA.xlsx");
  var blob4 = UrlFetchApp.fetch(url4, params).getBlob().setName("Sheet5 REPORTED ISSUES.xlsx");
  var blob5 = UrlFetchApp.fetch(url5, params).getBlob().setName("Sheet6 FIXED ISSUES.xlsx");

  var message = {
    to: "[email protected]",
    cc: "[email protected]",
    subject: "REPORTS - " value4,
    body: "Hi Team,\n\nPlease find attached Reprots.\n\nBest Regards!",
    name: "",
    attachments: [blob, blob1, blob2, blob3, blob4, blob5]
  }
  MailApp.sendEmail(message);
}

Error Message from App Script

9:05:45 PM  Notice  Execution started
9:05:49 PM  Error   Exception: Request failed for https://docs.google.com returned code 429. Truncated server response: <!DOCTYPE html><html lang="en"><head><meta name="description" content="Web word processing, presentations and spreadsheets"><meta name="viewport" c... (use muteHttpExceptions option to examine full response)
sendExcelAttachmentsInOneEmail  @ Code.gs:44

CodePudding user response：

I have made it work and also did an overhaul to the script due to personal reasons (I don't like repetitive lines of code). See the working script below.

Script:

function sendExcelAttachmentsInOneEmail() {
  var sheets = ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5', 'Sheet6'];
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadSheetId = spreadSheet.getId();

  var urls = sheets.map(sheet => {
    var sheetId = spreadSheet.getSheetByName(sheet).getSheetId();
    // Used this alternative url since I'm getting the error below. This other url has less traffic issues during testing:
    // "This file might be unavailable right now due to heavy traffic. Try again."
    return `https://docs.google.com/feeds/download/spreadsheets/Export?key=${spreadSheetId}&gid=${sheetId}&exportFormat=xlsx`;
  });

  var reportName = spreadSheet.getSheetByName('Sheet7').getRange(1, 10).getValue();

  var params = {
    method: 'GET',
    headers: {
      'Authorization': 'Bearer '   ScriptApp.getOAuthToken()
    },
    // Add mute http exceptions to proceed
    muteHttpExceptions: true
  };

  var fileNames = ['Sheet1 XYZ Reports.xlsx',
                   'Sheet2 TXT Data.xlsx',
                   'Sheet3 RAW FILES.xlsx',
                   'Sheet4 SYS DATA.xlsx',
                   'Sheet5 REPORTED ISSUES.xlsx',
                   'Sheet6 FIXED ISSUES.xlsx'];

  var blobs = urls.map((url, index) => {
    // Added an interval due to heavy traffic error. Increase interval if needed.
    Utilities.sleep(1000);
    return UrlFetchApp.fetch(url, params).getBlob().setName(fileNames[index]);
  });


  var message = {
    to: '[email protected]',
    subject: 'REPORTS - '   reportName,
    body: "Hi Team,\n\nPlease find attached Reports.\n\nBest Regards!",
    attachments: blobs
  }

  MailApp.sendEmail(message);
}

Output:

Notes:

Important bits here are the following:

muteHttpExceptions
The alternative url for less traffic issues
Utilities.sleep(1000)