in Google Apps Script; using Regular Expression & Conditional formatting - to create a IPv4 address validator / checker

Question

in Google Apps Script; using Regular Expression & Conditional formatting - to create a IPv4 address validaton / checker

1. VALIDATING the input with regular expression :

I try to make a script to get feedback of a validator, to check if the input an IP addresses is.

regular expression pattern :

cell ' ip!I12 ' named ' REGEXP_IP_pattern ' :

^((((25[0-5])|(2[0-4][0-9])|([01]?[0-9]{1,2}))\.){3}((25[0-5])|(2[0-4][0-9])|([01]?[0-9]{1,2})))

reg exp demo : https://regexr.com/54fon - works fine (in the formula)

formula in the cell :

works in the formula fine, but I have to work with formulas in formulas to define if the status is valid or not.

The regular expression in formula format works fine [see worksheet 'ip'], but the sad thing is that I fail to get it working in the script. Have some issues to retrieve the correct and good information, to define if a input 'ip' is valid or not. (see column 'input' , 'good ip')

in the formula : =IF( REGEXREPLACE(TRIM( <cell> &"");REGEXP_IP_pattern;wildChar_IP) = wildChar_IP;1;0)

• to force and cast the cell to a string : TRIM( <cell> &"")
• 'remove' the founded regexpmatch and on the rest of the string add a 'wildChar_IP' [a wild character]
• check if the rest is 'nothing' (in our case equal to the 'wildChar_IP')

script / code :

For the Google Apps Script, TOOLS > SCRIPT EDITOR

To see the output of the logger : VIEW > LOGBOOK

So how is it possible to get feedback, status, if it only matches with regular expression in a function?

for example a boolean as return.

This is my code ... but fails to define when it really matches. Because of autosense/autocomplete is not really reliable, is there an other way to find out all method of

var regExp = new RegExp(regExpPattIP, "gi"); var result01 = regExp.exec(ip01);

code :

var ip01 ='256.12.2.0'
var ip02 ='255.12.2.0'
var ip03 ='192.168..1'
var ip04 ='10.12.12.12.12'
var ip05 ='010.060.090.002'



function validatorRegExp() {

  //var regExpPattIP ="^((((25[0-5])|(2[0-4][0-9])|([01]?[0-9]{1,2}))\.){3}((25[0-5])|(2[0-4][0-9])|([01]?[0-9]{1,2})))";
  var regExpPattIP = actSheet.getRangeByName(RegExp_patt_IP).getValue();

  var regExp = new RegExp(regExpPattIP, "gi");  // "i" is for case insensitive --  "g" is for global
  var result01 = regExp.exec(ip01);
  console.log(" --- ip01: ", ip01, "---");
  console.log(result01);
  var result02 = regExp.exec(ip02);
  console.log(" --- ip02: ", ip02, "---");
  console.log(result02);
  var result03 = regExp.exec(ip03);
  console.log(" --- ip03: ", ip03, "---");
  console.log(result03);
  var result04 = regExp.exec(ip04);
  //var result04B = regExp.exec(ip04)[1]; // null - PROBLEM
  console.log(" --- ip04: ", ip04, "---");
  console.log(result04);
  // console.log(result04B);
  console.log(" --- ip05: ", ip05, "---");
  var result05 = regExp.exec(ip05);
  var result05B = regExp.exec(ip05)[1];
  console.log(result05);
  console.log(result05B);


}

RESULT

[20-05-12 23:54:45:052 CEST]  --- ip01:  256.12.2.0 ---
[20-05-12 23:54:45:054 CEST] null
[20-05-12 23:54:45:056 CEST]  --- ip02:  255.12.2.0 ---
[20-05-12 23:54:45:058 CEST] [ '255.12.2.0',
  '255.12.2.0',
  '2.',
  '2',
  undefined,
  undefined,
  '2',
  '0',
  undefined,
  undefined,
  '0',
  index: 0,
  input: '255.12.2.0',
  groups: undefined ]
[20-05-12 23:54:45:060 CEST]  --- ip03:  192.168..1 ---
[20-05-12 23:54:45:061 CEST] null
[20-05-12 23:54:45:063 CEST]  --- ip04:  10.12.12.12.12 ---   ****** is bad 
[20-05-12 23:54:45:064 CEST] [ '10.12.12.12',
  '10.12.12.12',
  '12.',
  '12',
  undefined,
  undefined,
  '12',
  '12',
  undefined,
  undefined,
  '12',
  index: 0,
  input: '10.12.12.12.12',
  groups: undefined ]
[20-05-12 23:54:45:066 CEST]  --- ip05:  010.060.090.002 ---
[20-05-12 23:54:45:068 CEST] null
[20-05-12 23:54:45:069 CEST] 010.060.090.002

If you think a 'valid' feature would be helpfull, vote up here : https://issuetracker.google.com/issues/36762591

2. VISUALISING the 'valid' / 'unvalid' *status* of the input with conditional formatting :

And the idea is then make it visual by 'conditional formatting' (https://developers.google.com/sheets/api/guides/conditional-format#apps-script)

All Help is more then welcome.

---

formula and script:

Link the spreadsheet and google script :

https://drive.google.com/open?id=1HTgdC6Ss8oOvwVm86kbOdlt5jX8UyVmd-Eyo-oWhEGw

(only read access, if you want to test/check it out make a copy)

REMARK : (continued) How can I dynamically apply a regular expression on change of a cell or in conditional format?

Google Apps Script; using Regular Expression on a dynamic way implemented, on change of a cell or in conditional format.

Answer 1

I believe your goal as follows.

• You want to output the boolean type when the IP addresses are checked using Google Apps Script.
• You want to check a column in the Spreadsheet.

For this, how about this answer?

Modification points:

• In this answer, as the regex for matching IPV4, ^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$ is used. Ref
• In order to check and return the boolean type, I used test() for this.

Sample script:

The sample script is as follows. In this case, it supposes that there are the IP addresses in the column "A". And the output value is put to the column "B".

function myFunction() {
  const regExpPattIP = '^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$';
  const regExp = new RegExp(regExpPattIP);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const range = sheet.getRange("A1:A" + sheet.getLastRow());
  const values = range.getDisplayValues();
  var res = values.map(([e]) => ([regExp.test(e.trim())]));
  range.offset(0, 1).setValues(res);
}

Note:

• When the following modification is reflected to above sample script, the cell color of the valid IP address is changed.

  • From

    var res = values.map(([e]) => ([regExp.test(e.trim())]));
    range.offset(0, 1).setValues(res);
    

  • To

    var colors = values.map(([e]) => ([regExp.test(e.trim()) ? "green" : null]));
    range.setBackgrounds(colors);
    

Result:

When above script is run, the following result is obtained. I used your sample IP addresses at the column "A". The result is put to the column "B".

References:

• test()
• trim()