I can import multiple ranges to a page using the following formula:

{{"Source","Date" ,"Amount" ,"Business" ,"Category" ,"TransactionID" ,"Account" ,"Status"};
  Let(SheetID,B1,SheetName, A1, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0));
  Let(SheetID,B2,SheetName, A2, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0));
  Let(SheetID,B3,SheetName, A3, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0))
}

However I need to edit the function everytime a new sheet is added to the list.

Is there a way to simplify this formula using ARRAYFORMULA?

I tried different ways and got the following error:

"In ARRAY_LITERAL, an Array Literal was missing values for one or more rows."

This is the array formula version I used:

={{"Source","Date" ,"Amount" ,"Business" ,"Category" ,"TransactionID" ,"Account" ,"Status"};
  ARRAYFORMULA( Let(SheetID,B1:B3,SheetName, A1:A3, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0)))
}

I also tried using MAP function as shown below:

=MAP(A1:A3,B1:B3,LAMBDA(SheetName,SheetID,query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0)))

However, Got the following errors:

1. Spreadsheet can not be found
2. Result should be a single row.

Sample Data: (link to sample sheet

1) Sheet1-Data

I can import multiple ranges to a page using the following formula:

{{"Source","Date" ,"Amount" ,"Business" ,"Category" ,"TransactionID" ,"Account" ,"Status"};
  Let(SheetID,B1,SheetName, A1, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0));
  Let(SheetID,B2,SheetName, A2, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0));
  Let(SheetID,B3,SheetName, A3, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0))
}

However I need to edit the function everytime a new sheet is added to the list.

Is there a way to simplify this formula using ARRAYFORMULA?

I tried different ways and got the following error:

"In ARRAY_LITERAL, an Array Literal was missing values for one or more rows."

This is the array formula version I used:

={{"Source","Date" ,"Amount" ,"Business" ,"Category" ,"TransactionID" ,"Account" ,"Status"};
  ARRAYFORMULA( Let(SheetID,B1:B3,SheetName, A1:A3, query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0)))
}

I also tried using MAP function as shown below:

=MAP(A1:A3,B1:B3,LAMBDA(SheetName,SheetID,query(IMPORTRANGE(SheetID,"___Transactions"),"Select '"&SheetName&"', Col1, Col2, Col3, Col4, Col5, Col6, Col7 Where Col1 Is Not Null Label '"&SheetName&"' ''",0)))

However, Got the following errors:

1. Spreadsheet can not be found
2. Result should be a single row.

Sample Data: (link to sample sheet

1) Sheet1-Data

2) Sheet2-Data

3) Sheet3-Data

Expected Output:

• google-sheets

• Please provide a markdown table (you may create one with the help of this link ) or an anonymous sample spreadsheet (using this link). – Jats PPG Commented 2 days ago
• Consider providing input and expected output as a markdown table in the question. You may create one with the help of this link, which is easy to copy/paste . Also, note that your email address can also be accessed by the public, if you share Google Sheets. – leylou Commented 2 days ago
• Instead of map(), reduce() should do. similar Q&A here which you could improvise on... – rockinfreakshow Commented 2 days ago
• Are you open to solving this question using Google Apps Script? – leylou Commented yesterday
• You may try this formula: =Query( {Sheet1!A:Z ; Sheet2!A:Z ; Sheet3!A:Z },"Select * WHERE Col1 IS NOT NULL ") – leylou Commented 23 hours ago

2 Answers 2

I'm not sure If I missed something in the question, but the below basic and straightforward formula does the job. If there is a new sheet, you can update the query data {Sheet1!A2:C; Sheet2!A2:C; Sheet3!A2:C; etc..}:

=QUERY(
  {Sheet1!A2:C; Sheet2!A2:C; Sheet3!A2:C},
  "select Col1, Col2, Col3 where Col1 is not null order by Col1 asc",
  0
)

Regarding:

However I need to edit the function everytime a new sheet is added to the list.

To avoid updating your formula every time you add a sheet, you can create a custom function that automatically combines data from all sheets (regardless of their names) except those you want to ignore. For example, you can set up a sheet called Exception: where you list the names of sheets to ignore (like “Final” and “Exception”).

Exception Sheet:

Final Sheet:

Place in A1 this formula:

=getCombinedData(Exception!A2:A)

Open Extensions > Apps Script in your Google Sheet and paste in this code:

Code:

/**
 * Combines data from all sheets (columns A:C starting at row 2) in the order they appear,
 * excluding any sheets whose names are listed in the given exception range.
 *
 * @param {Range} exceptionRange A vertical range listing sheet names to ignore (for example, Final, Exception).
 * @return {Array} A 2D array with a header row followed by the combined data.
 * @customfunction
 */
function getCombinedData(exceptionRange) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  
  // Manually flatten the exceptionRange (it comes in as a 2D array)
  let exValues = [];
  if (exceptionRange && Array.isArray(exceptionRange)) {
    if (Array.isArray(exceptionRange[0])) {
      exValues = exceptionRange.reduce((acc, row) => acc.concat(row), []);
    } else {
      exValues = exceptionRange;
    }
  }
  
  // Build a set of sheet names to ignore.
  const exceptions = new Set();
  exValues.forEach(name => {
    if (name && typeof name === 'string') {
      exceptions.add(name.trim());
    }
  });
  
  const header = ["Date", "Amount", "TransactionID"];
  let combinedData = [];
  
  // Process sheets in the order they appear.
  sheets.forEach(sheet => {
    const sheetName = sheet.getName();
    if (exceptions.has(sheetName)) return;  // Skip this sheet.
    
    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return;  // No data.
    
    // Get data from A2:C.
    const data = sheet.getRange(2, 1, lastRow - 1, 3).getValues();
    
    // Remove rows where Date is empty.
    const validData = data.filter(row => row[0] !== "" && row[0] !== null);
    if (validData.length > 0) {
      combinedData = combinedData.concat(validData);
    }
  });
  
  // Return the header row plus all the data.
  return [header, ..binedData];
}

It's unclear whether your different sheets containing that month's transactions are located within the same spreadsheet or if they are indeed located in separate spreadsheets. I would advice that at a minimum, you keep at least 12 months for a year on the same spreadsheet...all of them if you can.

I've updated your sample spreadsheet with examples of how to handle both cases.

Template Sheet

To begin with, since each of your sheets appear to be structured the same, I recommend you make a Template sheet. Include a table for your transactions and any other summary or additional data tables that you want for each month. Assign Named Ranges to key ranges that you would like to access on each sheet later on. When you're ready to create a new month's sheet for transaction, right-click on this Template's tab and duplicate it. The named ranges will also be duplicated.

Separate Spreadsheets

If these sheets are in fact on separate spreadsheets, then REDUCE over the sheet names and the SpreadsheetIDs.

=REDUCE({"Date","Amount","TransactionID"},SEQUENCE(COUNTA(F:F)-1,1,2),LAMBDA(tot,i,
  IF(LEN(INDEX(F:F,i)),
   IFERROR(VSTACK(tot,
    QUERY(IMPORTRANGE(INDEX(G:G,i),INDEX(F:F,i)&"!Transactions"),
          "Select * Where Col1 Is Not Null label Col1 '', Col2 '', Col3 ''",1))),
   tot)))

Different Sheets/Same Spreadsheet

If all of your monthly sheets are in the same spreadsheet, you don't need to use IMPORTRANGE or be concerned about the SpreadsheetID. To combine current and future month's sheets, be sure to have a naming scheme for each sheet. My naming scheme is Feb 25. Designate a start date to go along with your first month's sheet name.My start date is located in F1. Here's the modified version of rockinfreakshow's REDUCE formula. It goes month-by-month checking first to see if that month is a valid sheet name and stacks together the ones that are valid.

=REDUCE({"Date","Amount","TransactionID"},
        SEQUENCE(DATEDIF(F1,TODAY(),"M")),
        LAMBDA(tot,i,
   IF(ISREF(INDIRECT(TEXT(EOMONTH(F1,i-1),"MMM YY")&"!Transactions")),
     IFERROR(VSTACK(
       tot,
       QUERY(
         INDIRECT(TEXT(EOMONTH(F1,i-1),"MMM YY")&"!Transactions"),
         "Select * Where Col1 Is Not Null label Col1 '', Col2 '', Col3 ''",1))),
    tot)))