Google script - send email alert

user_1128912 0
Viewed 70

I have a script that looks into values in column G and if the correspondent cell in column A is empty, sends me an email.

--- WHAT WORKS --

  • It works ok for static values: it sends one email per each not empty cell in column G for which there is no value in column A

--- WHAT DOESN'T WORK --

  • It sends several emails for what I assume it's every Column G cell (empty or not) when the column A values are fetched from another tab. That way it's like all G and A cells have data, so I get multiple unwanted emails.

This is the script code:

  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet to send emails');
  const data = sh.getRange('A2:G'+sh.getLastRow()).getValues();
  data.forEach(r=>{
     let overdueValue = r[0];  
     if (overdueValue === ""){
         let name = r[6];
         let message = 'Text ' + name;
         let subject = 'TEXT.'
         MailApp.sendEmail('myemail@gmail.com', subject, message); 
     }
  });  
}

And this is the link to the test sheet:

https://docs.google.com/spreadsheets/d/1OKQlm0PjEjDB7PXvt34Og2fa4vPZWnvLazTEawEtOXg/edit?usp=sharing

In this test case, I "should" only get one email, related to ID 55555. With the script as is, I get one related to 55555 and several others "undefined".

To avoid e-mail spam, I didn't add the script to that sheet but it shows the "Vlookup" idea.

Can anyone give me a hand, please? Thank you in advance

google-apps-script email
2 Answers

Issue:

The issue with your original script is that the sh.getLastRow returns 1000 (it also processes those rows that doesn't have contents, result to undefined)

output1

Fix 1: Get specific last row of column G:

const gValues = sh.getRange('G1:G').getValues();
const gLastRow = gValues.filter(String).length;

output2

or

Fix 2: Filter data

const data = sh.getRange('A2:G' + sh.getLastRow()).getValues().filter(r => r[6]);

output3

Note:

  • As Kris mentioned in the comments, there is a specific case where getting the last row above will fail (same with getNextDataCell). This will not properly get the last row WHEN there are blank rows in between the first and last row of the column. If you have this kind of data, then use the 2nd method which is filtering the data.
  • If your data in column G does not have blank cells in between the first and last row, then any method should work.
user_161186860

I checked your test sheet, and sh.getLastRow() is 1000.

OPTION 1

If column G won't have empty cells between filled ones, then you can do this:

  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Sheet to send emails");
  
  // get the first cell in column G
  var gHeader = sheet.getRange(1, 7);

  // equivelent of using CTRL + down arrow to find the last da
  var lastRow = gcell.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

  const data = sheet.getRange(2, 1, lastRow, 7).getValues();

OPTION 2

Add another condition to your code - like this:

  data.forEach(r=>{
     let overdueValue = r[0]; 
     let name = r[6] 
     
     // check if the value in col A is blankd and col G is not blank
     if (overdueValue === "" && name !== ""){
         let message = 'Text ' + name;
         let subject = 'TEXT.'
         MailApp.sendEmail('myemail@gmail.com', subject, message); 
     }
  });

And to speed it up, use a named range to limit how many rows it has to iterate through:

  const ss = SpreadsheetApp.getActive();
  const data = ss.getRangeByName("Your_NamedRange_Here").getValues();
user_154701380
Related