Split the data using regex with google script

Question

As a newbie, I have tried a lot to solve the below problem.

My Current table

    TestID            TestName                  Name       Url
    1592461  Google-page (www.google.com)
    1592467  Yahoo - Page (www.yahoo.com)

I am trying to split the data present in the column "TestName" and add the result to the columns "Name" and "URL" as given in the below table

Expected table

    TestID         TestName                      Name               Url
    1592461  Google-page (www.google.com)     Google-page    www.google.com
    1592467  Yahoo - Page (www.yahoo.com)     Yahoo - Page   www.yahoo.com

I have tried to compile the following script but was unsuccessful.

  function getUrl(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName("Sheet1");
  var s2 = ss.getSheetByName("Sheet2");
  var data = s1.getSheetValues(1, 2, s1.getLastRow() , 1);
  var regExp = new RegExp("\(([^]]+)\)");

  var row = [];

  for(i = 0; i<data; i++) {

      var url = regExp.exec(data)[i];
      var output = s2.getRange("C2").setValue(url);

      logger.log(url);
      return url;
  }
}

Could someone please help me in solving this.

Answer 1

In addition, I just wanted to let you know this can also be done with a (rather simple) formula. Enter in C1

=ArrayFormula(split(substitute(B2:B3, ")",""), "("))

Change range to suit.

Answer 2

I have an impression you want to get the data from Column 2 of the current spreadsheet into Column 3 and 4 in the same spreadsheet.

I suggest using the following regex:

var regExp = /(.*?)\(([^)]+)\)/;

The (.*?) will capture any 0+ chars other than line break chars into Group 1 (all before () then \( will match a ( and then ([^)]+) will capture 1+ chars other than ) into Group 2 (the URL) and then the \) will match a ).

And use it to analyze Column B data:

function getUrl(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName("Sheet1");
  var src_range = s1.getRange("B:B");  // Grab the Column B range
  var regExp = /(.*?)\(([^)]+)\)/;     // Define the regex
  for(i = 1; i<=src_range.getLastRow(); i++) { // Loop through all the cells in the range
    if (!src_range.getCell(i, 1).isBlank()) {  // If the cell is not blank, process it
      var m =  regExp.exec(src_range.getCell(i, 1).getValue()); // Run the regex
      if (m) {            // If there is a match
        var text = m[1];  // Text to be placed into Column C
        s1.getRange('C' + i).setValue(text);
        var url = m[2];   // URL to be placed into Column D
        s1.getRange('D' + i).setValue(url);
      }
    }
  }
}

See a sample document.