Attempting to learn SendMail Script

Question

i'm new to the script side of sheets and i'm not 100% sure on what i'm doing and just trying to learn a bit more.

I have this script :

function DebtorMailSend(g) {
 If (g.rangecolumnstart != 7 || g.Value != 60) return;
Const rData = g.source.getactivesheet().getrange(g.range.rowStart,1,1,5).getValues()
let c = rData[0][0]
let i = rData[0][1]
let e = rData[0][2]
let b = rData[0][3]
let n = rData[0][4]

let msg = "Hi,"  "I hope you're well." "We have been reviewing our records and understand that invoice number" + n + "relating to UK services have not yet been settled." "We would be grateful if you could expedite payment and let us know when we can expect to receive the remittance." "Should you have any queries or require further details, please do not hesitate to contact me." "Kind Regards," "Ken"
GmailApp.sendEmail(e,c + "- Invoice Reminder - " + n,msg)
}

And i cant save because of an issue with the Return, which i don't fully understand.

Syntax error: SyntaxError: Unexpected string line: 10 file: Code.gs

I have a bunch of stuff i need to add like a 2nd condition and CC's but i'm trying to get the basics first.

Of course i'm not expecting anyone to solve the whole thing, but any explanations or guidance is very much appreciated, feel free to add a tab with guidance or tips!

---

Here is the screenshot of the sheet:

Answer 1

The issue is caused when you define the msg variable.

When defining a variable, you can't assign it with a value with more than 1 string unless you concatenate them.

Try this instead:

//On Edit Trigger
function DebtorMailSend(e) {
  let editedCellValue = e.value;
  let editedRow = e.range.getRow();
  let editedColumn = e.range.getColumn();
  if (editedColumn != 6 || editedCellValue != 60) return;
  const rData = e.source
    .getActivesheet()
    .getRange(editedRow, 1, 1, 5)
    .getValues();
  let [column1, column2, column3, column4, column5] = rData[0];
  let msg = `Hi,\nI hope you're well.\nWe have been reviewing our records and understand that invoice number ${column5} relating to UK services have not yet been settled.\nWe would be grateful if you could expedite payment and let us know when we can expect to receive the remittance.\nShould you have any queries or require further details, please do not hesitate to contact me.\nKind Regards,\nKen`;
  GmailApp.sendEmail(column3, `${column1}- Invoice Reminder - ${column5}`, msg, { cc: "example@gmail.com" });
}

What I've changed:

1. Updated the msg string to concatenate the strings. Also added \n to create new lines where the string were previously separated.
2. Simplified assigning the [c, i, e, b, n] variables using Destructuring assignment
3. Updated the concatenation to Template literals instead of using + to join the strings.
4. Lower cased the If function and the Const variable.
5. Fixed a few bugs with how the edited values were being pulled.
6. Fixed an issue where the column if statement was looking at column G instead of F. Columns start at position 0. So 0 is actually column A.
7. Renamed a few variables for clarity
8. Renamed the parameter g to e to follow onEdit best practices.
9. Adjusted the capitalization in a few areas.
10. Added the option to define the cc emails using an object parameters (separate emails by commas). Just replace example@gmail.com with the email address that should be cc'd.

Notes:

• If you'd like to add more formatting to your message, this might help.
• Javascript is very sensitive to the capitalization of the code. Ensure you're following the capitalization exactly as they are in the documentation.
• If you're looking to get more control over the formatting of the email, you could use the htmlBody object parameter option to use html instead of a formatted string.

Edit: Based on the information that Column F is a formula that isn't manually edited, I've adjusted my answer to be a dropdown menu trigger.

How to run:

1. Run the onOpen script from within the code editor
2. Refresh the Google Sheet doc
3. You'll notice a new menu appear in your spreadsheet called "Send Late Payment Emails"
4. Whenever you're ready to send the reminder emails, use the "Send Emails" dropdown option from the "Send Late Payment Emails" menu and it'll send an email for each late invoice that doesn't have "Sent" in column F
5. One the email is sent, the script will automatically update column F to "Sent".

/**
 * Creates a new dropdown menu on the top of your sheet that can trigger the DebtorMailSend script
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Send Late Payment Emails")
    .addItem("Send Emails", "DebtorMailSend")
    .addToUi();
}

function DebtorMailSend() {
  let activeSheet = SpreadsheetApp.getActiveSheet()
  let dataRange = activeSheet.getRange(2, 1, activeSheet.getLastRow() - 1, 7) //only looks at columns A -> G
  let sheetValues = dataRange.getValues()

  //Loops through each row of the spreadsheet
  for (var [rowIndex, rowValues] of sheetValues.entries()) {
    var [client, invoiceWith, contact, billingManager, invoiceNumber, status, daysOutstanding] = rowValues
    
    //Checks if the invoice is outstanding and avoids sending an email twice
    if (daysOutstanding < 60 || status == "Sent") continue;

    //Sends email
    var msg = `Hi,\nI hope you're well.\nWe have been reviewing our records and understand that invoice number ${invoiceNumber} relating to UK services have not yet been settled.\nWe would be grateful if you could expedite payment and let us know when we can expect to receive the remittance.\nShould you have any queries or require further details, please do not hesitate to contact me.\nKind Regards,\nKen`;
    GmailApp.sendEmail(columnC, `${client}- Invoice Reminder - ${invoiceNumber}`, msg, {cc: "example@gmail.com"});
    
    //Updates the status to "Sent" after an email has been sent
    activeSheet.getRange(rowIndex + 2, 6).setValue("Sent")
  }
}

Answer 2

As an example it could be two functions like this:

// set checkboxes in 13th column for the rows that have >60 in 6th column

function set_checkboxes() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var days_column = 6;
  var checkboxes_column = 13;

  var data = sh.getDataRange().getValues();

  data.forEach((row,i) => { if (row[days_column-1] > 60) 
      sh.getRange(i+1,checkboxes_column).insertCheckboxes().check();
  });
}


// send emails for every row that has checked checkbox in 13th column

function send_emails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var checkboxes_column = 13;

  var data = sh.getDataRange().getDisplayValues();

  data.forEach((row,i) => {
    if (row[checkboxes_column-1] == 'TRUE') {

      // you can use html tags in your message :)
      var msg = `<p>Hi,</p>
        <p><font size=5>I hope you're <font color='red'>well</font>.</font></p>
        <p>Best regards.</p>`;

      var addr = row[2];
      var copy = row[3];
      var subj = row[0] + " - Invoice Reminder - " + row[4];

      MailApp.sendEmail(addr, subj, msg, { 'htmlBody': msg, 'cc': copy });
      
      sh.getRange(i+1,checkboxes_column).uncheck();
    }
  });
}

First function sets checkboxes in 13th columns for the rows that have >60 in 6th column.

Second function sends emails for the checked rows. And you can uncheck any checkbox if you don't want to send email for some rows. And vice versa you can check any other row if you want to send the email even if the row doesn't meet the condition.

You can make the buttons to run the functions: