使用Google应用程序脚本从电子表格数据中检索行 [英] Retrieve rows from spreadsheet data using google app script
问题描述
我使用Google应用脚本将表单数据写入电子表格。现在我想从符合条件的Excel中获取值(例如,按日期,用户名过滤)并使用应用程序脚本显示它们。
我的电子表格包含
+ ------- ------ + ----------- + ---------------------- +
|日期|用户名|评论|
+ ------------- + ----------- + ------------------- --- +
| 2012-05-02 | palani |我的第一个评论|
| 2012-05-02 | raja |我的第二个评论|
| | palani |我的第三个评论|
| | raja |我的第四个评论|
+ ------------- + ----------- + ------------------- --- +
现在我要过滤 2012-05- 02
on date, raja
关于用户名,并使用app-script中的标签显示它们(作为Web服务运行)。
我所有的搜索都使用 SpreadsheetApp.getActiveSheet()。getDataRange();
,我认为这并不是优化的方式,可以在表格中显示1000多行中的一条记录。
编辑
现在,我使用 在tmp单元格上应用 最简单的方法和我所知道的是: 然后循环 .getValues()$ c仅限$ c>。此处显示的数据仅供参考。我现在的真实表格有15列和5000+行。最终它将增长到数百万,因为这是一个时间表应用程序。我想知道有没有其他方式来过滤行
getValues()
?
= Filter
公式也是一个问题,因为脚本可以同时用数字的人。
获取数组中的值使用
var data = SpreadsheetApp.getActiveSheet()。getDataRange()。getValues();
<$ p $在这个循环中检查是否存在($ in $ data){
日期( data [i] [0]
)等于您正在查找的日期,如果名称( data [i] [1]
)等于你正在查找的那个。并将其推送到数组中。
label.push([data [i] [0],data [i] [1],data [i] [2]]);
然后让你的数据在这个数组中,你可以用一个for循环
for(i in labels){...
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.
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) { ...
这篇关于使用Google应用程序脚本从电子表格数据中检索行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!