Regexp Match Google Script inside a loop?

Question

I've been on this problem for a couple of hours, I'm new to coding, so excuse me if it's a very simple question.

So I have a list of text and I want to find if there is one of the regular expression from the other sheet in every cell. If yes, paste the regular expression next to the text.

Example: For the first row: 7063 BIO PLANET LIEGE. --> i'd like it to write "BIO PLANET" in the cell to the right. (Because BIO PLANET is one of the regular expression to test from the second sheet).

I wrote something like this, but couldn't really figure out what needs to be fixed:

function ExpenseMatching() {
  
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet1 = spreadsheet.getSheetByName("Import2");
  var sheet2 = spreadsheet.getSheetByName("Regular Expression");



 for ( i =1; i<24 ; i++)
 
 { 
//Browser.msgBox(i)

   var test1 = sheet2.getRange("A"+ i);
   var test2 = sheet1.getRange("A2");



      var test = new RegExp(test1).test(test2); 

 
 if (regexp==true)
 
                        { 
                          test1.copyTo(sheet1.getRange("I2"));
                   

                        Browser.msgBox(test)
                          
                         }     
                         
                         
                         else 
                         
                         {
                   
                         
       
                         }

}
     


}

Thanks is advance for your help guys !

The code has several things that should be fixed. i.e. regexp is not declared so regexp==true will always be false but the code block for the else part of the if is empty. Please read minimal reproducible example and developers.google.com/apps-script/guides/support/… — Wicket
– Wicket, Commented Mar 30, 2020 at 22:37

halfer · Accepted Answer · 2021-12-21 23:31:09Z

You want to retrieve the values of the column "A" on the sheet Import2 and the values of the column "A" on the sheet Regular Expression.
You want to check whether the values of Import2 includes the values of Regular Expression. When the values of Import2 includes the values of Regular Expression, you want to put the value of Regular Expression to the column "B" on Import2.
You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer?

Modification points:

In your script,
- if (regexp==true) doesn't work and an error occurs. Because regexp is not declared.
  - This has already been mentioned by Rubén's comment.
- From your question, I thought that you want to put the result value to the column "B" of Import2. But it seems that your script puts the value to the column "I" from test1.copyTo(sheet1.getRange("I2")).
- Your script checks only "A2" of Import2.
- Each row is checked and copy the value in the for loop. In this case, the process cost will be high.

When above points are reflected to your script, how about the following modified script?

Modified script:

function ExpenseMatching() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet1 = spreadsheet.getSheetByName("Import2");
  var sheet2 = spreadsheet.getSheetByName("Regular Expression");
  
  const values1 = sheet1.getRange(`A2:A${sheet1.getLastRow()}`).getValues();
  const values2 = sheet2.getRange(`A2:A${sheet2.getLastRow()}`).getValues();
  const res = values1.map(([r1]) => {
    for (let i = 0; i < values2.length; i++) {
      if (new RegExp(values2[i][0]).test(r1)) {
        return [values2[i][0]];
      }
    }
    return [""];
  });
  sheet1.getRange(2, 2, res.length, 1).setValues(res);
}

I think that in your situation, you can also use if (r1.includes(values2[i][0])) { instead of if (new RegExp(values2[i][0]).test(r1)) {. This might be able to reduce more cost.

Note:

In this modification, the result values are put to the column "B" of Import2.
Please run the script with enabling V8.

Collectives™ on Stack Overflow

Regexp Match Google Script inside a loop?

1 Answer 1

Modification points:

Modified script:

Note:

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

Note:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related