How can I determine if a respondent's email address is unique in a list?

Overview: A Google Sheets file is linked to 3 Google Forms. After a form is submitted, the responses are added to the coinciding Dummy spreadsheet tabs: Form Responses 1 Form Responses 2 Form Responses 4

After each form submission, email addresses are being tracked (column B), in addition to other responses from the form’s questions.

Goal: My goal is to identify if a respondents email address is unique within all 3 Form Responses tab.

For example, the preferred result would only display one email address if it is listed in multiple Dummy Sheet tabs.

The Email addresses are being tracked in column B of the Dummy Spreadsheet. After this validation takes place, subsequent functions will be executed.

Current issue: The current logic is identifying the Unique email addresses per sheet tab, but not a cumulative result (i.e. for all tabs combined). The preferred result would only display one email address if it is listed in multiple Dummy Sheet tabs.

Further guidance would be much appreciated.

function findUnique(){
  var col = 1 ; // column B (Email Address)
  var ss = SpreadsheetApp.getActive();
  var allsheets = ss.getSheets();

  // Array listing sheets to exclude from execution
  var exclude = ["Reference","Index"];

 for (var s in allsheets){
 var source_sheet = allsheets[s];   

    // Stop iteration execution; exclude Reference and Index tabs.
  if(exclude.indexOf(source_sheet.getName()) != -1) continue;

  var data=source_sheet.getDataRange().getValues();// get all data
  //Logger.log(data);
  var newdata = new Array();
  for(nn in data){
    var duplicate = false;
    for(j in newdata){
  
      if(data[nn][col] == newdata[j][0]){
        duplicate = true;
      }
    }

    if(!duplicate){
      newdata.push([data[nn][col]]);
    }
  
  }
Logger.log(newdata);
  newdata.sort(function(x,y){
  var xp = Number(x[0]);// ensure you get numbers
  var yp = Number(y[0]);
  return xp == yp ? 0 : xp < yp ? -1 : 1;// sort on numeric ascending
});
//Logger.log(newdata); // Uniques?
  }
}

Dummy Spreadsheet

Use [...new Set()] To Get The Unique Values of the Array

Gather all of the email addresses from all sheets (excluding the ignored sheets) and then transform the resulting array into a 1 dimensional array. Afterwards, you only need to apply:

var out = [...new Set(array)];

to your data. The resulting script should look like this (I have added some comments for guidance):

function findUnique() {
  var exclude = ["Reference","Index"]; // list all sheets to be ignored
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets().map(x=>x.getSheetName()).filter(x=>!exclude.includes(x)); //gets the sheet names to be processed
  var data = sh.map(x => ss.getSheetByName(x).getDataRange().getValues()).flat().map(y => y[1]); //gets all the data from all sheets
  var out = [... new Set(data)]; //gets the unique emails from the data
  out.shift(); // removes the header also, you can use sort after this line if you want
  console.log(out); //displays output on the execution log
}

Output

References:

• JavaScript Sets
• JavaScript Map
• JavaScript Array Methods

---

Looking for a match on form Submission

• Create an installable 'onFormSubmit' trigger in the spreadsheet
• Enable Event objects by adding e to the function argument
• var email = e.namedValues['Email Address'] - get the submitted email address
• var result = out.indexOf(email); use indexOf to look for a match in out (the list of emails)
• indexOf The indexOf() returns the first index at which the email can be found in the array OR -1 if it is not present.
  • if (result == -1){ - so test for a result of -1

---

function findUnique(e) {
  Logger.log(JSON.stringify(e))
  var email = e.namedValues['Email Address']
  Logger.log("the sumbitted email address = "+email)
  var exclude = ["Reference","Index"]; // list all sheets to be ignored
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets().map(x=>x.getSheetName()).filter(x=>!exclude.includes(x)); //gets the sheet names to be processed
  var data = sh.map(x => ss.getSheetByName(x).getDataRange().getValues()).flat().map(y => y[1]); //gets all the data from all sheets
  var out = [... new Set(data)]; //gets the unique emails from the data
  out.shift(); // removes the header also, you can use sort after this line if you want
  console.log(out); //displays output on the execution log
  
  // use indexOf to find a match in the existing emails
  // if = -1, then no match;
  var result = out.indexOf(email);
  Logger.log("DEBUG: the result of the mail match is "+result)

  if (result == -1){
    // email doesn't exist, create a new template
    console.log("DEBUG: Email does not exists so create new Dummy Template")
    
  }
  else{
    // email exists, so template exists
    console.log("DEBUG: Email exists so Dummy template already exists")
  }
}

---

SAMPLE LOG - email submission

---