在Google Apps脚本中；使用正则表达式和条件格式-创建IPv4地址验证器/检查器 [英] in Google Apps Script; using Regular Expression & Conditional formatting - to create a IPv4 address validator / checker

问题描述

我尝试编写一个脚本以获取验证器的反馈，以检查输入的IP地址是否为

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

单元格'ip！I12'名为'REGEXP_IP_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演示: https://regexr.com/54fon -正常运行(在公式中)

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

可以很好地使用公式，但是我必须使用公式中的公式来定义状态是否有效.

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

公式格式的正则表达式可以很好地工作[请参见工作表'ip']，但可悲的是我无法在脚本中使它正常工作. 在检索正确和良好的信息，定义输入"ip"是否有效时存在一些问题. (请参见输入"，良好IP"列)

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

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

• 强制将单元格强制转换为字符串:TRIM( <cell> &"")
• 删除"已建立的正则表达式，然后在字符串的其余部分添加"wildChar_IP" [通配符]
• 检查其余的内容是否为" "(在我们的示例中等于"wildChar_IP")

• 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')

对于Google Apps脚本，工具> 脚本编辑器

For the Google Apps Script, TOOLS > SCRIPT EDITOR

要查看记录器的输出，请执行以下操作:查看> LOGBOOK

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.

这是我的代码...但是无法定义何时真正匹配. 由于autosense/autocomplete并非真正可靠，因此还有另一种方法可以找出所有的方法

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

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

代码:

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


}

结果

[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

如果您认为有效"功能会有所帮助，请在此处投票: https://issuetracker .google.com/issues/36762591

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

然后通过条件格式设置"使之可视化 ( https://developers.google.com/sheets/api /guides/conditional-format#apps-script )

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

欢迎访问所有帮助.

公式和脚本:

https://drive.google.com/open?id=1HTgdC6Ss8oOvyVmX8KB -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脚本；

推荐答案

我相信您的目标如下.

• 使用Google Apps脚本检查IP地址时，您要输出布尔类型.
• 您要检查电子表格中的一列.

为此，这个答案如何?

• 在此答案中，使用^(?:(?: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]?)$作为匹配IPV4的正则表达式. 参考
• 为了检查并返回布尔类型，我为此使用了test().

• 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.

示例脚本如下.在这种情况下，假定在"A"列中有IP地址.然后将输出值放在"B"列中.

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);
}

注意:

• 当以上示例脚本反映了以下修改时，有效IP地址的单元格颜色就会更改.

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

  运行上述脚本时，将获得以下结果.我在"A"列中使用了您的示例IP地址.结果放入"B"列.

  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".

  • test()
  • trim()
  • test()
  • trim()

  这篇关于在Google Apps脚本中；使用正则表达式和条件格式-创建IPv4地址验证器/检查器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！