Google Spreadsheet; Function to Search a Sheet for String and Return Value of Next Column

Question

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
    }
}

Answer 1

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

Answer 2

If I have understood your question correctly, you want to search a column for a specific string, and if found, return a value from another column in the same row.

Here's my suggestion:

var SPREADSHEET_NAME = "Sheet1";
var SEARCH_COL_IDX = 1;
var RETURN_COL_IDX = 0;

function searchStr(str) {
  var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SPREADSHEET_NAME).getDataRange().getValues();
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    if (row[SEARCH_COL_IDX] == str) {
      return row[RETURN_COL_IDX];
    }
  }
}

To use this function from your sheet, simply use the formula =searchStr(search term). I have set up an example spreadsheet here.

The SEARCH_COL_IDX and RETURN_COL_IDX constants can be made parameters to the search function, if you like, to make it more generic.