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.

I won't write this as an answer due to your last sentence, but the spreadsheet function solution could be: =QUERY('All the data'!A:D;"select * where D contains 'hello'"). Note this will find "hello" in "Othello". — AdamL
Thanks for your quick reply Adam. Your comment is good enough for me to be an answer. If you'd like to post it as such I will mark this as answered. I will attempt to do the conversion to a Google Apps Script myself. — user1464409

Serge insas Serge insas · Accepted Answer · 2012-06-19T08:36:55

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

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

2 Answers