使用regex循环的Google Apps脚本使用更少的资源，因此不会超过最大执行时间

Question

是否有办法使这个脚本使用更少的资源?我得到超过最大执行时间，即使只选择数据集的一小部分。

我认为Regex消耗了相当多的资源，特别是因为它是在源列的每次迭代中完成的，对于目标列的每次迭代。但我不确定如何解决它，使它能够通过大量的数据运行。

function updatecategory() {
 /* let us say source array with name(columnA) & ID(columnB) is array 'source'
and target array with only IDs is array 'target', you get these with something like*/
var source = SpreadsheetApp.getActiveSpreadsheet().getSheets()[4].getDataRange().getValues();
// and
var target = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange("A3:A30").getValues();// if other columns, change index values in the arrays : 0=A, 1=B ...
//   then let's create a 3 rd array that will be the new target with ID + names, and call it 'newtarget' 
var newtarget=new Array()
// the iteration could be like this :
      for(i=0;i<target.length;++i){ // don't miss any ID
        var found=""
        var targetstr = target[i][0].toString()
       for(j=0;j<source.length;++j){ // iterate through source to find the name
         for(k=2;k<3;++k){
           var str = source[j][k].toString()
           var regex = new RegExp(".(?!(?!.*.[a-zA-Z ]{2,}" + targetstr + ")(?!" + targetstr + "[a-zA-Z *]+$)).*$", "gm") 
           //Find only words in a cell that exactly match the target word,
           //that is not part of a word or a phrase.
           var replace = ''
           var newstr = str.replace(regex,replace)
           if(newstr.indexOf(targetstr)>-1){
             var newsource = source[j][0] + "(n)"
             var newtargetrow=[target[i][0], newsource] // if match found, store it with name (idx0) and ID (idx 1)
             //Logger.log(target[i][0].toString().match(source[j][0].toString()) + " " + source[j][0].toString())
             //newtarget.push(newtargetrow);// store result in new array with 2 columns
             found="found"
             Logger.log(targetstr + " " + newsource)
           } else if (found != "found") {
             var newtargetrow=[target[i][0], ''] // if no match, show it in name column
             //Logger.log(found)
           }
         }
       }
        //Logger.log(newtarget)
         newtarget.push(newtargetrow);// store result in new array with 2 columns
       //loop source

     } // loop target
     /* now you have a newtarget array that can directly overwrite the old target 
    using setValues() */
    var sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];// assuming the target sheet is sheet nr2
    sh.getRange(3,1,newtarget.length,newtarget[0].length).setValues(newtarget);
//

}

Answer 1

根据您的反馈，您可以使用

var regex = new RegExp("(^|[,;]\s)\b" + targetstr + "\b(?!\s)", "gm")

它匹配一个targetstr，它是一个完整的词(因为b词边界锚定在术语周围)，并且前面有一个起始锚(^)或逗号或分号，后跟一个空格([,;]\s) 和不后跟空格的 ((?!\s))。