Hi here's the script I'm using to find all the matching rows from mSheet with the values of searchText and insert value1 in all the matching rows in column(B) and that is working really good. So what I'm trying to do is also insert the value from value2 on the same matching rows in column(M) I've tried different ways but just can't seem to get it right any help would be greatly appreciated

function  insert(){
  var mSheet =  SpreadsheetApp.openById('1rAQ0t--PPK0-wovqdc')
  var ss = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var searchText = ss.getRange('A1').getValue()
  var value1 = ss.getRange('B1').getValue()
  var value2 = ss.getRange('C1').getValue()
  var sheet = mSheet.getSheetByName('Data');
  
  if(searchText!=''){
  var rangeList = sheet
    .getRange('C1:C')
    .createTextFinder(searchText)
    .matchEntireCell(true)
    .findAll()
    .map((r) => r.offset(0, -1).getA1Notation());
    sheet.getRangeList(rangeList).setValue(value1);
    console.log(searchText,value1,rangeList);
    }
    }

CodePudding user response：

You could just modify the substring "B" with "M":

  var rangeList2 = rangeList.map((n) => n.replace('B','M'))

and then:

sheet.getRangeList(rangeList2).setValue(value2);

CodePudding user response：

I think the solution might be more obvious for you if you broke out some of your lines of code. What you have works fine, but as you can see, you've already essentially done the hard work, you just need to repeat part of it for column M (offset 10 columns away). See this to replace your if statement...

  if(searchText!=''){
  var rangeList = sheet
    .getRange('C1:C')
    .createTextFinder(searchText)
    .matchEntireCell(true)
    .findAll();
    //Creates two range lists.
    var list1 = rangeList.map(r => r.offset(0, -1).getA1Notation());
    var list2 = rangeList.map(r => r.offset(0, 10).getA1Notation());
    sheet.getRangeList(list1).setValue(value1);
    sheet.getRangeList(list2).setValue(value2);
    console.log(searchText,value1,rangeList);
    }