Question

0

147

Vistas

Find Replace In Google Sheets App Scripts With Regex Not Working

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

about 4 years ago · Juan Pablo Isaza

1 Respuestas

Responde la pregunta

0

Responde la pregunta

Encuentra empleos remotos

Accepted Answer · 2022-08-31T04:51:36.602Z

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

0

147