GAS: Replacing every regex-match in all sheets with cell address

问题内容:

I want to search for all instances of ‘xyz’ in a Google spreadsheet and replace every ‘y’ with the cell reference and sheet name.

Imagine I have this table in a sheet called ‘Monday’:

A      B
1 xyz
2      xyz

I then want the text in A1 and B2 to be replaced so the result is this:

A            B
1 xA1Mondayz
2            xB2Mondayz

And so on for the remaining sheets.

So far I’ve not found a similar question…

My code looks something like this:

function replaceY() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheets = spreadsheet.getSheets();
  //Do for each sheet
  for(m = 0; m < sheets.length; m++){
    var data = sheets[m].getDataRange().getValues();
     for(var i = 0; i < data.length; i++){
      for(var n = 0; n < data[0].length; n++){
        if(typeof(data[i][n]) == 'string'){
          var regex = /.*(y).*/ig;
          var result = regex.exec(data[i][n]);
          if(result[1] !== null){ 
            var currentCellAddress = sheets[m].getActiveRange();
            // Loop through regex matches and replace every group
            // with currentCellAddress and sheet name
          }
        }
      }
    }
  }
}

I realize I might be out of my depth here, but if you know of a solution, I’ll be glad to see it!

update
The error I’m receiving is: Cannot read property ‘1’ of null.
And that’s without any looping through matches.

问题评论:

1  
What is the problem with the code? Are you getting an error? 🙂
    
@sniperd Yes. TypeError: Cannot read property ‘1’ of null. And that’s without any looping through matches.
– Nickelbacker
2 hours ago
    
why you don’t use indexOf(‘String’) method?

原文地址:

https://stackoverflow.com/questions/47754159/gas-replacing-every-regex-match-in-all-sheets-with-cell-address

添加评论

友情链接:蝴蝶教程