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

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

• javascript
• google-sheets
• google-apps-script

1 Answer 1

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

The key aspect of this additional code is:

• ALL emails will appear in the data array; whether it is the first submission or the nth submission
• so we add an extra element to make a count of the number of occurrences of each unique email address
  • if the count >1, then this is a subsequent submission and the Template has already been created;
  • else this must be the first submission so the Template needs to be created

• 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
• let uA = [...new Set(data)].sort((a,b) => {return b - a}).filter(e => e).map(e => [e,0]) - create an array of unique email addresses and a second element created for the count
• if (uA[j][0] === data[i]) { uA[j][1]++
  • create a nested loop: #1-the data array, #2-the uA (Unique addresses) array
  • increment the counter in uA for each occurrence of an email address.
• var emailAddresses = uA.map(function(e){return "'"+e[0]+"'"}) - create a temporary array holding just the unique email addresses.
• var result = emailAddresses.indexOf(email) use indexOf to look for a match of the submitted email in `the temporary array (the list of unique emails)
• uA[result][1] - this is the count of occurences in the array of unique email addresses

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

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.