I am trying log match for last row in form submission against an index I created-CodePudding

I have a forms responses sheet and an index sheet. The index sheet has Name, Location, Email. I am needing to get the value of the last row in form responses and find a name match in my index sheet. Then log the location and email. Right now my code finds all matches and displays them. As I stated before I only want the match for last row.

    function findLocation() {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName('Form Responses');
  var vs1 = sh1.getRange('I3:I'   sh1.getLastRow()).getValues().flat();
  var sh2 = ss.getSheetByName('Match');
  var vs2 = sh2.getRange('A2:C'   sh2.getLastRow()).getValues();
  var matchRows = vs2.filter(row => row[0].length && vs1.includes(row[0]));
  matchRows.forEach(row => {
    var siteMatch = row[1];
    var emailMatch = row[2];
    Logger.log(JSON.stringify(siteMatch));
    Logger.log(JSON.stringify(emailMatch));
  });
}

Screenshot for clarification

CodePudding user response：

Description

I made a simple test data set that I think is similar to yours. The sample script finds the name in index sheet Match that matches the last form response row.

Form Responses

Match

Sample script

function findLocation() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh1 = ss.getSheetByName('Form Responses');
    var vs1 = sh1.getRange(sh1.getLastRow(),1).getValue();
    var sh2 = ss.getSheetByName('Match');
    var vs2 = sh2.getRange('A2:C'   sh2.getLastRow()).getValues();
    var matchRow = vs2.find( row => row[0] ===  vs1 );
    Logger.log(matchRow);
  }
  catch(err) {
    Logger.log(err);
  }
}

Execution log

9:51:59 AM  Notice  Execution started
9:52:00 AM  Info    [John, A, [email protected]]
9:52:01 AM  Notice  Execution completed

References

Array.find()