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.

Unfortunately there's no way to getValues with applied filter. But if your data grow to millions of rows, the script will not be your primary concern, as a spreadsheet can only hold 400 thousand cells. — Henrique G. Abreu

Cartman Cartman · Accepted Answer · 2012-05-10T09:18:43

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) { ...

Retrieve rows from spreadsheet data using google app script

2 Answers