Google Appscript: Custom function to concatenate only the initials from multiple rows based on a criteria

Question

Apologies if I can't explain it very well but I have a spreadsheet that I want to use to create usernames based on a naming convention criteria (e.g. Type 1 = given name 1st & 2nd initials + middle name initial + lastname).

I managed to achieve that with something similar below but as you can tell, it's very lengthy.

I'm new to coding and scripting in general so if there's a way to use a script for this, or even a shorter way to go about it using the existing functions in Google Spreadsheet, that'll be extremely helpful.

=IF((LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+(LEN(A2)>1))=1,(IF($F$2="Type 1",(LEFT(A2,1))&B2&C2)),IF((LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+(LEN(A2)>1))=2,(IF($F$2="Type 1",(LEFT(A2,1)&MID(A2,FIND(" ",A2)+1,1)&B2&C2))),IF((LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+(LEN(A2)>1))=3,(IF($F$2="Type 1",(LEFT(A2,1)&MID(A2,FIND(" ",A2)+1,1)&MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1)&B2&C2))),IF((LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+(LEN(A2)>1))=4,(IF($F$2="Type 1",(LEFT(A2,1)&MID(A2,FIND(" ",A2)+1,1)&MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1)&MID(A2,FIND(" ",A2,FIND(" ",A2,FIND(" ",A2)+1)+1)+1,1)&B2&C2))),IF((LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+(LEN(A2)>1))=5,(IF($F$2="Type 1",(LEFT(A2,1)&MID(A2,FIND(" ",A2)+1,1)&MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1)&MID(A2,FIND(" ",A2,FIND(" ",A2,FIND(" ",A2)+1)+1)+1,1)&MID(A2,FIND(" ",A2,FIND(" ",A2,FIND(" ",A2,FIND(" ",A2)+1)+1)+1)+1,1)&B2&C2))))))))

Where:

A2 = Given Names, B2 = Middle Initials, C2 = Last Names, $F$2 = Naming convention criteria "Type 1"

I added first argument there to count the words on A2 - I kept getting errors due to the FIND function not being able to work if there's a difference in the number of words in A2.

If I'm asking the wrong community, kindly point me in the right direction. Any help would be greatly appreciated.

You can do what you want with a script. If you don't know JavaScript, you'd need to at least become familiar with some basics. But you're putting lots of energy into a using a complicated function. — Alan Wells, Mar 04 '15 at 13:09
I agree, I figured I'd read up on Google's Appscript but it was a little overwhelming for someone that's not really used to the entire thing. Thanks for the code you gave below, will be adding if arguments to it and see how it goes :) Have an awesome day! — tai, Mar 05 '15 at 02:59

score 0 · Answer 1 · answered Mar 04 '15 at 13:41

Here is some basic code to get you started:

function getCellValues() {
  var rangeNotation = "A2:C2";

  var theSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var theSheet = theSpreadSheet.getActiveSheet();

  var theRangeToGet = theSheet.getRange(rangeNotation);
  var theValues = theRangeToGet.getValues(); //Creates a two dimensional array

  var concatenatedValues = "";

  for (var i = 0;i < theValues[0].length; i++) {
    concatenatedValues += theValues[0][i];
  };

  var rangeToPutValue = theSheet.getRange("B4");
  rangeToPutValue.setValue(concatenatedValues);
};

Google Appscript: Custom function to concatenate only the initials from multiple rows based on a criteria

1 Answers1