I use a script to automatically insert the date and username to fill in the string. Several people fill out the table. How can I modify the script so that the first name and the last name are inserted instead of the username?
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var idCol = e.range.getColumn();
var idRow = e.range.getRow();
if ( idCol == 4 && sheet.getName() =='LIST1' ) {
var Value = e.range.offset(0, -2).getValues();
if ( Value == "" ) {
var vartoday = getDate(); //Gets the current date//
sheet.getRange(idRow, 2).setValue( vartoday ); //Inserts into column 2 of the current row//
var u = Session.getEffectiveUser().getUsername(); //Gets the username of the editor//
sheet.getRange(idRow, 7).setValue(u); //Inserts into column 7 of the current row//
}
}
function getDate() {
var today = new Date();
today.setDate(today.getDate());
return Utilities.formatDate(today, 'GMT+03:00', 'dd/MM/yy');
}
}
Issue:
getUsername()
only gives the email address without the domain part (that is, the content before@
). It doesn't provide information on the name or surname, assuming that not every email address is formatted in a distinguishable way, as suggested in other answers (e.g.name.surname
).If you want to access the actual name and surname of a user, you would need to access the Users resource from Directory API. The easiest way to do that is to use the advanced Admin SDK Directory Service on your script.
Important note: This requires the use of Admin SDK, which can only be used by Google Workspace (formerly G Suite) accounts. If you are not a Workspace user, there's no way to retrieve this information.
Workflow:
Resources > Advanced Google Services...
, set thedirectory_v1
toon
and clickOK
: see example.onEdit(e)
trigger. First, change the name of your function so that it's not calledonEdit
. That's a reserved function name, to be used for simple triggers (I called itfireOnEdit
on the code sample below).e.user
) instead.User.name.givenName
) and family name (User.name.familyName
).Code sample:
Notes:
G
and the other to columnH
. I added both possibilities in the code sample, please comment/uncomment the corresponding lines according to your preferences.