javascript - 将正则表达式值与切片值连接起来

Question

我有以下函数将 B 列中的值与 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);
}

我只需要取valuesB的前三个字母（所以没有空格和特殊字符）和valuesL的最后八个字母。我该怎么做？

标签： javascriptgoogle-apps-scriptconcatenationslice

Answer 1

由于.getValues()返回显然可以转换为字符串的对象，因此您可以

1. 将String构造函数转换为常规字符串作为函数将其转换为字符串。

2. 使用String#replace正则表达式：

   • /[^a-zA-Z]/g它将替换任何不是字母的东西
   • /[^0-9]/g这将替换任何不是数字的东西

3. 调用String#slice剩下的东西来获得你想要的角色。如果您将负索引传递给.slice()它，它将从末尾获取项目。

所以你需要的一切都是：

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

然后您可以在代码中使用它：

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];
}

由于 Google Scripts 支持现代 ES6+ 语法，因此您可以获得更紧凑的代码：

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