CODEWITHSUNDEEP
javascriptregexgoogle-sheets
Angelzzz
Angelzzz

Reputation: 1446

How to use regExp to find value in cell and put in next row in spreadsheet?

I have table with one row which contains text. I need to extract value from this text using regExp.

My code is: 

function myfunc(){

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = spreadsheet.getRange('A1:B1000');
  var regExp = /(\?<=Баланс)(.*)(\?=р8)/;

  for (var i = 1; i < 1000; i++) {
    var cell = range.getCell(i,1)
    var cell2 = range.getCell(i,2)    
    var val = regExp.exec(cell.getValue());
    cell2.setValue(val);
  }
}

But it doesn't do anything. What can be the problem?

Upvotes: 1

Views: 75

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627419

You need to fix the patter:

/Баланс(.*?)р8/

Then, you may access the Group 1 value after matching with regExp.exec(cell.getValue()), it is accessible via val[1].

function myfunc(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = spreadsheet.getRange('A1:B1000');
  var regExp = /Баланс(.*?)р8/;

  for (var i = 1; i < 1000; i++) {
    var cell = range.getCell(i,1)
    var cell2 = range.getCell(i,2)    
    var val = regExp.exec(cell.getValue());
    if (val) {
      cell2.setValue(val[1]);
    }
  }
}

Note that you may get what you need with a mere =REGEXEXTRACT:

=REGEXEXTRACT(A13,"Баланс(.*?)р8")

enter image description here

Upvotes: 1

Related Questions