Unpivot data in Google Sheets with Query Function

Question

I have data in a spreadsheet that is being pulled and refreshed through the Salesforce connector that looks like this:

Person	State	LA ORDR	LA RTRN	HA ORDR	HA RTRN	MO ORDR	MO RTRN
Carly	GA	3	5	20	10	3	1
Megan	GA	4	5	32	12	3	2
Jenny	TN	5	5	32	11	4	3

I am trying to unpivot the data so that I end up with:

Person	State	Product	ORDR	RTRN
Carly	GA	LA	3	5
Carly	GA	HA	20	10
Carly	TN	MO	3	1
Megan	GA	LA	4	5
Megan	GA	HA	32	12

I was able to successfully get to this:

Person	State	ORDR	Product
Carly	GA	3	LA
Carly	GA	20	HA
Carly	TN	3	MO
Megan	GA	4	LA
Megan	GA	32	HA

Using this formula:

={
QUERY(A1:E8, "select A, B, C, 'LA' label C 'ORDR', 'LA' 'Product'", 1);
QUERY(A1:E8, "select A, B, D, 'HA' offset 1 label 'HA' ''", 0);
QUERY(A1:E8, "select A, B, E, 'MO' offset 1 label 'MO' ''", 0)
}

I can't seem to figure out if it possible to add in the additional column RTRN. Anyone help would be appreciated!

Answer 1

Alternative Option

Alternatively, you could also take advantage of the built-in scripting in Google Sheets to create a custom =unpivot() formula for a cleaner usage of the formula in your spreadsheet file. Please see the sample script below.

You may follow the steps in creating & saving a custom function in Google Sheet.

[UPDATED] Sample custom formula script

function unpivot(range) {
  //Filter Person in A, State in E, LA Order in L, LA RTRN in N, HA ORDR in O, HA RTRN in Q, MO ORDR in R, and MO RTRN in T
  const indices = [0, 4, 11, 13, 14, 16, 17, 19];
  range = range.map(row => row.filter((_, index) => indices.includes(index)));

  var rawData = range.map((data, index) => index > 0 && data.map((x, i) => i > 1 && [range[0][i].split(' ')[0], range[0][i].split(' ')[1], x, data[0], data[1]]).filter(x => x)).filter(y => y).flat();
  var container = [['Person', 'State', 'Product', 'ORDR', 'RTRN']];
  rawData.forEach((d, i, arr) => {
    try {
      d[0] == arr[i + 1][0] && d[3] == arr[i + 1][3] && d[4] == arr[i + 1][4] && container.push([d[3], d[4], d[0], d[2], arr[i + 1][2]])
    } catch { }
  });
  return container;
}

Demo

Using the =unpivot() custom function.

Reference

• Custom Functions in Google Sheets
• JavaScript Array map()
• JavaScript Array filter()
• JavaScript Array forEach()
• JavaScript flat()