Google Sheets – Function to Search Sheet for String and Return Value of Next Column

google sheetsgoogle-apps-script

Here is what I have so far. it will:

  1. Search for keyword in my other sheet (JSON)
  2. When it finds the keyword (modname) it returns the row #

The keyword is on the A column. What I want is for a function to return the value on the B column of the same row. How do I do this?

function onSearch2(modname)
{
    var searchString = modname;
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("JSON164"); 
    var column =1; //column Index   
    var columnValues = sheet.getRange(2, column, sheet.getLastRow()).getValues(); //1st is header row
    var searchResult = columnValues.findIndex(searchString); //Row Index - 2

    if(searchResult != -1)
    {
        //searchResult + 2 is row index.
        SpreadsheetApp.getActiveSpreadsheet().setActiveRange(sheet.getRange(searchResult + 2, 1))
        return searchResult+2
    }
}

Best Answer

I was working on a solution as well.

Code

function onSearch1(searchString) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("JSON164");
  var values = sh.getDataRange().getValues();

  for(var i=0, iLen=values.length; i<iLen; i++) {
    if(values[i][0] == searchString) {
      return values[i][1];
    }
  }     
}

Explained

The value var will retrieve all values at once. The single result var will capture the first index found in the range. After that, the result of the adjacent column will be shown, analogue to values[i][0].

Usage

=onSearch1("test")

Note

The findIndex function is none-exsisting in Google Apps Script. If you want to seperate them, then use this code:

function onSearch2(searchString) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("JSON164");
  var values = sh.getDataRange().getValues();
  var i = findIndex(values, searchString);

  return values[i][1]; 
}

function findIndex(values, searchString) {
  for(var i=0, iLen=values.length; i<iLen; i++) {
    if(values[i][0] == searchString) {
      return i;
    }
  }
}

Example

I've created an example file for you: findIndex

Related Topic