Search spreadsheet by column, return rows

Question

I'm trying to find the best script in terms of runtime to complete a task. I've got a decently large spreadsheet where I need to check values in certain known columns, and depending on a match case it returns that row. Ideally I'd like a new spreadsheet containing the returned rows.

I've got the spreadsheet opened by ID and I've got the sheet & range, but not sure the most efficient way to search through the specific columns and grabbing not just that value but the entire row.

Code speaks louder than words. Can you post the code that you have? If you have written something to start with, we can help with the optimization — Srik, Aug 28 '13 at 08:16

score 53 · Accepted Answer · edited Sep 08 '15 at 19:33

53

You can use the code below to search in a specific column. Code is self explanatory.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Search", functionName: "onSearch"} ];
  ss.addMenu("Commands", menuEntries);    
}

function onSearch()
{
    var searchString = "Test11";
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName"); 
    var column =4; //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))
    }
}

Array.prototype.findIndex = function(search){
  if(search == "") return false;
  for (var i=0; i<this.length; i++)
    if (this[i] == search) return i;

  return -1;
}

edited Sep 08 '15 at 19:33

Jeremy H.

523
1
5
18

answered Aug 28 '13 at 08:19

A G

21,087
11
87
112

3

Can you shed some light on where to find the "findIndex" function? Cannot see this function in the official documentation – matcheek Oct 30 '13 at 16:28
@Aseem gautam. I want to check for array of items in a column. How i can use your function to find out if those array items don't exist on a column then insert them if they don't exist already ? – user1788736 Oct 19 '15 at 21:17
@matcheek - It looks like the function is defined within this script – Veggiet Jun 08 '22 at 16:51
Array.prototype.findIndex - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/findIndex – Jeffrey Van Alstine Dec 20 '22 at 18:16

score 19 · Answer 2 · answered Apr 20 '19 at 00:53

19

You may want to use the Google Apps Script textFinder class.

This example function will search a string and show the row of the first occurrence in an alert dialog:

function searchString(){
  var sheet = SpreadsheetApp.getActiveSheet()
  var search_string = "dog"
  var textFinder = sheet.createTextFinder(search_string)
  var search_row = textFinder.findNext().getRow()
  var ui = SpreadsheetApp.getUi();
  ui.alert("search row: " + search_row)
}

answered Apr 20 '19 at 00:53

Jéter Silveira

329
2
6

1

Note that this solution does not search in specific columns as asked in the question. It searches entire spreadsheet. – Emin Nov 10 '20 at 09:37
2

@Emin that function searches within a range. That range can be a column or whatever you want. Create the range for that single column and replace `sheet.createTextFinder()` with `range.createTextFinder()` – T Nguyen Nov 02 '21 at 09:43

score 0 · Answer 3 · answered Feb 04 '18 at 08:46

My lookup script on GitHub

Usages:

Lookup_(SheetinfoArray,"Sheet1!A:B",0,[1],"Sheet1!I1","n","y","n");

Lookup_(Sheetinfo,"Sheet1!A:B",0,[1],"return","n","n","y");

Lookup_(SheetinfoArray,"Sheet1!A:B",0,[0,1],"return","n","n","y");

Lookup_(Sheetinfo,"Sheet1!A:B",1,[1,3,0,2],"return","y","n","n");

Lookup_("female","Sheet1!A:G",4,[2],"Database!A1","y","y","y");

Lookup_(Sheetinfo,LocationsArr,4,[0],"return","y","n","y");

Lookup_(/RegEx+/i,LocationsArr,4,[0],"return","y","n","y");

///////////////////////////////////////////////////////

Parameters Explaination:

-"Search_Key" - Can be be a string, array, or regex to lookup multiple things at once

-"RefSheetRange" - The Reference source of information. Can be local sheet reference and range or an array of data from a variable.

-"SearchKey_RefMatch_IndexOffSet" - What column of information you are referencing of 'Search_Key' to 'RefSheetRange' data.

-"IndexOffSetForReturn" - Once a 'Search_Key' match has been found what columns of data will be returned from 'RefSheetRange'.

-"SetSheetRange" - Where are you going to put the chosen information from 'RefSheetRange' that matched 'Search_Key' OR you can use 'return' and when the function finishes it will return so you can output the function to a variable.

-"ReturnMultiResults" - If 'Y' Say you 'Search_Key' is 'NW' and you want to find every store in a chain that falls under the northwest in your dataset. So declaring 'Y' wont stop after it finds the first match it will keep searching throught the rest of the data.

-"Add_Note" - If 'Y' you are setting the results to a spreadsheet and not returning it to memory then it will set the first cell in the 'SetSheetRange' with a note of what and when.

-"Has_NAs" - If 'Y' it will put in '#N/A' the column where it did not find data for 'Search_Key' other wise it will leave the column blank.

score 0 · Answer 4 · answered Jan 11 '21 at 23:52

The combination of INDEX and MATCH should work:

=INDEX($B$2:$D$1000,MATCH(S2,$A$2:$A$1000,0))

S2 is a search key.

$A$2:$A$1000 is a range, where we will lookup.

$B$2:$D$1000 is a range, that we will return.

Keep in mind that numeric parts of $B$2:$D$1000 and $A$2:$A$1000 should match.

score 0 · Answer 5 · answered Aug 10 '22 at 19:32

I copied a scrip to search a Google Sheet but it will only return results if the search is the exact content of the cell. The cells have names. For example, row 1 have "John Doe". It will only bring results if I search for "John Doe". If I search just John or Doe or part of the name it will not return any resul.

    <div class="container">
        <br>
          <!-- ## SEARCH FORM ------------------------------------------------ -->
          <form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">
                <div class="form-group mx-sm-3 mb-2">
                  <input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Digite parte do nome">
                </div>
                <button type="submit" class="btn btn-primary mb-2">Pesquisar</button>
              </form>
              <!-- ## SEARCH FORM ~ END ------------------------------------------- -->
        </div>
            <!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
            <div id="search-results" class="table-responsive">
              <!-- The Data Table is inserted here by JavaScript -->
            </div>
            <!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
          <br>
          </div>
        </div>
    </div>

score -1 · Answer 6 · answered Apr 09 '15 at 18:38

function onSearch()
{
    var searchString = "SD0023";
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1"); 
    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, 3)).setValue("found here");
    }
}

Array.prototype.findIndex = function(search){
  if(search == "") return false;
  for (var i=0; i<this.length; i++)
    if (this[i].toString().indexOf(search) > -1 ) return i;

  return -1;
}

Made some small changes to the answer to search inline text.

Search spreadsheet by column, return rows

6 Answers6

Linked