CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
HM Production
HM Production

Reputation: 37

Possible to use QUERY to combine multiple sheets in the same workbook?

I have a workbook that is used to track production outputs. There are a few main tab sheets (e.g. Main List, PDLine1, PDLine2, Daily Summary).

Main List is used to list out all the production orders, while PDLine sheets is used to track production orders in their respective production line. Daily Summary will list the total output of the day for each PDLine.

From the PDLine sheet, I actually generate a new sheet to track each production orders to measure their Daily Output (one order takes roughly about 3 - 5 days to be done).

Therefore, is it possible, that using QUERY function and app script, everytime I generate a new production order sheet, that sheet will be added into the QUERY function in the Daily Summary sheet?

I don't know if what I mentioned above make sense to you or not, but if you have an idea of how to do this, and willing to help, feel free to ask me to elaborate more.

Link to example spreadsheet: Example Spreadsheet

Upvotes: 2

Views: 695

Answers (1)

user11982798
user11982798

Reputation: 1908

I think you don't need import range for sheet PDLine, you can use this one for each PDLine Sheet just change filter with your sheet:

={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'") )}

And for Daysummary I test in mine:

In cell A4 I put:

=query({query(JobList!A10:O,"Select A,O where H<>'' ",-1),ARRAYFORMULA(if(query(JobList!H10:H,"Select H where H<>'' ",-1)="PDLine1",query(JobList!H10:O,"Select O where H<>'' ",-1),0)),ARRAYFORMULA(if(query(JobList!H10:H,"Select H where H<>'' ",-1)="PDLine2",query(JobList!H10:O,"Select O where H<>'' ",-1),0))},"Select Col1,sum(Col3),sum(Col4),Sum(Col2) where Not Col1='' group By Col1 LABEL Sum(Col3) '',Sum(Col4) '',Sum(Col2) ''",0)

and in E4 I put:

=query({query(JobList!A10:I,"Select A,I where H<>'' "),ARRAYFORMULA(if(query(JobList!H10:I,"Select H where H<>'' ")="PDLine1",query(JobList!H10:I,"Select I where H<>'' "),0)),ARRAYFORMULA(if(query(JobList!H10:I,"Select H where H<>'' ")="PDLine2",query(JobList!H10:I,"Select I where H<>'' "),0))},"Select sum(Col3),sum(Col4),Sum(Col2) where not Col1='' group By Col1 label sum(Col3) '',sum(Col4) '',sum(Col2) ''",0)

May be this can be used in script

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);
}

In yours may will be like this:

=query({query(JobList!A10:O,"Select A,O where H<>'' ",-1),ARRAYFORMULA(if(query(JobList!H10:H,"Select H where H<>'' ",-1)="PDLine1",query(JobList!H10:O,"Select O where H<>'' ",-1),0)),ARRAYFORMULA(if(query(JobList!H10:H,"Select H where H<>'' ",-1)="PDLine2",query(JobList!H10:O,"Select O where H<>'' ",-1),0))},"Select Col1,sum(Col3),sum(Col4),Sum(Col2) where Not Col1='' group By Col1 LABEL Sum(Col3) '',Sum(Col4) '',Sum(Col2) ''",0)

and in E4 I put:

=query({query(JobList!A10:I,"Select A,I where H<>'' "),ARRAYFORMULA(if(query(JobList!H10:I,"Select H where H<>'' ")="PDLine1",query(JobList!H10:I,"Select I where H<>'' "),0)),ARRAYFORMULA(if(query(JobList!H10:I,"Select H where H<>'' ")="PDLine2",query(JobList!H10:I,"Select I where H<>'' "),0))},"Select sum(Col3),sum(Col4),Sum(Col2) where not Col1='' group By Col1 label sum(Col3) '',sum(Col4) '',sum(Col2) ''",0)

May be this can be used in yours

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,B,C,D,E,F,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);
}

Upvotes: 1

Related Questions