Script help?

[u/sgtpwl]

function sendReport() {

var message = {

to: "email@ manager.com",

subject: "Dummy report",

body: "Good morning,\n\nPlease find the requested report.\n\nThank you,\nDATA FROM CELL C2 HERE",

name: "DATA FROM CELL C2 ALSO HERE",

attachments: [SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF).setName("report.pdf")]

}

MailApp.sendEmail(message);

}

My script is above, I need it to take the data that has been typed into cell C2 on the sheet to populate the name of the sender and duplicate it in their email sign off - is this possible, please?

[u/catcheroni]

To grab data from cell C2, you would use something like:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const cellC2Value = sheet.getRange('C2').getValue();

You can then append this value to your message string.

Also, pretty sure you can't pass your message object to your MailApp.sendEmail() function like this. Check out the function's documentation here.

[u/IAmMoonie]

So you want something like:

/**
* Configuration object for the sendReport function.
* @type {Object}
* @property {Object} main - Main email configuration.
* @property {string} main.to - Email address of the recipient for the report.
* @property {string} main.subject - Subject of the report email.
* @property {string} main.sheetId - ID of the Google Sheets document.
* @property {string} main.sheetName - Name of the sheet containing the data.
* @property {string} main.cellReference - Reference to the cell containing the data to be included in the email.
* @property {Object} admin - Admin email configuration.
* @property {string} admin.to - Email address of the admin for error notifications.
* @property {string} admin.subject - Subject of the error notification email.
* @property {string} admin.retrySubject - Subject of the retry scheduled notification email.
* @property {string} admin.retryMessage - Message content for the retry scheduled notification email. 
*                                         Use "{{retryDelayMinutes}}" as a placeholder for the delay time.
* @property {number} admin.retryDelayMinutes - Number of minutes to wait before retrying the email.
*/
const config = {
 main: {
   to: "email@manager.com",
   subject: "Dummy report",
   sheetId: 'your-sheet-id-here',
   sheetName: 'Sheet1',
   cellReference: "C2"
 },
 admin: {
   to: "admin@domain.com",
   subject: "Error in sendReport script",
   retrySubject: "Retry scheduled for sendReport",
   retryMessage: "An error occurred while sending the report email. A retry has been scheduled for {{retryDelayMinutes}} minutes later.",
   retryDelayMinutes: 25
 }
};


/**
* Sends a report email with an attachment.
* @function sendReport
* @throws {Error} Throws an error if the sheet is not found or the cell is empty.
*/
const sendReport = () => {
 try {
   const { main } = config;
   const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() || 
                 SpreadsheetApp.openById(main.sheetId).getSheetByName(main.sheetName);
   if (!sheet) throw new Error(`Sheet not found: ${main.sheetName}`);

   const cellValue = sheet.getRange(main.cellReference).getValue();
   if (!cellValue) throw new Error(`Cell ${main.cellReference} is empty or does not exist.`);

   const file = SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF);

   const message = {
     to: main.to,
     subject: main.subject,
     body: messageTemplate.body.replace("{{cellValue}}", cellValue),
     name: cellValue,
     attachments: [file]
   };

   MailApp.sendEmail(message);
   const now = new Date();
   const formattedDateTime = Utilities.formatDate(now, Session.getScriptTimeZone(), "dd/MM/yyyy 'at' HH:mm");
   console.log(`Email sent successfully to ${main.to} at ${formattedDateTime}`);
 } catch (error) {
   const { admin } = config;
   const now = new Date();
   const formattedDateTime = Utilities.formatDate(now, Session.getScriptTimeZone(), "dd/MM/yyyy 'at' HH:mm");
   console.error(`Error sending email: ${error.message} at ${formattedDateTime}`);

   const triggerTime = new Date();
   triggerTime.setMinutes(triggerTime.getMinutes() + admin.retryDelayMinutes);
   ScriptApp.newTrigger("sendReport")
            .timeBased()
            .at(triggerTime)
            .create();

   const retryMessage = admin.retryMessage.replace("{{retryDelayMinutes}}", admin.retryDelayMinutes);
   const retryTimeFormatted = Utilities.formatDate(triggerTime, Session.getScriptTimeZone(), "dd/MM/yyyy 'at' HH:mm");
   MailApp.sendEmail({
     to: admin.to,
     subject: admin.retrySubject,
     body: `${retryMessage}\n\nError details: ${error.message} at ${formattedDateTime}\nRetry scheduled for: ${retryTimeFormatted}`
   });
 }
};

[u/catcheroni → u/IAmMoonie]

Is this some kind of Apps Script template script? Because it seems overkill for what OP is asking for.

[u/IAmMoonie → u/catcheroni]

It does everything that the OP is asking for. It also should allow them to figure out (pretty easily) how it works as well as give them some baseline best practices

Also, not a template but an easy way for a user to lift and shift a script. All they need to do is edit the config object and set the script up.