Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
    }
}
share|improve this question
    
Could you share the spreadsheet with us? Or perhaps include a portion of it in your answer? It will make it easier to understand your problem. –  Vidar S. Ramdal Feb 19 at 9:11
    
Please accept rollback and post this as an answer !! –  Jacob Jan Tuinstra Feb 21 at 8:04
1  
Duncan, just post your solution as an answer to your own question. –  Jacob Jan Tuinstra Feb 21 at 8:32
    

2 Answers 2

up vote 0 down vote accepted

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

share|improve this answer
    
Thanks this one is good too. Here is what I ended up using in OP –  Paul Duncan Feb 21 at 5:43

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.

share|improve this answer
    
Thanks this one is good. Here is what I ended up using in OP –  Paul Duncan Feb 21 at 5:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.