CODEWITHSUNDEEP
emailgoogle-apps-script
user1128912
user1128912

Reputation: 191

Google script - send email alert

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 --

--- WHAT DOESN'T WORK --

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('[email protected]', 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

Upvotes: 2

Views: 171

Answers (2)

NightEye
NightEye

Reputation: 11194

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.

Upvotes: 2

Kris
Kris

Reputation: 567

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('[email protected]', 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();

Upvotes: 1

Related Questions