CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
ebrahim khoshnood
ebrahim khoshnood

Reputation: 13

Search a cell value within a column of values to find row number

I have a inventory list sheet and I have a shopping list sheet. I am scripting the onEdit() function that gets the name(code) in a certain cell in shopping list sheet and search through name column in inventory list sheet. When it finds the EXACT MATCH, it will return the row number and I will do my next move.

Problems:

  1. Some names are in Persian language and in this case it is finding the first row with a text in it ( it doesn't matter if its the same or not)
  2. With iterating its giving different results with changing the value (even if the whole column are numbers or text or ...)
  3. After a lot of searching, I found below code it is good but its not finding the exact match
    //sheetss name
      var inventory = "inventory"
      var shopping = "shopping"
      // sheets
      var ss = SpreadsheetApp.getActiveSpreadsheet()
      var activeSheet = ss.getActiveSheet()
      var inventorySheet = ss.getSheetByName(inventory)
      var lr = anbardariSheet.getLastRow()


      //var sh = getSheet(); //custom function that returns target Sheet;
      var rng = activeSheet.getRange(3,3, lr, 1); //change to desired Range boundaries;

      //create TextFinder and configure;
      var tf = rng.createTextFinder('14'); // 
          tf.matchCase(false); //{Boolean} -> match target text's case or not;
          tf.matchEntireCell(false); //{Boolean} -> check the whole Range or within;
          tf.ignoreDiacritics(true); //{Boolean} -> ignore diacretic signs during match;
          tf.matchFormulaText(false); //{Boolean} -> search in formulas (if any) or values;

      //invoke search;
      var res = tf.findNext();

      //do something with result;
      if(res!==null) {
        var vals = res.getvalues();
        Logger.log(vals);
      }

Upvotes: 0

Views: 850

Answers (2)

ebrahim khoshnood
ebrahim khoshnood

Reputation: 13

thank you guys both for answering in such short notice @Cooper @Andres Duarte

it solved my problem thanks again

Upvotes: 0

Andres Duarte
Andres Duarte

Reputation: 3350

Some problems i think you may have in your code to achieve what you want:

1) You're doing the search in the activeSheet, are you sure this is the inventory sheet?

2) You're using matchEntireCell [1] with a false parameter, if you want an exact match you have to use a true parameter, otherwise you'll have partial matches like you have now (i.e. 14 could match with 2214).

I fixed the previous problems and here is the code i tested in a onEdit function. I got the value to compare from the last row in the shopping sheet.

  //sheetss name
  var inventory = "inventory"
  var shopping = "shopping"
  // sheets
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var shoppingSheet = ss.getSheetByName(shopping)
  var inventorySheet = ss.getSheetByName(inventory)
  var lr = inventorySheet.getLastRow();

  //Get value to find
  var lrValue = shoppingSheet.getLastRow();
  var value = shoppingSheet.getRange(lrValue, 3).getValue(); 
  Logger.log(value)

  //var sh = getSheet(); //custom function that returns target Sheet;
  var rng = inventorySheet.getRange(3,3, lr, 1); //change to desired Range boundaries;

  //create TextFinder and configure;
  var tf = rng.createTextFinder(value); // 
      tf.matchCase(false); //{Boolean} -> match target text's case or not;
      tf.matchEntireCell(true); //{Boolean} -> check the whole Range or within;
      tf.ignoreDiacritics(true); //{Boolean} -> ignore diacretic signs during match;
      tf.matchFormulaText(false); //{Boolean} -> search in formulas (if any) or values;

  //invoke search;
  var res = tf.findNext();

  //do something with result;
  if(res!==null) {
    var vals = res.getValues();
    Logger.log(vals);
    Logger.log(res.getA1Notation());
  }

[1] https://developers.google.com/apps-script/reference/spreadsheet/text-finder#matchEntireCell(Boolean)

Upvotes: 2

Related Questions