Javascript – How to return closest match for an array in Google Sheets Appscript

Problem:

I am struggling to return the highest count for a range of cells in A:A. The purpose of this code is to check for the value which is col A inside column N:N and return the highest character count in the sequences found. I am able to return the number of characters from the matched sequences just for A418 and any single cell, (can be A1). The issue is that I cannot get it to work for a range. In short I need to change A418 to a range such as this var dataA = sheet.getRange(“A1:A1000”).getValues();

function countCommonCharactersInOrder() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var searchString = sheet.getRange("A418").getValues().toString();
  var data = sheet.getRange("N:N").getValues();

  for (var i = 0; i < data.length; i++) {
    var text = data[i][0].toString();
    var commonCount = countCommonCharsInSequences(searchString, text);
    sheet.getRange(i + 1, 19).setValue(commonCount); // Column S (19th column)
  }
}

function countCommonCharsInSequences(str1, str2) {
  var commonCount = 0;
  
  for (var i = 0; i < str1.length; i++) {
    var currentCommonCount = 0;
    
    for (var j = 0; j < str2.length; j++) {
      if (str1.charAt(i) === str2.charAt(j)) {
        currentCommonCount++;
        i++;
      } else {
        if (currentCommonCount > 2) {
          commonCount += currentCommonCount;
        }
        currentCommonCount = 0;
      }
    }
    
    if (currentCommonCount > 2) {
      commonCount += currentCommonCount;
    }
  }
  
  return commonCount;
}

Solution:

From your reply, how about the following modification?

From:

function countCommonCharactersInOrder() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var searchString = sheet.getRange("A418").getValues().toString();
  var data = sheet.getRange("N:N").getValues();

  for (var i = 0; i < data.length; i++) {
    var text = data[i][0].toString();
    var commonCount = countCommonCharsInSequences(searchString, text);
    sheet.getRange(i + 1, 19).setValue(commonCount); // Column S (19th column)
  }
}

To:

function countCommonCharactersInOrder() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var dataA = sheet.getRange("A1:A" + lastRow).getValues();
  var data = sheet.getRange("N1:N" + lastRow).getValues();
  var res = dataA.map(([searchString]) => [Math.max(...data.map(([text]) => countCommonCharsInSequences(searchString, text)))]);
  sheet.getRange(1, 19, res.length).setValues(res); // Column S (19th column)
}

• By this modification, about each row of “A1:A”, the maximum values from countCommonCharsInSequences(searchString, text) are put into coulmn “S”.