In your situation, as an approach, how about converting the formulas to the cell values? When this is reflected in your script, it becomes as follows.
### Modified script:
```javascript
function sendExcelAttachmentsInOneEmail() {
var sheets = ['OH INV - B2B', 'OH INV - Acc', 'OH INV - B2C', 'B2B', 'ACC', 'B2C'];
var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
// --- I added the below script.
spreadSheet = spreadSheet.copy("temp");
spreadSheet.getSheets().forEach(sheet => {
var range = sheet.getDataRange();
range.copyTo(range, { contentsOnly: true });
});
SpreadsheetApp.flush();
// ---
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('IMEIS').getRange(1, 14).getValue();
var params = {
method: 'GET',
headers: {
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()
},
// Add mute http exceptions to proceed
muteHttpExceptions: true
};
var fileNames = ['OH INV - B2B.xlsx',
'OH INV - Acc.xlsx',
'OH INV - B2C.xlsx',
'B2B.xlsx',
'ACC.xlsx',
'B2C.xlsx'];
var blobs = urls.map((url, index) => {
// Added an interval due to heavy traffic error. Increase interval if needed.
Utilities.sleep(10000);
return UrlFetchApp.fetch(url, params).getBlob().setName(fileNames[index]);
});
var message = {
to: 'email@domain.com',
cc: 'email@domain.com',
subject: 'Combined - REPORTS - ' + reportName,
body: "Hi Team,\n\nPlease find attached Reports.\n\nBest Regards!",
attachments: blobs
}
MailApp.sendEmail(message);
// Added
DriveApp.getFileById(spreadSheetId).setTrashed(true);
}
```
- By this modification, the formulas are converted as the cell values.
### Reference:
- [copyTo(destination, options)](https://developers.google.com/apps-script/reference/spreadsheet/range#copyTo(Range,Object))
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: 'user@domain.com',
subject: 'REPORTS - ' + reportName,
body: "Hi Team,\n\nPlease find attached Reports.\n\nBest Regards!",
attachments: blobs
}
MailApp.sendEmail(message);
}
```
### Output:
[![output][1]][1]
### Notes:
Important bits here are the following:
- `muteHttpExceptions`
- The alternative url for less traffic issues
- `Utilities.sleep(1000)`
[1]: https://i.stack.imgur.com/fj7Kn.png
