Google Apps Script - How to get email addresses from Sheet 2 and send email

Question

I'm having trouble with getLastColumn().

I have two pages. The first page is the page where the data is entered. On the second page, the departments of the people to be sent e-mails are written, and in the other columns, the e-mail addresses of the department officials are in order along the cells.

Sheet Data Page

Sheet Email Address Page

What I want to do is find the department name on the first page, on the second page and it only emails all the officials of that department.

The code I have so far only sends emails to the first address in the email addresses that progress through the columns.

function ssForward_otomatik_mail() {

  // DEFINE YOUR MAIN SPREADSHEET
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1RxbUCsZdaUPrOlosAVcfaHeM08pJWfElugmzfunYrj8/edit#gid=996210355");


  const dataSheet = ss.getSheetByName("SHEET DATA PAGE");
  const dataSheet2 = ss.getSheetByName("SHEET EMAIL ADDRESS PAGE"); 
     
  for (var i = 2; i <= dataSheet.getLastRow(); i++){
    for (var k = 1; k <=dataSheet2.getLastRow(); k++){
      for (var l = 2; l <=dataSheet2.getLastColumn(); l++){
        
        const toEmail = dataSheet2.getRange(k,l).getValue();
        const department = dataSheet.getRange(i, 4).getValue();
        const mail_bildirimi = dataSheet.getRange(i,22).getValue();
        const egitim_durumu = dataSheet.getRange(i,20).getValue();
        const personelin_adi = dataSheet.getRange(i,2).getDisplayValue();
        const blank = "";
        const sicil_no = dataSheet.getRange(i,1).getDisplayValue();
        const department2 = dataSheet2.getRange(k,1).getValue();
        
        const htmlTemplate = HtmlService.createTemplateFromFile('emailTable15');

        htmlTemplate.personelin_adi = personelin_adi;
        htmlTemplate.sicil_no = sicil_no;

        const htmlForEmail = htmlTemplate.evaluate().getContent();
        

        if (deparmant == department2 & mail_bildirimi != "✓" & egitim_durumu == "HAYIR" & personelin_adi != blank & sicil_no>0 & sicil_no<300000){

          var subject = 'Biriminizde Yaşanmış İş Kazası Hakkında'
          MailApp.sendEmail({
            to: toEmail,
            //cc: ccEmail,
            subject,
            htmlBody: htmlForEmail,
          });  
          // MARK THE ROW AS COMPLETED
          dataSheet.getRange(i, 22).setValue("✓");
        }
      }
    }
  }
}

Any help would be greatly appreciated.

Answer 1

Get Department Emails in a string separated by commas

function getAllEmailsForDepartment(d) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet Name');
  const ds = sh.getRange(1,1,sh.getLastRow(),1).getValues().flat();
  let idx = ds.indexOf(d);
  if(~idx) {
    return sh.getRange(idx + 1,2,1,sh.getLastColumn() -1).getValues().flat().join(',');
  }else{
    SpreadsheetApp.getUi().alert(`Department ${d} not found`);
  }
}