Add cell value to email subject line in Google Script

Question

I'm not a programmer, so I'm trying to keep this nice and simple. I've created a script with which I can send a Google Sheet as a pdf attachment to multiple email addresses. This all works as it should.

However, I would also like to include that Sheet's B7 cell value in the email subject line: "Subject line ". What is the easiest way to do this?

This is the code I have so far:

function sendReport() {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Artikelen").hideSheet();
var message = {
to: "emailadresses",
subject: "Subject line",
body: "TEXT",
name: "NAME",
attachments: [SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF).setName("Transportaanvraag")]
}
MailApp.sendEmail(message);
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Artikelen").activate();
}

I've tried several scripts from other similar issues, but I keep getting a SyntaxError. This is the first script I've ever tried my hand on, so the solution is probably simple. Thanks for helping out!

Answer 1

You can try this:

function sendReport() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Artikelen");

  // Get the value of cell B7
  var cellB7Value = sheet.getRange("B7").getValue();

  // Define an array of email addresses
  var emailAddresses = ["[email protected]", "[email protected]", "[email protected]"];

  var message = {
    to: emailAddresses.join(), // Join the email addresses with commas
    // Include the value of B7 in the subject line
    subject: "Subject line " + cellB7Value,
    body: "Dear ...,\n\nPlease find attached ...\n\nRegards,",
    name: "NAME",
    attachments: [SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF).setName("Transportaanvraag")]
  };

  MailApp.sendEmail(message);
}