I'm trying to write a script that sends the content of one sheet when one particular cell is not empty. The checking part of the script is working. The email part is working too. I would like to attach the contents of the sheet (ideally in xlsx format, but csv could do if it is simpler) to the email but all the scripts I found online seem to use some Google Drive API and haven't figured out how to use it.
I commented out the lines that should be doing the exporting and, for now, the script just sends a reminder to manually export the sheet. This I would like to automate. Also, instead of sending the email to a hard-coded email, I would like to send it to the email address found in a particular cell in the same sheet (I1).
Any help would be appreciated.
Here's my script:
unction checkAndExport() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = 'Bookings List'; // Replace with the desired sheet name
var sheet = spreadsheet.getSheetByName(sheetName);
var checkCell = sheet.getRange('B3'); // Cell to check if it is empty
var cellValue = checkCell.getValue();
if (cellValue !== '') {
// Export sheet data to a CSV string
// var outputDocument = DocumentApp.create('My custom csv file name');
// var content = getCsv();
// var textContent = ContentService.createTextOutput(content);
// textContent.setMimeType(ContentService.MimeType.CSV);
// var blob = Utilities.newBlob(textContent, 'text/csv', sheetName + '.csv');
// Send an email with the CSV file attached
var recipientEmail = 'name@domain.com'; // Replace with actual email address
var subject = 'Bookings Email reminder';
var body = 'It is time to send the bookings list to some leader!';
var options = {
name: 'Exported Sheet',
// mimeType: blob.getContentType(),
inline: false,
// attachments: [blob]
};
From your question, I couldn't understand the source you want to attach as XLSX data. So, this is my guess. In order to attach the specific sheet in the active Spreadsheet as XLSX data to email, how about the following modification?
function checkAndExport() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = 'Bookings List'; // Replace with the desired sheet name
var sheet = spreadsheet.getSheetByName(sheetName);
var checkCell = sheet.getRange('B3'); // Cell to check if it is empty
var cellValue = checkCell.getValue();
if (cellValue !== '') {
// --- I modified the below script.
// Retrieve XLSX data from the specific sheet of the active Spreadsheet as blob.
var format = "xlsx"; // or "csv"
var sheetId = sheet.getSheetId(); // or spreadsheet.getSheetByName("sheet name").getSheetId()
var blob = UrlFetchApp.fetch(`https://docs.google.com/spreadsheets/export?id=${spreadsheet.getId()}&exportFormat=${format}&gid=${sheetId}`, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
// Send an email with the CSV file attached
var recipientEmail = sheet.getRange("I1").getDisplayValue();
var subject = 'Bookings Email reminder';
var body = 'It is time to send the bookings list to some leader!';
MailApp.sendEmail({ to: recipientEmail, subject, body, name: 'Exported Sheet', attachments: [blob] });
}
}
var sheetId = sheet.getSheetId();
to var sheetId = spreadsheet.getSheetByName("sheet name").getSheetId();
.var format = "xlsx";
to var format = "csv";
.Also, instead of sending the email to a hard-coded email, I would like to send it to the email address found in a particular cell in the same sheet (I1).
.MailApp.sendEmail
as a sample. So, please modify it to your actual situation.About your following request,
I noticed however that, since the values of three cells are formulas dependent on other sheets in the same file, the exported file shows an error in these cells (B1, I1 and O1). Would it be possible to export "values only"?
In this case, how about the following sample script?
function checkAndExport() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = 'Bookings List'; // Replace with the desired sheet name
var sheet = spreadsheet.getSheetByName(sheetName);
var checkCell = sheet.getRange('B3'); // Cell to check if it is empty
var cellValue = checkCell.getValue();
if (cellValue !== '') {
// --- I modified the below script.
// Retrieve XLSX data from the specific sheet of the active Spreadsheet as blob.
var format = "xlsx"; // or "csv"
var newSheet = sheet.copyTo(spreadsheet).setName(`new_${sheetName}`);
var range = newSheet.getDataRange();
range.copyTo(range, { contentsOnly: true });
var sheetId = newSheet.getSheetId(); // or spreadsheet.getSheetByName("sheet name").getSheetId()
SpreadsheetApp.flush();
var blob = UrlFetchApp.fetch(`https://docs.google.com/spreadsheets/export?id=${spreadsheet.getId()}&exportFormat=${format}&gid=${sheetId}`, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
// Send an email with the CSV file attached
var recipientEmail = sheet.getRange("I1").getDisplayValue();
var subject = 'Bookings Email reminder';
var body = 'It is time to send the bookings list to some leader!';
MailApp.sendEmail({ to: recipientEmail, subject, body, name: 'Exported Sheet', attachments: [blob] });
spreadsheet.deleteSheet(newSheet);
}
}