javascriptgoogle-sheetsgoogle-apps-script

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

enter image description here

Solution

  • 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

    output

    References:

    Looking for a match on form Submission

    The key aspect of this additional code is:

    function findUnique(e) {
  // Logger.log(JSON.stringify(e)) // DEBUG
  var email = "'"+e.namedValues["Email Address"]+"'"
  Logger.log("DEBUG: 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
  //Logger.log("the sheets to be processed are "+sh)
  var data = sh.map(x => ss.getSheetByName(x).getDataRange().getValues()).flat().map(y => y[1]); //gets all the data from all sheets
  // Logger.log(data) // DEBUG
  // Logger.log("DEBUG: data length = "+data.length)
  let uA = [...new Set(data)].sort((a,b) => {return b - a}).filter(e => e).map(e => [e,0]);
  // Logger.log(uA) // DEBUG
  // Logger.log("DEBUG: No of Unique values = "+uA.length)

  // Count the occurrences of each unique value.
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < uA.length; j++) {
      if (uA[j][0] === data[i]) {
        uA[j][1]++;
      }
    }
  } 
  // Logger.log(uA) // DEBUG
  // create an array of the email addresses
  var emailAddresses = uA.map(function(e){return "'"+e[0]+"'"})
  // Logger.log(emailAddresses) // DEBUG
  // find the email address and the count in the unqiue values
  var result = emailAddresses.indexOf(email)
  // Logger.log("DEBUG: result = "+result)
  Logger.log("DEBUG: the count for this email address = "+uA[result][1])
  if (uA[result][1] == -1){
    //couldn't find the email, somewrong
    // OP should insert appropriate warning
    return
  }
  if (uA[result][1]>1){
    // email exists, so template exists
    Logger.log("DEBUG: Email exists so Dummy template already exists")
  }else{
    // only one occurance, create the template
    Logger.log("DEBUG: First instance of email so create new Dummy Template")
  }
}

    SAMPLE LOG - email submission

    log

    Hat tip: @Cooper for the code to create the array of unique email addresses and provision for a count in Google Apps Script - Unique values results are not Unique.