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?
}
}