How to set value in another column of Google Sheets based on certain value of column in the same sheets

user_12444995 0
Viewed 826

I have this sheets that has a lists of email address. I want to auto populate the Column D base on the value of email in column B.

enter image description here

What I want to auto populate in column D is the first name of the email Address.

What I want, is to look like this:

enter image description here

let say I know or I can set the first name of the email lists.

  • ken@gmail.com = Ken

  • ben02@hotmail.com = Ben

  • kobe.brayant@gmail.com = Kobe

  • lebronJAMES@gmail.com = Lebron

The question is how to auto populate column D or (NAME) base on the value of Email Address.

Note that the data (Column A to C) is auto populated base on the Google Forms.

google-forms google-sheets-formula google-apps-script google-sheets javascript
1 Answers

Try this arrayformula in cell D1:

=ARRAYFORMULA({"NAME";proper(REGEXEXTRACT(B2:INDEX(B:B,COUNTA(B:B)),"^[a-z]+"))})

output

As a complete solution in Google Apps Script you could do that:

function myFunction() {
  
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Form Responses 1");
  const emails = sh.getRange("B2:B"+sh.getLastRow()).getValues().flat();
  const names = emails.map(str=>str.match('^[a-z]+')[0])
                .map(name=>name.charAt(0).toUpperCase()+name.slice(1));
  sh.getRange("D2:D"+sh.getLastRow()).setValues(names.map(nm=>[nm]));
}

Assuming the sheet name is Form Responses 1.

For illustration:

const emails = ['ken@gmail.com','kobe.brayant@gmail.com',
                  'ben02@hotmail.com','lebronJAMES@gmail.com']; 
const names = emails.map(str=>str.match('^[a-z]+')[0])
                .map(name=>name.charAt(0).toUpperCase()+name.slice(1));
console.log(names);

user_112252910
Related