Email registration reference

Ask for Help
2

App:Logins would be the place to get emails. You can use a UNIQUE formula in a new sheet to build a list, but you could run into problems if have other columns in your new sheet that need to match up to the email being pulled from App:Logins. I wouldn’t trust googles UNIQUE function in this case. Things can get out of whack if you’re not careful, as the order of emails could change. Instead I would use a script that will check for unique emails and apply them to another sheet. I use the following script to copy only new unique names from one sheet to another. It’s designed to start at row 3 and column 1 and then look at all the following rows in that column. The script uses an OnChange trigger to run anytime there are updates to the sheet. You will have to modify it for your use, but I hope it helps. If somebody has a much better way to accomplish this, I’m all ears. I try to stay away from scripting whenever possible, but some things you cant avoid.

  // You can activate a trigger on function copy_new_names()
    // Assuming that row 1 and 2 in each sheet is not included in the values to be processed...

    function CopyName() {
      var SheetID = '***** Your Sheet ID Here *****'
      if (SpreadsheetApp.getActiveSheet().getName() == "LTS_Students") {
        copy_new_names('LTS_Students', 'LTS_Tests', SheetID);
      }
      if (SpreadsheetApp.getActiveSheet().getName() == "Advanced_Students") {
        copy_new_names('Advanced_Students', 'Moves_Tests', SheetID);
        copy_new_names('Advanced_Students', 'FreeStyle_Tests', SheetID);
        copy_new_names('Advanced_Students', 'Dance_Tests', SheetID);
      }
    }    

    function copy_new_names(SheetFrom, SheetTo, SheetID) {
      var spreadsheet = SpreadsheetApp.openById(SheetID);
      var sheet = spreadsheet.getSheetByName(SheetFrom);
      var sheet2 = spreadsheet.getSheetByName(SheetTo);
      var endrow = sheet.getLastRow(), endcol = sheet.getLastColumn();
      var endrow2 = sheet2.getLastRow(), endcol2 = sheet2.getLastColumn();
      // assuming that row 1 and 2 is not included in the values to be processed
      var valuesSheet1 = sheet.getRange(3, 1, endrow-2, 1).getValues(), valuesSheet2 = [];
      if ( endrow2 > 2 ) valuesSheet2 = sheet2.getRange(3, 1, endrow2-2, 1).getValues();
      valuesSheet2 = [i for each (i in valuesSheet2)if ((i)[0].trim()!="")];

      var newlist = checkNameList(valuesSheet1, valuesSheet2);
      //Below to paste new list in sheet2 
      sheet2.getRange(3, 1, newlist.length, 1).setValues(newlist);
    }

    function checkNameList(valuesSheet1, valuesSheet2) {
      var values1 = valuesSheet1.map(function(d) { return d.join()}), values2 = [];
      if ( valuesSheet2.length ) values2 = valuesSheet2.map(function(d) { return d.join()});
      var newList = [];
        for ( var i in values1 ) {
          if ( values2.indexOf(values1[i]) === -1 && values1[i].length>0) newList.push([values1[i]]);
        }  
      newList = valuesSheet2.concat(newList);
      //Logger.log(newList)
      return newList;
    }
4 Likes