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;
}
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:
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
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)
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.
Jeff
I have tried to use your script but having copied into a new script I am getting an error at line 34
âSyntaxError: Unexpected token âforâ (line 34,â
line 34 is
valuesSheet2 = [i for each (i in valuesSheet2)if ((i)[0].trim()!="")];
I am new to scripts and would appreciate some guidance on what is not working if at all possible please?
Every time a user logs in the google sheet is updated twice
2a. There is an app:login which has entry of the login
2b. In the âUser Managementâ an unique entry is being added with just the email ID
