CODEWITHSUNDEEP
javascriptvbavbscriptexcelgoogle-apps-script
Matt
Matt

Reputation: 1570

Google Spreadsheet Script RegEx on Range of Cells

Background (Optional): I wrote a working script in VBA that I'm trying to write in JS for Google Spreadsheets, but I'm having a tremendous amount of difficulty with the regex validation on a per cell basis.

In this VBA code snippet, I set a range of data, and test each cell against a RegEx that is previously defined.

Set rRange = Range(arrLetters1(i) & intRange1, arrLetters2(i) & intRange2)

For Each rCell In rRange.Cells
  If re.Test(rCell) Then
    rCell.Interior.Color = RGB(0, 250, 0)
  Else
    Cells((intRange1 - 1), rCell.Column).Interior.Color = RGB(250, 0, 0)
    rCell.Interior.Color = RGB(250, 0, 0)
  End If
Next rCell

What I'm curious about, is the actual JavaScript function that would allow me to look through the same range and perform the same operation. Here's what I have:

var re = "[a-z]+"
var rRange = sheet.getRange(arrLetters1(i) + intRange1, arrLetters2(i) + intRange2)

for (var rCell in rRange) {
  if (rCell //is a "re" match) {
      //do some code
  }
} else {
     //do something else
}

Upvotes: 3

Views: 5736

Answers (2)

jatrim
jatrim

Reputation: 715

Okay, quickly looking this over, it looks like you want to examine the values in a range of cells, compare them to regular expressions and change the cell colors based on the results. Keeping in mind that I haven't used the google docs api before, it looks like there are just a couple of minor issues with your existing code.

Firstly, you need to create your regular expressions correctly.

Your line:

var arrPatterns = new Array("^([A-Z]{2}(-[0-9]{5}){4})$", "^[A-Z]{2}$", "^[0-9]{5}$", "^[a-z]{1,}$", "^\(\d\d\d\) \d\d\d-\d\d\d\d$", "^([a-z0-9]{1,}[,]{0,}){1,}$", "^(\d\d?:\d\d[aApP][mM] - \d\d?:\d\d[aApP][mM])|(Closed)|(All Day)$");

Becomes:

var arrPatterns = [/^([A-Z]{2}(-[0-9]{5}){4})$/, /^[A-Z]{2}$/, ... /^(\d\d?:\d\d[aApP][mM] - \d\d?:\d\d[aApP][mM])|(Closed)|(All Day)$/];

The two changes there are dropping new Array in favor of just using the literal array syntax of [ ] (optional but considered good practice), and replacing the quotes in your array strings with forward slashes (the regular expression literal syntax). The result is an array of regular expression objects instead of strings. That will allow you to actually do something like re.test("some string"); like you've done in your evaluation code.

Secondly, you need to loop over the range of cells, get their values and do the comparison. A quick examination of the documentation tells me that sheet.getRange returns a Range object. The range object has a getValues method that returns an array of arrays of cell values (array[][]).

So some quickly approximate code for what you want is:

var rRange = sheet.getRange(arrLetters1(i) + intRange1, arrLetters2(i) + intRange2);
var values = rRange.getValues();
var re = arrPatterns[0];

var row, col;
for (row = 0; rows < values.length; row++) {
  for (col = 0; col < values[row].length; col++) {
    if (re.test(values[row][col])) {
        /*do passes regex, looks like you probably need to pass the row/col to getCell
          to get a range with the desired cell in it and then call setBackgroundColor on
          that range.  I'm also leaving looping through your array of regular expressions to you,
          as it looked like you have a dependency on the expression being used and the array of
          letters i didn't take the time to understand.*/
    }
    else {
      //do fails regex
    }
  }
}

Upvotes: 2

peter
peter

Reputation: 42207

Here 2 examples how to work with RegEx in JS, the WScript.Echo is JScript for easier testing, replace with document.write or response.write or whatever

var rRange = "this string is for testpurposes"
var re = /[a-z]+/
var regExp = new RegExp(re);
if (rRange.match(regExp)) {
  WScript.echo("Successful match");
} else {
  WScript.echo("No match");
}

=>Successful match

var str="The rain in SPAIN stays mainly in the plain"; 
var n=str.match(/ain/g);
WScript.echo(n); 
=> ain,ain,ain

Upvotes: 1

Related Questions