Google Sheets script to send Email: If background color of cell is #00ff00, take email from next column and send mail there

Question

This code checks if a cell from Q4 to Q20 is green. Now I want to send an email to an emailadress (which is on the same row as the the green marked cell), every time a cell is green. I need a code which sees ah ok for example Q5 is green -> go to E5 -> take mail address and send mail to the address and this from Q4 to Q20. Maybe someone has an idea what to put in that IF

function automatednotifications() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    
    var range = sheet.getRange("Q4:Q20");
    var bgColors = range.getBackgrounds();
    
    for (var i in bgColors) {
      for (var j in bgColors[i]) {
        if (bgColors[i][j] === '#00ff00') {
          }
      }
    }
}

Answer 1

Based on your current code, you can modify your code as following:

...
var range = sheet.getRange("Q4:Q20");
var values = sheet.getRange("E4:E20").getValues().flat();
...
    if (bgColors[i][j] === '#00ff00') {
     var email = values[i];
    GmailApp.sendEmail(email, "subject", "body");
    }
...

Note:

• Since your emails are all in one column, you can reduce the 2D value range to a 1D array with flat(), so you need to loop only through i and not j
• You could do the same for bgColorsbgColors so you would need only one instead of two for` loops (would make your code simpler adn faster).

References:

• getValues()
• sendEmail(recipient, subject, body)