2

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.

Marios
  • 26,333
  • 8
  • 32
  • 52
kendy
  • 179
  • 11
  • Could you elaborate *let say I know or I can set the first name of the email lists.*? If the question is just "How to auto populate?", [this](https://stackoverflow.com/q/63837444/) should answer your question. – TheMaster Sep 21 '20 at 08:00
  • The data on the sheets came from a Google form... so if the new data is came from (let say ken@gmail.com). I want to auto populate the column D with data "Ken" on it. – kendy Sep 21 '20 at 09:09
  • But how is "Ken" associated with "ken@gmail.com"? Do you have a list? Have you tried anything to populate it? – TheMaster Sep 21 '20 at 09:30
  • Hmmm. if I can set it on the appscript like.. if you see the value "ken@gmail.com" then the return value will be "Ken" – kendy Sep 21 '20 at 09:38
  • Yes you can. Preferably with Object/Map(key-value pairs). – TheMaster Sep 21 '20 at 09:40
  • Cool.. can you point me on some example? or sample code on how to set it? – kendy Sep 21 '20 at 09:43
  • See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for "Codelabs" – TheMaster Sep 21 '20 at 11:01

1 Answers1

3

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);
Marios
  • 26,333
  • 8
  • 32
  • 52
  • Is there a way for me to set the NAME value? let say for email (ben02@hotmail) the NAME value will be Benjamin? – kendy Sep 21 '20 at 09:24
  • Is this doable in Appscript? – kendy Sep 21 '20 at 09:25
  • No, I don't think you can do that. You can only do that manually like if(d2="Ben","Benjamin) or something. You can use GAS but this formula will be way faster a more maintanable in the future. @kendy – Marios Sep 21 '20 at 09:26
  • what do mean by manually do it by (d2="Ben","Benjamin)? where do I set it? – kendy Sep 21 '20 at 09:36
  • @kendy see sample code regarding a Google Apps Script solution. Could you let me know if that worked for you? – Marios Sep 21 '20 at 11:40
  • I'm a little bit confuse... where is the part where I set the email (ben02@hotmail) that get the name "Ben" to "Benjamin". – kendy Sep 21 '20 at 12:55
  • @kendy replace the google formula in my solution with this and put it again in cell D1: `=ARRAYFORMULA({"NAME";if(proper(REGEXEXTRACT(B2:INDEX(B:B,COUNTA(B:B)),"^[a-z]+"))="Ben","Benjamin",proper(REGEXEXTRACT(B2:INDEX(B:B,COUNTA(B:B)),"^[a-z]+")))})` – Marios Sep 21 '20 at 13:02
  • I think I'll use this ARRAYFORMULA...last question so I can still use this formula to set the other name like if I set the email (ken@gmail.com) that get the name "Ken" to "Kendy". together with "Ben" to "Benjamin" – kendy Sep 21 '20 at 13:14
  • 1
    `=ARRAYFORMULA({"NAME";if(proper(REGEXEXTRACT(B2:INDEX(B:B,COUNTA(B:B)),"^[a-z]+"))="Ben","Benjamin",if(proper(REGEXEXTRACT(B2:INDEX(B:B,COUNTA(B:B)),"^[a-z]+"))="Ken","Kendy",proper(REGEXEXTRACT(B2:INDEX(B:B,COUNTA(B:B)),"^[a-z]+"))))})` @kendy – Marios Sep 21 '20 at 13:19