使用 Google 电子表格脚本进行正则表达式查询

发布于 2024-09-26 02:44:53 字数 1706 浏览 6 评论 0原文

我正在尝试完成 Google Apps 脚本来重新格式化我希望将其转换为超链接的字段。

这是电子表格中文本的常见格式:

tistaff:其他部分:人员:随机名称

这就是我希望它显示的方式:

<li><a href="http://www.thisisstaffordshire.co.uk/topics/person/randomname">randomname</a></li>

除了最后一点之外,我已经完成了大部分工作,我就是无法解决。任何人都可以帮忙。

这是我的脚本:

function HTMLtransform() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for(var c=1; c<sheet.getLastRow(); c++){
    var rng = sheet.getRange("B"+c);
    var rplc = sheet.getRange("F"+c);
    var value = String(rng.getValue());
    
    var replVal = new String('test');
    
    var target = 'test'; // this variable is designed to capture the unique bit of the variable
    
    target = value.replace(/tistaff: other sections:[a-z]*: ([a-z]*)$/, '$1');// this variable is designed to capture the unique bit of the variable
   
    replVal = value;
    replVal = replVal.replace(/ company:/, 'company/');
    replVal = replVal.replace(/ person:/, 'person/');
    replVal = replVal.replace(/ place:/, 'place/');
    replVal = replVal.replace(/tistaff: other sections:/, '<li><a href="http://www.thisisstaffordshire.co.uk/topics/');
    
    replVal = replVal.replace(/ ([a-z]*)$/, '');
    replVal = replVal + target + '">' + target + '</a></li>';
                              
                              
    
    rplc.setValue(replVal);
  }
}

它在一定程度上有效,但这是输出:

**<li><a href="http://www.thisisstaffordshire.co.uk/topics/person/tistaff: other sections: person: randomname">tistaff: other sections: person: randomname</a></li>**

我做错了什么?

I'm trying to finish off a Google Apps Script to reformat a field that I would ideally like to turn into a hyperlink.

This is the common format of the text in the spreadsheet:

tistaff: other sections: person: randomname

This is how I would like it to appear:

<li><a href="http://www.thisisstaffordshire.co.uk/topics/person/randomname">randomname</a></li>

I've done most of the work except the end bit, which I just can't work out. Can anyone help.

Here's my script:

function HTMLtransform() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for(var c=1; c<sheet.getLastRow(); c++){
    var rng = sheet.getRange("B"+c);
    var rplc = sheet.getRange("F"+c);
    var value = String(rng.getValue());
    
    var replVal = new String('test');
    
    var target = 'test'; // this variable is designed to capture the unique bit of the variable
    
    target = value.replace(/tistaff: other sections:[a-z]*: ([a-z]*)$/, '$1');// this variable is designed to capture the unique bit of the variable
   
    replVal = value;
    replVal = replVal.replace(/ company:/, 'company/');
    replVal = replVal.replace(/ person:/, 'person/');
    replVal = replVal.replace(/ place:/, 'place/');
    replVal = replVal.replace(/tistaff: other sections:/, '<li><a href="http://www.thisisstaffordshire.co.uk/topics/');
    
    replVal = replVal.replace(/ ([a-z]*)$/, '');
    replVal = replVal + target + '">' + target + '</a></li>';
                              
                              
    
    rplc.setValue(replVal);
  }
}

It works to a point, but this is the output:

**<li><a href="http://www.thisisstaffordshire.co.uk/topics/person/tistaff: other sections: person: randomname">tistaff: other sections: person: randomname</a></li>**

What am I doing wrong?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

温柔嚣张 2024-10-03 02:44:53

这是完成的代码:

我没有意识到您可以多次插入子模式。

function HTMLtransform() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for(var c=1; c<sheet.getLastRow(); c++){
    var rng = sheet.getRange("B"+c);
    var rplc = sheet.getRange("F"+c);
   var value = String(rng.getValue());

    regexFormat = /tistaff: other sections: (place|company|person): ([a-z]*)$/

   // var replVal = new String('test');


    replVal = value.replace(regexFormat, '<li><a href="http://www.thisisstaffordshire.co.uk/topics/$1/$2'+'">$2</a></li>');




      rplc.setValue(replVal);
  }
}

Here's the finished code:

I didn't realise that you could insert a subpattern more than once.

function HTMLtransform() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for(var c=1; c<sheet.getLastRow(); c++){
    var rng = sheet.getRange("B"+c);
    var rplc = sheet.getRange("F"+c);
   var value = String(rng.getValue());

    regexFormat = /tistaff: other sections: (place|company|person): ([a-z]*)$/

   // var replVal = new String('test');


    replVal = value.replace(regexFormat, '<li><a href="http://www.thisisstaffordshire.co.uk/topics/$1/$2'+'">$2</a></li>');




      rplc.setValue(replVal);
  }
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文