Search whole spreadsheet to find text within a cell and then print the whole row

Question

I have a Google Sheets that contains several rows and columns of data and would like to search for a certain phrase in each row of the spreadsheet and then print the row out on a new sheet if that row contains the phrase in one of the columns of data (lets say column D).

My sheet containing all the data is creatively called "All the data". Similarly, my sheet which will contain only the selected data is called "Selected data". Lets say the phrase I am looking for is "hello".

At the moment in the "Selected data" sheet, I have the following in cell A1:

=QUERY('All the data'!A:D,"select find("hello",All the data!D:D)>0")

This produces a parse error, I want it to print all the rows from the "All the data" sheet that contain "hello" in column D in the "All the data" sheet.

What am I doing wrong? Am I using the correct function? Is there another easier function to use? What is the correct formula?

Ultimately I would like to convert this into a Google Apps Script.

Solution 1

and here is a 'script solution' you could try. There are many possible approaches, this is one of them...

function testfindRow(){ ;// this function only to test the other one by giving it a parameter and using its result.
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var otherSheet=ss.getSheets()[1];// assuming there is a second sheet to copy to
        var datatocopy=findRow('Hello');
        Logger.log(datatocopy)
        if (datatocopy!=-1){
        otherSheet.getRange(otherSheet.getLastRow()+1,1,1,datatocopy[0].length).setValues(datatocopy);
        }
 }
//
function findRow(item) { ;// the actual search function
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet=ss.getSheets()[0];
        var values = sheet.getDataRange().getValues();
          for(cc =0; cc < values.length; ++cc) {
            if(values[cc].toString().match(item)==item){break};// here you can eventually use string modifiers like toLowerCase() to allow for wider search criteria
             }
        Logger.log(cc);// the array index in which the result was found, cc+1 is the Row index
        if (cc==values.length){return -1}
         var resultArray=sheet.getRange(cc+1,1,1,values[cc].length).getValues()
         return resultArray ;// the returned value is a 2 dimensions array to use with setValues()
 }

Following your comment, here is a version that returns all Rows containing the item, I had to change the error trapping as well but the whole things is quite simpler after all.

    function testfindRow(){ ;// this function only to test the other one by giving it a parameter and using its result.
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var otherSheet=ss.getSheets()[1];// assuming there is a second sheet to copy to
            var datatocopy=findRow('Hello');
            if (datatocopy.length>0){
            otherSheet.getRange(otherSheet.getLastRow()+1,1,datatocopy.length,datatocopy[0].length).setValues(datatocopy);
            }
     }
    //
    function findRow(item) { ;// the actual search function
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet=ss.getSheets()[0];
            var resultArray=new Array();
            var values = sheet.getDataRange().getValues();
              for(cc =0; cc < values.length; ++cc) {
                if(values[cc].toString().match(item)==item){// here you can eventually use string modifiers like toLowerCase() to allow for wider search criteria
// or like this to search only in column D // if(values[cc][3].toString().match(item)==item){
                resultArray.push(values[cc]);
                            };
                 }
            Logger.log(resultArray);// the array of Rows in which the item was found, 
            return resultArray ;// the returned value is a 2 dimensions array to use with setValues()
     }

Solution 2

The spreadsheet function solution would be:

=QUERY('All the data'!A:D;"select * where D contains 'hello'")

Note: this will find "hello" in "Othello".

Comments

[user1464409]

Thank-you Serge for your help as well. Unfortunately this doesn't seem to work for me. It only seems to return one of the columns (the first one in the spreadsheet) I think and if the item is not found there is an "out of bounds" error. I am assuming the "out of bounds" error is because the searched for term isn't found. When the search term is found it seems copy the whole contents of column A and not only the row where the match was found.

[Serge insas]

Sorry about that, I added an error trapping now and corrected some errors. It should work as expected.

[user1464409]

Thank-you Serge! Your edits have made it work, the only thing now is that it only picks up the first row that has this value ("hello") in it and not every row. How would I alter it so that it displays every row?

[Serge insas]

good question ! I'll see what I can find and keep you informed :-)

[Serge insas]

Got it ...I guess that's what you needed, isn't it ?