Compare values with a partial match then store matched data into new variable-CodePudding

So I am trying to compare a specific column value based on an active cell selection to a group of data in another sheet. This only has to be a partial match.

Example: comparing data value one in sheet one (1234) to a bunch of rows in column 1 of sheet 2 (1235TN, 1234TN etc) Basically I am looking for a partial match of the first 4 characters. Once found store the matched data in sheet 2 to a new variable.

I hope that is clear enough? Here is my sample code. all of the data gets pulled from both sheets, I just don't know how to partial match them and store the correct value.

function compare() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var row = sheet.getActiveCell().getRow();
  var col = 1;
  var value = sheet.getRange(row, col).getValue();
  var lookupSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test');
  var value2 = lookupSheet.getRange('A1:A150').getValues();
}

In the code above I am comparing value to value2, if value partially matches value2(4 characters) then store value2

Best,

D Table Image

CodePudding user response：

Find partial matches

function compare() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Sheet0');//You can change to active sheet
  var row = sh.getActiveCell().getRow();
  var col = 1;
  var n = sh.getRange(row, col).getValue();
  let re = new RegExp(`^${n.slice(0,4)}`,'');//first four characters
  let o = [];
  var lookupSheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');//you can change sheet name
  lookupSheet.getRange('A1:A150').getValues().flat().forEach((e,i) => {
    
    if(e.toString().match(re)) {
      o.push({value:e,row:i 1});
    }
  })
  Logger.log(JSON.stringify(o));
}

CodePudding user response：

function compare() {
  
  const sheet = SpreadsheetApp.getActiveSheet()
  const value = sheet.getRange(sheet.getActiveCell().getRow(), 1)
                     .getDisplayValue()
                     .slice(0, 4)

  const partialMatch = SpreadsheetApp.getActiveSpreadsheet()
                                     .getSheetByName(`Sheet1`)
                                     .getRange(`A1:A150`)
                                     .getDisplayValues()
                                     .flat()
                                     .find(cell => new RegExp(`${value}`, `i`).test(cell))

  if (partialMatch) Logger.log(partialMatch)

}