CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsfiltergoogle-sheets-api
mrRay
mrRay

Reputation: 77

How to make filter function and for Each loop NOT to duplicate data?

So, I have a spreadsheet where I take patient information and send row data to clinicians if they haven't seen the patient yet. So, with the code below, I was able to accomplish this task of sending clinicians email as a heads up to go and visit their patients. But here is the problem. Here's what I don't understand.

Let's say I have the following data:

enter image description here

When I get the email send to 1 clinician for all of these patients. The emails come to the inbox unordered. For example, I would get the email for Roland first, then for John, then Jessica. But John is the first record. And also sometimes, I got multiple emails for the same patient. But for the duplication part, I think its because I have a filter and a for each loop which I need them both in this case. Because this code filters rows if there is no data in column J. But then I need a way to pull rest of the row's data if this condition is met. But now I get unordered or multiple of a same records in the email.

Can you please take a look at my filter to see if there's something I can workaround for this? I am seriously lost on this one.

Thanks. 

 function selectRecords() {
      const ss = SpreadsheetApp.getActiveSheet();
      const dataRange = ss.getDataRange();
      const headers = 2;
      const dataValues = dataRange
        .offset(headers, 0, dataRange.getNumRows() - headers)//offsetting the headers from the whole range
        .getValues();

      dataValues
        .filter(row => row[9] == '') //filtered data where row[9] is empty
        .forEach(row => {
          //forEach filtered row do>
          let message =
            row[0] +
            '\n' +
            row[1] +
            '\n' +
            row[2] +
            '\n' +
            row[3] +
            '\n' +
            row[4] +
            '\n' +
            row[5]; 
          let email = row[13];
          let subject = 'Sending emails from a Spreadsheet';
          MailApp.sendEmail(email, subject, message);
          Logger.log(`${subject}: ${message} sent to ${email}`);
        });
    }

Upvotes: 0

Views: 190

Answers (1)

Cooper
Cooper

Reputation: 64042

function selectRecords() {
  const ss = SpreadsheetApp.getActiveSheet();
  const dataRange = ss.getDataRange();
  const headers = 2;
  const dataValues=ss.getRange(3,1,ss.getLastRow()-2,ss.getLastColumn()).getValues();
  dataValues.forEach(function(row){
    if(row[9]=='') {
      let message =row[0] + '\n' + row[1] + '\n' + row[2] + '\n' + row[3] + '\n' + row[4] + '\n' + row[5]; 
      let email = row[13];
      let subject = 'Sending emails from a Spreadsheet';
      MailApp.sendEmail(email, subject, message);
      Logger.log(`${subject}: ${message} sent to ${email}`);
    }
  });
}

Upvotes: 1

Related Questions