我有一个从多个工作表中提取数据的查询语句,它运行良好:
=QUERY({'A Physical Science'!A2:L; 'B Physical Science'!A2:L; 'E Physical Science'!A2:L; 'F Physical Science'!A2:L; 'G Physical Science'!A2:L},"select Col11 where Col6 is not null",0)
不过,当我尝试为数据区域创建动态源时,它不再有效。我成功地创建了一个文本字符串公式,它与的原始数据范围完全匹配
{'A Physical Science'!A2:L; 'B Physical Science'!A2:L; 'E Physical Science'!A2:L; 'F Physical Science'!A2:L; 'G Physical Science'!A2:L}
但无论我如何调用相邻QUERY语句中的数据范围,它都只返回文本字符串。INDEX, ARRAYFORMULA, INDIRECT
,大括号,没有什么能让数据范围文本字符串栩栩如生。
@鲁本,我在你的脚本中添加了我的旧函数,使其动态,并在打开谷歌表单时触发它运行。只是想看看是否有更优雅的方式来实现这一点?
function RangeArray() {
const skiplast = 5; //last 5 sheets need not be arrayed
var data = ""; //initialize string that will be the data range
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length - skiplast ; i++) {
data = data + "'"+ [ sheets[i].getName() ] + "'!A2:Z" ; //append the sheet names to the data string, plus necessary bits for parsing
if (i<sheets.length - skiplast -1) {data = data + ";"} //except for the last sheet, also append a semicolon
else {}
}
const here = SpreadsheetApp.getActiveSpreadsheet().getRange('DO NOT EDIT!A1');
const formula = '{'+ data +'}'; // make an array out of the data string
here.setFormula(formula);
}
没有办法像那样转换单元格值
{'A Physical Science'!A2:L; 'B Physical Science'!A2:L; 'E Physical Science'!A2:L; 'F Physical Science'!A2:L; 'G Physical Science'!A2:L}
使用Google Sheets内置函数将其转换为Google Sheets数组,但也可以使用Google Apps Script,将该值用作setFormula
方法之一的参数,即:
function addArray(){
const a1 = SpreadsheetApp.getActiveSpreadsheet().getRange('Sheet1!A1');
const formula = `{'A Physical Science'!A2:L; 'B Physical Science'!A2:L; 'E Physical Science'!A2:L; 'F Physical Science'!A2:L; 'G Physical Science'!A2:L}`;
a1.setFormula(formula);
}