Question 410880 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – GAS Filter() not returning expected results

I’m having an issue using filter() (ln 19 below) extract only the emails in data (please see
data) of the user’s whose first and last name match the selected users from the last col and row of eList (these are the selected users, please see attached eList). Right now filter() is returning all emails. Can someone please explain what I’m doing wrong?

Here is the function:

//Extracts emails from data of selected users
function getEmailAddresses(e) {
  // Get the responses from the form.
  const responses = SpreadsheetApp.openById("SHEET_ID").getSheetByName("Form Responses 2");
  
  // Extract the selected names from the checkboxes.
  const eList = responses.getRange(responses.getLastRow(),2,1, 1).getValue();
  Logger.log("eListn" + eList);

  // Get the spreadsheet and sheet.
  const ss = SpreadsheetApp.openById("SHEET_ID");
  const shet = ss.getSheetByName('ECI');

  const data = shet.getRange(2, 1, shet.getLastRow() - 1, 3).getValues();

  Logger.log("Datan" + data);

  // Filter the data based on selected names and extract email addresses.
  const selectedNames = eList.split(', ').map(item => item.split(','));
  const emailAddresses = data.filter(row => selectedNames.includes(row.slice(0, 2))).map((row) => row[2]);
  Logger.log("Emailsn" + emailAddresses);

  // Return the email addresses as a comma-separated string.
  return emailAddresses.join(',');
}

// Populates Name in the Form onOpen(e)
function popFormList() {
  var sheet = SpreadsheetApp.openById('SHEET_ID').getSheetByName("ECI");
  var form = FormApp.getActiveForm();

  // Populates values for eList
  var siteValues = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2),
    values = siteValues.getValues();

  values.sort();
  Logger.log(values);

  /* Populates eList with Names from ECI sheet */
  var siteItem = form.getItems(FormApp.ItemType.CHECKBOX)
    .filter(function (siteItem) {
      return siteItem.getTitle() === "Name";
    })[0].asCheckboxItem();
  siteItem.setChoiceValues(values);

}
// Runs when form is submitted with selected users to extract the emails of users for comms    
function main(e) {
  var formResponses = e.response,
    itemResponses = formResponses.getItemResponses(),
    body = "",
    emails = "";

  for (x = 0; x < itemResponses.length; x++) {
    if (itemResponses[x].getResponse() === "") {
      continue;
    }
    else {
      body += itemResponses[x].getItem().getTitle() + ": " + itemResponses[x].getResponse() + "n";
    }
  }
  emails = getEmailAddresses(e);
  subject = "ECI Request"
  Logger.log(emails);
  Logger.log(body);

  try {
    // GmailApp.sendEmail(emails, subject, body, { noReply: true});
  }
  catch (e) {
    Logger.log('Error: There was a problem please see belown' + e);
  }
}
Tags:

2

Answers


  1. 0

    Right now this line:

    const emailAddresses = data.map((row) => row[2] || false).filter(Boolean);

    is just filtering out empty values. You can see that eList doesn’t appear in the expression at all.

    You can try changing it to this:

    const selectedNames = eList.split(', ').map(item => item.split(','));
const emailAddresses = data.filter(row => {
  return selectedNames.some(fullName => {
    return fullName[0] === row[0] && fullName[1] === row[1] ||
           fullName[1] === row[0] && fullName[0] === row[1];
    });
}).map((row) => row[2]);

    For that to work, you should also modify the following line:

    // Extract the selected names from the checkboxes.
const eList = responses.getRange(responses.getLastRow(),2,1, 1).getValues();

    to this:

    const eList = responses.getRange(responses.getLastRow(),2,1, 1).getValue();

    What changed? The last s, so the method returns a string instead of a 2D array.

    EDIT – Previously, I had suggested the following code:

    const selectedNames = eList.split(', ').map(item => item.split(','));
const emailAddresses = data.filter(row =>
                                   selectedNames.includes(row.slice(0, 2)) ||
                                   selectedNames.includes(row.slice(0, 2).reverse()))
                           .map((row) => row[2]);

    That doesn’t work, of course: I fell victim to the good old passed by reference pitfall.

    Login or Signup to reply.
  2. 0

    ALTERNATIVE SOLUTION

    In the example, it seems that the format of the names in the form submission would look like first_name1,last_name1, first_name2,last_name2, first_name3, last_name3.

    If that’s the case, you may use this method, which gets the names as first,last, adds them to become [ 'first_name1,last_name1', 'first_name2,last_name2', 'first_name3,last_name3' ] and separates them as [ 'first_name1', 'last_name1', 'first_name2', 'last_name2', 'first_name2', 'last_name3' ] to be used in the filtering of data.

    This is the modified version script:

    function getEmailAddresses() {
  const responses = SpreadsheetApp.openById("SHEET_ID").getSheetByName("Form Responses 2");
  const vl = responses.getRange(responses.getLastRow(), 2, 1, 1).getValues();
  const eList = vl[0][0].split(", ").flatMap(e => e.split(','));
  const ss = SpreadsheetApp.openById("SHEET_ID");
  const shet = ss.getSheetByName('ECI');
  const data = shet.getRange(2, 1, shet.getLastRow() - 1, 3).getValues();
  const emailAddresses = data.filter((r) => eList.includes(r[0]) || eList.includes(r[1])).map((r) => r[2]);
  return emailAddresses;
}

    Note: I removed the event object (e) from the function since it wasn’t in use.

    OUTPUT

    OUTPUT

    REFERENCES

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search