Google Sheets GROUPBY Query that combines values into one cell to satisfy grouped columns

Question

Sorry for the confusing title, I'm not sure how to best describe it. I've attached screenshots to clarify. Please note that I have cut off the first column because of sensitive information. You can assume all values in the first column will be the same. Here's a dummy sheet to work with What I have is this:

The result I'm looking for is as follows:

So obviously in this case I did it manually to show the result I'm looking for, but what I want it to do is group by SKU where the species, form, and size are the same. This will mean that the total net weight and unit quantity columns will be summed. The issue is the "Date" column. I want this to show ALL dates from the grouped columns. I would also like it to do this with the "Case type" and "Location" columns.

For reference, here's the Query I've been using that does not reach this result (it just removes the date column). As I noted before, I have cut off the first column because of sensitive information. You can assume all values in the first column will be the same:

=QUERY({IMPORTRANGE("MY URL HERE", "'Finalized Inventory'!A3:K")}, "select Col1, Col3, Col4, Col5, Col6, SUM(Col8), Col9, Col10 where Col1<>'' GROUP BY Col1, Col3, Col4, Col5, Col6, Col8, Col9, Col10 label SUM(Col8)''")

I'm not particularly attached to this method. If there's a better way to do this, through a different query or an app script, which I'm comfortable using, that would be super helpful.

Edit: To clarify, the data is updated often and I'm seeking a solution that automatically updates as the data is updating. What I need is basically exactly the behavior of the Power Sheets "Merge and Combine" functionality, except I need it to auto-update as the data changes, where the power sheets functionality creates and static table.

Answer 1

Solution:

You could use Apps Script to do the following:

• Get data from your source sheet (called Source in the sample below), using getValues().
• Get the unique combinations of Customer, Species, Form, Size and SKU. This would require retrieving only these values from these rows, using map, and then removing all the duplicate arrays from the resulting 2D array (see this answer, for example). The resulting array (grups in the sample below) will contain all the unique combinations of Customer, Species, Form, Size and SKU.
• Iterate through the unique combinations, and for each combination, retrieve which rows (groupRows in the sample below) from the original data match that combination, using filter.
• Use reduce on the groupRows to sum Unit, Weight and add the different Dates, Case Types and Locations to the same value.
• Remove duplicate Dates, Case Types and Locations from the resulting array (called groupedData). See this question for how to remove duplicate values from an array.
• Write data to your destination sheet (called Destination in the sample below) using setValues(values), or use return groupedData; instead if you want this to be a custom function.

Code snippet:

// Copyright 2021 Google LLC.
// SPDX-License-Identifier: Apache-2.0

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName("Source");
  const sourceValues = sourceSheet.getRange(3,1,sourceSheet.getLastRow() - 2, sourceSheet.getLastColumn()).getValues(); // Get source data
  const groups = sourceValues.map(row => { // Get unique combinations
    return JSON.stringify([row[0]].concat(row.slice(2,5)).concat(row[8]));
  }).reverse().filter((e, i, a) => a.indexOf(e, i + 1) === -1)
    .reverse().map(JSON.parse);
  let groupedData = groups.map(group => {
    const groupRows = sourceValues.filter(row => { // Get matching rows
      return JSON.stringify([row[0]].concat(row.slice(2,5)).concat(row[8])) === JSON.stringify(group);
    });
    return groupRows.reduce((acc, current) => { // Adding the values from same combination
      const date = acc[1] === "" ? current[1] : acc[1] + "\n" + current[1];
      const caseType = acc[5] === current[5] ? acc[5] : acc[5] + "\n" + String(current[5]);
      const location = acc[11] === "" ? current[11] : acc[11] + "\n" + current[11];
      const unit = Number(acc[6]) + Number(current[6]);
      const weight = Number(acc[7]) + Number(current[7]);
      const comments = acc[10] + "\n" + current[10];
      return [group[0], date, ...group.slice(1,4), caseType, unit, weight, group[4], current[9], comments, location];
    }, Array(12).fill(""));
  });
  groupedData.forEach(row => { // Removing duplicate dates, case types, locations
    row[1] = [...new Set(row[1].split("\n"))].join("\n");
    row[5] = [...new Set(row[5].split("\n"))].join("\n");
    row[11] = [...new Set(row[11].split("\n"))].join("\n");
  });
  return groupedData;
}

Output: