I have read many posts about this on SO. I am having trouble using Regex in google scripts for sheets.

I have a list of domains.

Goal: I want to remove all the path information from every domain in the list (everything after the .*/)

For example, I want to turn this:

example.com/path-after-domain

example2.com/another-path-afterDomain

into this:

example1.com

example2.com

Here is what I am trying to do.

Looping and replace function

function replaceInSheet(values, to_replace, replace_with) {
  
  for(var row in values){
 
  var replaced_values = values[row].map(function(original_value) {
    return original_value.toString().replace(to_replace,replace_with)
  })
}
}

Using the function by passing Regex

replaceInSheet(values, '((\/\w )*\/)(.*)', "")

I also tested using createtextfinder. But I get an error when trying to use regex

const check = range.createTextFinder("((\/\w )*\/)(.*)").useRegularExpression(true)

check.replaceWith("")

Note - I tested the regex with https://regex101.com/ and it successfully gets all path information

CodePudding user response：

I believe your goal is as follows.

• You want to achieve the following conversion using Google Apps Script.

  • From

      example.com/path-after-domain, example2.com/another-path-afterDomain
    

  • To

      example1.com, example2.com
    

In this case, how about using split as follows?

Sample script:

const values = ["example.com/path-after-domain", "example2.com/another-path-afterDomain"];
const res = values.map(e => e.split("/")[0]);
console.log(res) // [ 'example.com', 'example2.com' ]

Note:

• If you want to use this as a custom function, how about the following sample script? In this case, please put a custom function like =SAMPLE(A1:A10) to a cell.

    const SAMPLE = values => values.map(r => r.map(c => c.split("/")[0]));
  

• If you want to use TextFinder using your regex of ((\/\w )*\/)(.*), how about the following sample script? In this case, please escape \. I thought that \\/.* might be able to be also used.

    const sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange("A1:A"   sheet.getLastRow()).createTextFinder("((\\/\\w )*\\/)(.*)").useRegularExpression(true).replaceAllWith("");
  

  • In this case, the column "A" is used.

• If the built-in function is used, how about the following sample formula?

    =ARRAYFORMULA(IFERROR(INDEX(SPLIT(A1:A,"/"),, 1)))
  
    =ARRAYFORMULA(REGEXREPLACE(A1:A,"/.*",""))
  

Reference:

• split()