CODEWITHSUNDEEP
arraysemailgoogle-sheetsgoogle-apps-scriptscripting
Mikołaj Gano
Mikołaj Gano

Reputation: 39

Apps script using data from Sheets fails to send from Gmail account without returning any errors

I need to automate sending emails to a list of recipients stored in a Google spreadsheet. I'm new to Apps Script and Javascript but made some simple code based on tutorials that fit what I need.

My sheet includes columns for the email address, subject, and body text.

There is also a single cell, F2, for a common BCC address that is used by all rows.

I have a column with a checkbox to indicate if a row should be included.

When I run the script to send an email to myself, it executes without error, but I never receive the email.

Sample Data

Link to a sample Google spreadsheet

A B C D E F
1 People Send Email Subject Body Blind Carbon Copy
2 name01 ☑ email01 subject01 body01 common_bcc_address
3 name02 ☑ email02 subject02 body02
4 name03 ☐ email03 subject03 body03
  1. The same BCC address, located in F2, is used for all messages, not just the first row.
  2. For each row, an email should only be sent if column B is checked.
  3. The sheet is named People1

Script

function sendEmails() {
  var ss  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('People1');
  var values = ss.getDataRange().getValues();
  var ebcc = ss.getRange(2, 6).getValue()

  for (var i = 0; i > values.length ;i++){
    if (values[i][3] == true) {  
      var theEmail = ss.getRange(i,3).getValue();
      var theSubject = ss.getRange(i,4).getValue();
      var theBody = ss.getRange(i,5).getValue();
      GmailApp.sendEmail(theEmail, theSubject, theBody, {bcc: ebcc})
      }
  }
}

Upvotes: 1

Views: 88

Answers (2)

player0
player0

Reputation: 1

try:

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('People1');
  var values = ss.getDataRange().getValues();
  var ebcc = ss.getRange(2, 6).getValue();

  for (var i = 1; i < values.length; i++) {
    if (values[i][3] == true) {  
      var theEmail = values[i][0];
      var theSubject = values[i][1];
      var theBody = values[i][2];
      GmailApp.sendEmail(theEmail, theSubject, theBody, {bcc: ebcc});
    }
  }
}
  • changed i > values.length to i < values.length to ensure the loop iterates through each row of data
  • changed ss.getRange(i, 3).getValue() to values[i][0], values[i][1], and values[i][2] respectively, ensuring data is correctly accessed from the values array (assuming email, subject, and body are in the first, second, and third columns respectively)
  • adjusted to start i from 1 instead of 0 to skip the header row

Upvotes: 1

Blindspots
Blindspots

Reputation: 1014

TL;DR

Error in script Correction 
> values.length < values.length
values[i][3] values[i][1]

Adapted Script

This is how I would adapt your script:

function sendEmails() {
  const ss  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('People1');
  const values = ss.getDataRange().getValues();
  
  values.forEach(r => (r[1] == true) &&
    GmailApp.sendEmail(r[2],r[3],r[4],{bcc: values[1][5]}));
}

Your Approach

If you prefer your original approach, here it is updated:

function sendEmails() {
  const ss  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('People1');
  const values = ss.getDataRange().getValues();
  const ebcc = values[1][5];
  
  for (let i = 0; i < values.length; i++){
    if (values[i][1] == true) {  
      let theEmail = values[i][2];
      let theSubject = values[i][3];
      let theBody = values[i][4];
      GmailApp.sendEmail(theEmail, theSubject, theBody, {bcc: ebcc})
    }
  }
}

Notes:

  1. As @TheMaster commented, your for loop should have < values.length not > values.length
  2. Even with the previous item corrected, none of the rows would ever be processed because if (values[i][3] == true) is never true:
    • While columns and rows may start at 1, array indexes start at 0 so you need to be aware of whether you are referring to an array index or a row/column number.
    • 3 in values[i][3] specifies the value from Column D (Column 4) whereas your checkboxes are in Column B (Column 2). So, to refer to the value from Column B, you would use values[i][1]
  3. While you retrieve all the values from the Sheet in the third row of your script using ss.getDataRange().getValues(), you then retrieve needed values again which is unnecessary and delays your script with extra calls. For example,
    1. const ebcc = ss.getRange(2,6).getValue()
      should be
      const ebcc = values[1][5]
    2. let theEmail = ss.getRange(i,3).getValue()
      should be
      let theEmail = values[i][2]
  4. I used a forEach() loop instead of for, but either will work.

Upvotes: 2

Related Questions