29

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.

zessx
  • 68,042
  • 28
  • 135
  • 158
palaniraja
  • 10,432
  • 5
  • 43
  • 76
  • 2
    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 May 11 '12 at 14:46

2 Answers2

40

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) { ...
Nakilon
  • 34,866
  • 14
  • 107
  • 142
Cartman
  • 498
  • 8
  • 10
  • thanks @cartman. But I have thousands of records and looping it seems to be bad solution. – palaniraja May 10 '12 at 09:43
  • Thousands of rows is definitely very few, specially with 3 columns only. Getting all at once with getValues and looping is the solution here. Unless there's something we do not know about your data or usage. e.g. the filter is static, or the data is. – Henrique G. Abreu May 10 '12 at 20:59
  • You may need to use `getDisplayValues` instead of `getValues` if your spreadsheet contains Date fields, as `getValues` fails by default when your rows contain Date fields – Vance Palacio Mar 09 '23 at 20:19
10

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
kckoay
  • 101
  • 1
  • 2