Google Sheets - Combine Multiple Sheets with Different Structure into One

Question

I am trying to create a cash flow report which should summarize the transactions made in several bank accounts. Every bank account has a separate tab and a different layout. In order to create a pivot table, I need to combine all the tabs into one tab with a unified layout and also extract the tab name. I'd like the data to be sorted in a table with the following columns/headers - Date; Amount; Category; Reference; Tab (sheet) Name The table should be sorted by date

Is it possible? How would you recommend doing it?

Here is a link to the sample file - https://docs.google.com/spreadsheets/d/10dXvOIr4vcL6M2UARUxfi88QKDaBCREuW5ca4kOqpQE/edit?usp=sharing

Any help would be greatly appreciated, thanks

I tried using a query function but managed only to combine each column separately so the columns received were not synched.

Answer 1

I believe your goal is as follows.

• You have 3 sheets like "Account 1", "Account 2", and "Account 3".
• Each sheet has the different order of the 1st header row.
• You want to populate all values from 3 sheets by the order of header as follows.
  • Date,Amount,Category,Reference,Tab (sheet) Name
• And, you want to sort the values by the date column.

In order to correctly understand your situation by other users, when I saw your provided Spreadsheet, the headers of "Account 1", "Account 2", and "Account 3" as follows.

• "Account 1" sheet: ["Date","BA","Internal Voucher number","Voucher number","GKonto","Amount","Haben","StS","Ausziff.Nr.","Reference","Catagory",""]
• "Account 2" sheet: ["Book date","Value date","Name","Booking text","Purpose of use","Betrag","Catagory"]
• "Account 3" sheet: ["Date","Amount","Reference","Catagory"]

In this case, for example, the following issues are existing.

1. You want to use Category header title. But, the actual sheet has Catagory. Is this a spelling mistake?
2. In "Account 2" sheet, "Date", "Amount", and "Reference" columns are not existing. "Book date", "Betrag", and "Purpose of use" are "Date", "Amount", and "Reference" columns, respectively? I assumed like that in my answer.

When a sample script for achieving your goal by including these issues, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of your provided Google Spreadsheet, and, save the script.

function myFunction() {
  const headers = ["Date", "Amount", "Category", "Reference", "Tab (sheet) Name"]; // This header titles are from your question.

  // In order to match the header titles to the actual header titles, I prepared the following object.
  // If your actual situation is different from the following values, please modify it.
  const adjustHeaderTitles = { "Date": "Date", "Book date": "Date", "Amount": "Amount", "Betrag": "Amount", "Category": "Category", "Catagory": "Category", "Reference": "Reference", "Purpose of use": "Reference" };

  // Retrieve sheets of "Account #" and create an array including your expected values and sort it as the date.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const hMap = new Map(Object.entries(adjustHeaderTitles).map(([k, v]) => [k.toLowerCase(), v]));
  const values = ss.getSheets().reduce((ar, sheet) => {
    const sheetName = sheet.getSheetName();
    if (sheetName.includes("Account")) {
      const [h, ...v] = sheet.getDataRange().getValues();
      const idx = headers.map(hh => h.findIndex(k => hMap.get(k.toLowerCase()) == hh)).filter(j => j != -1);
      ar = [...ar, ...v.map(r => [...idx.map(i => r[i]), sheetName])];
    }
    return ar;
  }, []).sort(([a], [b]) => a.getTime() > b.getTime() ? 1 : -1);

  // Put the array to a new sheet.
  const res = [headers, ...values];
  const newSheet = ss.getSheetByName("newSheet") || ss.insertSheet("newSheet");
  newSheet.clear().getRange(1, 1, res.length, res[0].length).setValues(res);
}

• When you run myFunction, a new sheet of newSheet is created and the result values are put into newSheet.

Testing:

When this script is run with your provided Spreadsheet, the following result is obtained in newSheet.

Note:

• From your expected header titles and the header titles in your provided Spreadsheet, I prepared an object (adjustHeaderTitles) for searching the header titles. This object is to your provided Spreadsheet. So, if your actual situation is different from the following values, please modify it.

• I supposed that "Book date" of "Account 2" sheet is as "Date". If you want to use "Value date" as "Date", please modify "Book date": "Date" to "Value date": "Date".

• This sample script is for your provided Spreadsheet. When I tested my script using your provided Spreadsheet, no error occurs. So, please test this script to your provided Spreadsheet. If you change the Spreadsheet, the script might not be able to be used. Please be careful about this.

References:

• reduce()
• map()
• sort()