Find matching cell value and return email address in another spreadsheet-CodePudding

The Purchase Orders List Sheet contains a list of PO# And we only want the first 3 characters in cell A2 (I have this working). In another spreadsheet “Active1” we also have many PO#’s in Column A excluding the header but we only want the first 3 characters as well. data sheet for the PO#’s is the range A2:A.

How can I get the email of the cell on the data sheet that matches when then the PO#’s Match? I know the email address is in Column G of “Active1” how can I get the email address to return once the first 3 Characters of PO#’s from the two spreadsheet Match?

Here Is an updated version. Now this doesn't show the email in Console.Log. It's the Emaildata.every() it not showing the email address.

    function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    "Purchase Orders List"
  );
  var po_numID = ss.getRange("A2").getDisplayValue().substr(0, 3); // Gets PO# the first 3 Characters of the PO
  Logger.log(po_numID)
  var emailDataSheet = SpreadsheetApp.openByUrl(
    "https://docs.google.com/spreadsheets/d/1xIA3xcXrpjCPO8r3rzXuVWhu-ycUkN1iQzfYbpFFlFM/edit#gid=1768035929"
  )
    .getSheetByName("TestA");

  var emailData = emailDataSheet
    .getRange("A2:A")
    .getDisplayValues()
    .flat()
    .map(po => po.substr(0,3));

  emailData.every((po, index) => {
    if (po == po_numID){
      var email = emailDataSheet.getRange(index   2,7).getValue();
      Logger.log(email);
      return false;
    }
  })
}

CodePudding user response：

every() checks to see if every item in the array passes the test and returns true || false, see [docs][1]. Since you want to find one match and get a value based on its index or column, , you need to use another method, and I think your tool of choice would be forEach(see [docs][2]). Basically your last function would look like this:

const testColumnNumber = 1 // column A
emailData.forEach( (record, index) => { 
  if( record[testColumnNumber-1] == po_numID ){
    var email = emailDataSheet.getRange(index 2, 7).getValue()
  }
})

[1]: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/every
[2]: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/find