从包含多列的电子表格中提取时，限制下拉列表中的结果数

Question

我有一个正在创建的谷歌网络应用程序，遇到了一个障碍。

在这个应用程序中，我使用了多个下拉列表。这些下拉列表使用以下代码从电子表格中提取数据。

var userList = ws.getRange(2,1,ws.getRange("A2").getDataRegion().getLastRow(),1).getValues();
var sourceList = ws.getRange(2,2,ws.getRange("B2").getDataRegion().getLastRow(),1).getValues();

现在，我遇到的问题是，我使用的每个下拉列表都与我从中提取值的电子表格中最长的列一样长。例如。如果列 A 有 4 个值，B 列有 12 个值，则从 A 列拉取的下拉列表将具有四个值以及另外 8 个空白值。

如何更改此代码以获取我要从中提取值的列的最后一行，而不是最长列的最后一行？我知道我可以放一个数字值来代替ws.getRange("A2").getDataRegion().getLastRow()但我会定期向下拉列表中添加新选项，并且不希望每次都更新代码。

这是需要发生这种情况的功能

function doGet(e){
var ss = SpreadsheetApp.openById(ssId)
var ws = ss.getSheetByName("List Source - External");
var range = ws.getRange("A2");

Logger.log(range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getA1Notation());
var lastRowInColumnA = range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
var userList = ws.getRange(2,1,lastRowInColumnA-1,1).getValues();
var sourceList = ws.getRange(2,2,lastRowInColumnA-1,1).getValues();
//var userList = ws.getRange("A2:A").getValues();
//var userList = ws.getRange(2,1,ws.getRange("A2:A").getDataRegion().getLastRow(),1).getValues();
//var sourceList = ws.getRange(2,2,ws.getRange("B2").getDataRegion().getLastRow(),1).getValues();
var brandList = ws.getRange(2,3,lastRowInColumnA-1,1).getValues();
var brand2List = ws.getRange(2,3,ws.getRange("C2").getDataRegion().getLastRow(),1).getValues();
var userListArray = userList.map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
var sourceListArray = sourceList.map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
var brandListArray = brandList.map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
var brand2ListArray = brand2List.map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
var tmp = HtmlService.createTemplateFromFile("builder");
tmp.userList = userListArray;
tmp.sourceList = sourceListArray;
tmp.brandList = brandListArray;
tmp.brand2List = brand2ListArray;

return tmp.evaluate();
}

Answer 1

这

可以通过getNextDataCell(direction(来实现

此方法为您提供指定方向上相邻数据范围的最后一个非空单元格。

样本：

...
var range = ws.getRange("A2");
Logger.log(range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getA1Notation());
var lastRowInColumnA = range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
var userList = ws.getRange(2,1,lastRowInColumnA-1,1).getValues();
var sourceList = ws.getRange(2,2,lastRowInColumnA-1,1).getValues();
...

更新

下面是如何在循环中单独查找每列的最后一行的示例：

function doGet(e){
var ss = SpreadsheetApp.openById(ssId)
var ws = ss.getSheetByName("List Source - External");  
var range = ws.getRange("A2:D2");
var valuesArray = [];
for (var i = 1; i <= range.getLastColumn(); i++){
var lastRowInColumn = range.getCell(1, i).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
var list = ws.getRange(2,i,lastRowInColumn-1,1).getValues();
valuesArray.push(list);
}
var userListArray = valuesArray[0].map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
var sourceListArray = valuesArray[1].map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
var brandListArray = valuesArray[2].map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
var brand2ListArray = valuesArray[3].map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
var tmp = HtmlService.createTemplateFromFile("builder");
tmp.userList = userListArray;
tmp.sourceList = sourceListArray;
tmp.brandList = brandListArray;
tmp.brand2List = brand2ListArray;
return tmp.evaluate();
}