Email registration reference

Hi, where can I find the email addresses from the registration (when I use public with email option to register)? There is App:Logins tab but is it the one? Records are not unique in this sheet.
I need to reference to registration emails and update dynamically another tab to create records (UNIQUE formula) of all people registering to the application.

Just to give you an example:
User signs up with email, goes to the HOME tab and his registered email is displayed on the tab and created in the personal info sheet so I don’t need to update this information manually in the sheet.

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;
    }
2 Likes

Maybe to expand as a feature request, It would be nice if Glide had an option to auto populate any sheet with unique emails so a user would have a personal Per User Request record already populated whenever they log in. I had a similar but different request regarding the Unique ID Special Value. In my case I could eliminate my script if a new record in one sheet would populate others sheets as well with the Unique ID:

https://community.glideapps.com/t/please-test-new-entry-styles-unique-identifier-and-more/1870/9?u=jeff_hager

1 Like

Exactly, I didn’t check your script but even using unique feature I can’t make it work. Sheet formula is blocked by the fact that I have set up the “filter rows by sign in user” as this works by the mail reference it can’t have any formulas on the column :frowning:

A column with a formula in the sheet shouldn’t stop you from using that column for for Per User Data. Glide shouldn’t be aware of any formula’s in use on the sheet. It just looks at the data that displays in the cell. I’m not sure why it’s not working for you, but you could try creating a second column that grabs the emails from the first column. Then use the second column to filter by signed in user (Per User Data)

Thanks for the summary here. I am also looking for this feature. Will play around with the scripting, as well :+1:

So I made a per user data option and it doesn’t want to expand the formula because it uses email for that. I even did second column to “copy” mail and reference per user data to column D but as it’s a reference, it still has problem with original column C.

1 Like

You have to clear the data below the formula so it can propagate down.

2 Likes

Omg silly me! Thanks it helped :slight_smile:

1 Like