我有一个工作簿，用于跟踪生产输出。主要标签页有一些（例如主列表，PDLine1，PDLine2，每日摘要）。
主列表用于列出所有生产订单，而PDLine工作表用于跟踪各自生产线中的生产订单。每日摘要将列出每个PDLine当天的总输出。
实际上，我从PDLine工作表中生成了一个新工作表，用于跟踪每个生产订单以衡量其每日产量（完成一个订单大约需要3-5天）。
因此，是否有可能使用QUERY函数和应用脚本，每次生成新的生产订单表时，该表都会被添加到“每日摘要”表的QUERY函数中？
我不知道我上面提到的内容对您是否有意义，但是如果您对如何执行此操作有想法，并且愿意提供帮助，请随时请我详细说明。
链接到示例电子表格： Example Spreadsheet
我认为您无需为工作表PDLine导入范围，您可以为每个PDLine工作表使用此范围，只需更改工作表的过滤器即可：
={QUERY(JobList!A:P,"Select A,B,C,D,E,F,G where H='PDLine1'"),ArrayFormula( QUERY(JobList!H:O,"Select O where H='PDLine1'") ),ArrayFormula(QUERY(JobList!D:H,"Select D where H='PDLine1'")- QUERY(JobList!H:O,"Select O where H='PDLine1'") )}
在Daysummary中，我在我的手机中进行了测试：
在A4单元格中输入：
=query({query(JobList!A10:O,O where H<>'' ",-1),ARRAYFORMULA(if(query(JobList!H10:H,"Select H where H<>'' ",-1)="PDLine1",query(JobList!H10:O,"Select O where H<>'' ",0)),-1)="PDLine2",0))},"Select Col1,sum(Col3),sum(Col4),Sum(Col2) where Not Col1='' group By Col1 LABEL Sum(Col3) '',Sum(Col4) '',Sum(Col2) ''",0)
在E4中，我输入：
=query({query(JobList!A10:I,I where H<>'' "),ARRAYFORMULA(if(query(JobList!H10:I,"Select H where H<>'' ")="PDLine1",query(JobList!H10:I,"Select I where H<>'' "),"Select H where H<>'' ")="PDLine2","Select sum(Col3),Sum(Col2) where not Col1='' group By Col1 label sum(Col3) '',sum(Col4) '',sum(Col2) ''",0)
也许可以在脚本中使用
function createQuery() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var myJ = ss.getSheets();
var ShList=[];
for (a=1; a< myJ.length; a++)
{
if (myJ[a-1].getSheetName().substring(0,3)=='myJ')
ShList.push (myJ[a-1].getSheetName());
}
var myQry='={';
for (a=0; a<ShList.length; a++)
{
if (a>0) myQry = myQry + ",";
myQry= myQry + "QUERY(" + ShList[a] + "!B8:C10,\"Select B,C\")";
}
myQry= myQry + '}';
Logger.log(myQry); //put this to cell
ss.getSheetByName('myRslt').getRange('A4').setFormula(myQry);
}
您的情况可能是这样的：
=query({query(JobList!A10:O,0)
也许可以用在你的
function createQuery() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var myJ = ss.getSheets();
//Below you will use the ss as your spreadsheet and myJ as your Sheets
//But may be different in yours,//ShList is stringlist to store your sheetname that matchs to your criteria
var ShList=[];
for (a=1; a< myJ.length; a++)
{
//If your sheet prefix is 'JO' use below,if others you can change it
//substring(0,2) is depend on your sheet name that will be included
//in myQuery later
if (myJ[a-1].getSheetName().substring(0,2)=='JO')
ShList.push (myJ[a-1].getSheetName());
}
var myQry='={';
for (a=0; a<ShList.length; a++)
{
if (a>0) myQry = myQry + ",";
//You must change A8:G10 to your actual condition,may be you can use getLastRow() first,you can use select * if all column define will be included
myQry= myQry + "QUERY(" + ShList[a] + "!A8:G10,\"Select A,G\")";
}
myQry= myQry + '}';
//Please check your logger.log content,myQry must be success first
Logger.log(myQry); //put this to cell
//Change myRslt to your destination sheet that you will store your query
//Change A4 to your destination cell
ss.getSheetByName('myRslt').getRange('A4').setFormula(myQry);
}