How to unpivot table in Google Sheets?

Question

I would like to unpivot the pink table to display as shown in the green table in the image linked below. [transpose pink table to desired green table]

Pivoting in Google Sheets didn't work as columns B, C and D are separate.

Any ideas on how to get this to work using apps script and/or Google Sheets query?

Cooper · Answer 1 · 2022-09-04T15:01:32.237

Unpivot

function unpivot() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  const [h, ...vs] = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getValues();
  let n = 0;
  let obj = { pA: [] }
  vs.forEach((r, i) => {
    if (!obj.hasOwnProperty(r[0])) {
      obj[r[0]] = { sA: [] };
      obj.pA.push(r[0]);
    } //collect the first column as properties
    r.forEach((c, j) => {
      if (j > 0) {
        if (!obj[r[0]].hasOwnProperty(h[j])) {
          obj[r[0]][h[j]] = c;
          obj[r[0]].sA.push(h[j])
        } //collecting the first row as properties and assigning the value to each subproperty
      }
    });
  });
  let oA = [];
  obj.pA.forEach(p => {
    obj[p].sA.forEach(s => oA.push([p,s,obj[p][s]]));
  });//separating them into a two dimensional array
  //Logger.log(JSON.stringify(oA));
  oA.sort((a,b) => new Date(a[1]).valueOf() - new Date(b[1]).valueOf());//sorting by date
  osh.clearContents();
  osh.getRange(1,1,oA.length,oA[0].length).setValues(oA);//displaying
}

Sheet0:

Group	1/1/2022	2/1/2022	3/1/2022
A	1	5	9
B	2	6	10
C	3	7	11
D	4	8	12

Sheet1:

A	1/1/2022	1
B	1/1/2022	2
C	1/1/2022	3
D	1/1/2022	4
A	2/1/2022	5
B	2/1/2022	6
C	2/1/2022	7
D	2/1/2022	8
A	3/1/2022	9
B	3/1/2022	10
C	3/1/2022	11
D	3/1/2022	12

How to unpivot table in Google Sheets?

1 Answers1