仅获取Google App脚本getRange的未过滤值 [英] Only get non-filtered values for Google App Script getRange
问题描述
有没有办法仅在Google App脚本中仅获取未过滤的值?(即获取显示的值而不是隐藏的值?
Is there a way to only get non-filtered values only in Google App Script? (i.e. get values that are showing and not hidden ones?
例如,假设我在Google表格中具有以下单元格和值.
For example, let’s say I have the following cells + values in Google Sheets.
A1=abc B1=x
A2=def B2=y
A3=ghi B3=y
A4=kjl B4=x
然后我过滤了B列,因此它仅显示[y].
And I filtered B column so it only displays [y].
A2=def B2=y
A2=ghi B3=y
当我使用以下脚本时,隐藏值和非隐藏值都将打印到msgBox.
When I use the following script, both hidden and non-hidden values gets printed to the msgBox.
function msgBoxTest(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var lastColumn = ss.getLastColumn();
var range_input = ss.getRange("A1:A").getValues();
var result = [i for each (i in range_input)if (isNaN(i))];
// remove commas originating from empty cells
Browser.msgBox(result);
//I want only def & ghi to display here.
//Instead, I get all values -> abc,def,ghi,kjl
}
我已经在Google上搜索并在线查找,但是找不到有关在Google App脚本中过滤掉值的代码.有什么建议么?
I've googled and looked online but couldn't find code about filtering out values in Google App script. Any suggestions?
推荐答案
如果要使用脚本,可以这样做:
If you want to to use script, this will do it:
function msgBoxTest(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var lastColumn = ss.getLastColumn();
var range = ss.getRange("A1:B").getValues();
var filter=[]//new array
for(var i=0;i<range.length;i++){
if (range[i][1]=="y"){
filter.push(range[i][0])
}}
Browser.msgBox(filter);
//I want only def & ghi to display here.
}
或者您可以使用简单的查询公式来做到这一点:
Or you could do it with a simple query formula:
=query(A1:B,"Select A where B='y'")
这篇关于仅获取Google App脚本getRange的未过滤值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!