Concatenate regexed values with sliced values

Question

I have the following function that concatenate values from column B with values from column L:

function ConcatenateJS() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var lr = sheet.getLastRow();
    var valuesB = sheet.getRange(2, 2, lr,1).getValues();  //Get values for column B
    var valuesL = sheet.getRange(2, 12, lr,1).getValues();  //Get values for column L
    var results = [];                               //Create a empty array to be filled concatenated elements

    //Add items to results
    for(var i=0; i<lr; i++){
        results[i] = [valuesB[i][0]+"-"+valuesL[i][0]];
    }

    //Post back to column 1 starting on row 2
    sheet.getRange(2, 1, lr, 1).setValues(results);
}

I need to take only the first three letters of valuesB (so no spaces and special characters) and only the last eight letters of valuesL. How can I do it?

Answer 1

Since .getValues() returns objects which are apparently convertable to strings, you can

1. Convert to a regular string with the String constructor as a function to convert it to a string.

2. Use String#replace using a regular expression:

   • /[^a-zA-Z]/g which will replace anything that is not a letter
   • /[^0-9]/g which will replace anything that is not a number

3. Call String#slice on what remains to get the characters you want. If you pass a negative index to .slice() it takes items from the end.

So everything you need would be:

function lettersOnly(value) {
  return String(value).replace(/[^a-zA-Z]/g, "");
}

function numbersOnly(value) {
  return String(value).replace(/[^0-9]/g, "");
}

function formatValues(str1, str2){ 
  var value1 = lettersOnly(str1).slice(0, 3);
  var value2 = numbersOnly(str2).slice(-8);
  
  return [value1 + "-" + value2];
}

console.log(formatValues("abcdef", "0987654321"));
console.log(formatValues("a1_b -2c-d4e5f", "q-1*r-2+s-3't-4-u-5_v-6 w-7,x-8.y-9(z-0"));
console.log(formatValues("ab6", "xyz12345"));

console.log(formatValues("Johnny", "0987654321"));
console.log(formatValues("S-Uper Nick", "0987654321"));
console.log(formatValues("S4mm3r", "0987654321"));

const objB1 = { toString() { return "Johnny"; } };
const objB2 = { toString() { return "S-Uper Nick"; } };
const objB3 = { toString() { return "S4mm3r"; } };

const objL = { [Symbol.toPrimitive]() { return 10987654321; } };

console.log(formatValues(objB1, objL));
console.log(formatValues(objB2, objL));
console.log(formatValues(objB3, objL));

Which you can then use in your code:

function ConcatenateJS() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var lr = sheet.getLastRow();
    var valuesB = sheet.getRange(2, 2, lr,1).getValues();  //Get values for column B
    var valuesL = sheet.getRange(2, 12, lr,1).getValues();  //Get values for column L
    var results = [];                               //Create a empty array to be filled concatenated elements

    //Add items to results
    for(var i=0; i<lr; i++){
        results[i] = formatValues(valuesB[i][0], valuesL[i][0]);
    }

    //Post back to column 1 starting on row 2
    sheet.getRange(2, 1, lr, 1).setValues(results);
}

function lettersOnly(svaluetr) {
  return String(value).replace(/[^a-zA-Z]/g, "");
}

function numbersOnly(value) {
  return String(value).replace(/[^0-9]/g, "");
}

function formatValues(str1, str2){ 
  var value1 = lettersOnly(str1).slice(0, 3);
  var value2 = numbersOnly(str2).slice(-8);
  
  return [value1 + "-" + value2];
}

Since Google Scripts supports modern ES6+ syntax, you can get slightly more compact code:

const lettersOnly = value => String(value).replace(/[^a-zA-Z]/g, "");
const numbersOnly = value => String(value).replace(/[^0-9]/g, "");

const formatValues = (str1, str2) => [
  `${lettersOnly(str1).slice(0, 3)}-${numbersOnly(str2).slice(-8)}`
];

console.log(formatValues("abcdef", "0987654321"));
console.log(formatValues("a1_b -2c-d4e5f", "q-1*r-2+s-3't-4-u-5_v-6 w-7,x-8.y-9(z-0"));
console.log(formatValues("ab6", "xyz12345"));

console.log(formatValues("Johnny", "0987654321"));
console.log(formatValues("S-Uper Nick", "0987654321"));
console.log(formatValues("S4mm3r", "0987654321"));

const objB1 = { toString() { return "Johnny"; } };
const objB2 = { toString() { return "S-Uper Nick"; } };
const objB3 = { toString() { return "S4mm3r"; } };

const objL = { [Symbol.toPrimitive]() { return 10987654321; } };

console.log(formatValues(objB1, objL));
console.log(formatValues(objB2, objL));
console.log(formatValues(objB3, objL));