I have a column with strings containing a name and an ID concatenated. How can I extract just the name part of the string and use that for filtering a dataset and copying it over to another worksheet in the google sheets file using the google apps script? The main thing I'm having difficulty with is just extracting the names from the column since they can vary in length. Additionally, I'm trying to perform automation using google apps script, so I want to avoid making a column manually and use something like regex.

Below is an example of how the column values look:

CodePudding user response：

We can use regular expressions here:

var input = "Adam500";
var regExp = new RegExp("([a-z] )", "i")
var name = regExp.exec(input)[1];
Logger.log(name);  // Adam

CodePudding user response：

I believe your goal is as follows.

• You want to copy the following values on a column of the source sheet.

    Identity
    Jane100
    Adam500
    Adam500
    Erica234
    Jessica8
  

• And, you want to paste the following values to a column of the destination sheet (in the same Spreadsheet) by converting from the above values.

    Identity
    Jane
    Adam
    Adam
    Erica
    Jessica
  

• You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script 1:

function sample1() {
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.
  const srcColumn = 1; // Values are retrieved from column "A".
  const dstColumn = 1; // Values are put to the column "A".

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(e => ss.getSheetByName(e));
  const srcValues = srcSheet.getRange(1, srcColumn, srcSheet.getLastRow()).getValues();
  const dstValues = srcValues.map(([a], i) => [i == 0 || !a.toString() ? a : a.replace(/\d /g, "")]);
  dstSheet.getRange(1, dstColumn, dstValues.length).setValues(dstValues);
}

• In this sample script, the values are retrieved from the column of the source sheet, and the converted values are put on the destination sheet.

Sample script 2:

function sample2() {
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.
  const srcColumn = 1; // Values are retrieved from column "A".
  const dstColumn = 1; // Values are put to the column "A".

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(e => ss.getSheetByName(e));
  const last = srcSheet.getLastRow();
  const dstRange = dstSheet.getRange(1, dstColumn);
  srcSheet.getRange(1, srcColumn, last).copyTo(dstRange);
  dstRange.offset(1, 0, last - 1).createTextFinder("\\d ").useRegularExpression(true).replaceAllWith("");
}

• In this sample script, the values are copied from the column of the source sheet to the destination sheet. And, the copied values are converted. In this case, the text styles can be copied.

References:

• getValues()
• setValues(values)
• createTextFinder(findText)