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

And this is the link to the test sheet:

Fix 1: Get specific last row of column G:

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

or

Fix 2: Filter data

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

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.

CodePudding user response：

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();