Get Name and Surname from Username in Google Apps Script

Question

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');
  }
}

Answer 1

Looks like you are storing the username here -

var u = Session.getEffectiveUser().getUsername();  //Gets the username of the editor//

And then inserting the username here -

sheet.getRange(idRow, 7).setValue(u); //Inserts into column 7 of the current row//

You will want to split the name before you store it. Something like this -

var first_name = // Split by your delimeter here and store first name 
var last_name = // Split by your delimeter here and store last name 
sheet.getRange(idRow, 7).setValue(first_name); //Inserts into column 7 of the current row//
sheet.getRange(idRow, 8).setValue(last_name); //Inserts into column 8 of the current row//

Answer 2

Explanation:

Assuming that the username has the format of name.surname then you can use split() to separate the name from the surname by using u.split('.')[0] and u.split('.')[1] respectively.

If the username has a different format then you can change the argument accordingly. For example, if you have name-surname then use u.split('-')[0] and u.split('-')[1].

---

Solution:

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.split('.')[0]); //Inserts into column 7 of the current row the firstname of the editor//
sheet.getRange(idRow, 8).setValue(u.split('.')[1]); //Inserts into column 8 of the current row the last of the editor//
}
}
function getDate() {
var today = new Date();
today.setDate(today.getDate());
return Utilities.formatDate(today, 'GMT+03:00', 'dd/MM/yy');
}
}

Answer 3

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:

• Enable the advanced Directory Service on your script by clicking Resources > Advanced Google Services..., set the directory_v1 to on and click OK: see example.
• Since accessing this API requires authorization, and simple triggers cannot access service that require authorization (see Restrictions), you will have to install the onEdit(e) trigger. First, change the name of your function so that it's not called onEdit. That's a reserved function name, to be used for simple triggers (I called it fireOnEdit on the code sample below).
• Install the trigger, either manually (see example settings) or programmatically.
• In installed triggers, the effective user is the user who installed the trigger, not the one whose edit triggered the script. Therefore, you would need to use getActiveUser(), or the event object (property e.user) instead.
• Using the Advanced service, retrieve the active user properties by calling Users: get.
• Retrieve the two desired fields from the API response: first name (User.name.givenName) and family name (User.name.familyName).

Code sample:

function fireOnEdit(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 email = e.user.getEmail();
      var user = AdminDirectory.Users.get(email);
      var name = user.name.givenName;
      var surname = user.name.familyName;
      //sheet.getRange(idRow, 7).setValue(name + " " + surname); // Name and surname copied to the same cell in G
      sheet.getRange(idRow, 7, 1, 2).setValues([[name, surname]]); // Name and surname copied to G and H
    }
  }
  function getDate() {
    var today = new Date();
    today.setDate(today.getDate());
    return Utilities.formatDate(today, 'GMT+03:00', 'dd/MM/yy');
  }
}

Notes:

• Information on the active user might not always be available (for example, if the user who created the trigger and the one causing the script to run don't belong to the same domain). See this answer, for example.
• You would need to install the trigger with an account that has access to the Users resource from Directory API.
• I'm unsure whether you want to concatenate both first name and surname in the same cell, or add one to column G and the other to column H. I added both possibilities in the code sample, please comment/uncomment the corresponding lines according to your preferences.

Answer 4

If you're a Gsuite/Google workspace customer, You can use Directory API as mentioned in this answer.

If not, You may be able to leverage the identity token provided by ScriptApp.

★ You need to get explicit permission from each of your editor to get their name. Without getting explicit permission, You will not be able to log their edits programmatically. The built in feature "edit history" would still log them though.

Flow:

• Get current script scopes from File > Project properties> Scopes
• Add explicit openid, profile and email scopes to the above scopes and add them to the manifest. For eg, the following sample script requires the following scopes:

  "oauthScopes":["openid","profile","email",
                 "https://www.googleapis.com/auth/script.scriptapp",
                 "https://www.googleapis.com/auth/spreadsheets.currentonly",
                 "https://www.googleapis.com/auth/script.container.ui"
                ],

• Ask editors to sign up to add them to the edit log by clicking a menu button

• Create a installed Edit trigger for them on clicking the menu button.

• Use the installable edit trigger to get a identity token. Parse the token to get given name and family name.

Sample script:

function getNameOfCurrentEditor() {
  const idToken = ScriptApp.getIdentityToken();
  const body = idToken.split('.')[1];
  const decoded = Utilities.newBlob(
    Utilities.base64Decode(body)
  ).getDataAsString();
  const { given_name: firstName, family_name: lastName } = JSON.parse(decoded);
  return { firstName, lastName };
}

/**
 * @param{GoogleAppsScript.Events.SheetsOnEdit} e
 */
function installedEditTrigger(e) {
  const eUser = e.user.getEmail();
  if (eUser === '') return; //no permission=> exit
  const { firstName, lastName } = getNameOfCurrentEditor();
  e.range.setNote(
    `${e.range.getNote()}\n${e.value} added by ${firstName}_${lastName}`
  );
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Edit Logger')
    .addItem('Sign me up!', 'createEditTrigger')
    .addToUi();
}

function createEditTrigger() {
  ScriptApp.newTrigger('installedEditTrigger')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create();
}

Note: Multiple edit triggers for all editors, who signed up will run automatically, but only the editor who actually made the edit will be allowed to pass beyond this condition:if (eUser === ''). This works because each editor is unable to get email addresses of other editors. Only a empty string is returned in that case.