Retrieve rows from spreadsheet data using google app script

Question

I am using Google app script to write form data to spreadsheet. Now I would like to fetch the values from Excel which matches conditions (eg., filtered by date, username) and display them with app script.

My spreadsheet has

+-------------+-----------+----------------------+ | Date        | Username  | Comment              | +-------------+-----------+----------------------+ | 2012-05-02  | palani    | My first comment     | | 2012-05-02  | raja      | My second comment    | | 2012-05-03  | palani    | My third comment     | | 2012-05-03  | raja      | My fourth comment    | +-------------+-----------+----------------------+ 

Now I want to filter data for 2012-05-02 on date and raja on username and display them using labels in app-script (which is running as a web service).

All my searches return solution using SpreadsheetApp.getActiveSheet().getDataRange(); which i think is not optimized way to display one record out of 1000+ rows in sheet.

EDIT

Right now, I am using .getValues() only. And the data shown here is for sample; my real sheet has 15 column and 5000+ rows for now. And eventually it will grow to millions as this is a timesheet application. I am wondering is there any other way to getValues() of filtered rows?

Applying =Filter formula on a tmp cell will also be an issue, as script could be used simultaneously by number of people.

Answer 1

The easiest way, and the one I know, is to :
get the values in an Array using

var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues(); 

then to make a loop

for (i in data) { 

in this loop to check if the date (data[i][0]) is equal to the one you're looking for, and if the name (data[i][1]) is equal to the one you're looking for too. And push it to an array

var labels=new Array; label.push( [data[i][0], data[i][1], data[i][2]] ); 

then having all your data in this array, you can populate a panen Ui with label using a for loop

for (i in labels) { ... 

Answer 2

Well, you can consider use a cell and use the "Query" formula on it. Lets assume Date is at Column A, Username = Column B, Comment is Column C, while the blank cell is D1, your script will look like this:

SpreadsheetApp.getActiveSheet().getRange("D1").setFormula('Query(A:C,"SELECT A,B,C WHERE B="' + "SomeOne'" + ',1)'; //filter the data and populate to D1 onwards     SpreadsheetApp.getActiveSheet().getRange("D:F").getValues();//this is the filtered data