Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
UNPIVOT GS
/**
* Unpivot a pivot table of any size.
*
* @param {A1:D30} data The pivot table.
* @param {1} fixColumns Number of columns, after which pivoted values begin. Default 1.
* @param {1} fixRows Number of rows (1 or 2), after which pivoted values begin. Default 1.
* @param {"city"} titlePivot The title of horizontal pivot values. Default "column".
* @param {"distance"[,...]} titleValue The title of pivot table values. Default "value".
* @return The unpivoted table
* @customfunction
*/
function unpivot(data,fixColumns,fixRows,titlePivot,titleValue) {
var fixColumns = fixColumns || 1; // how many columns are fixed
var fixRows = fixRows || 1; // how many rows are fixed
var titlePivot = titlePivot || 'column';
var titleValue = titleValue || 'value';
var ret=[],i,j,row,uniqueCols=1;
// we handle only 2 dimension arrays
if (!Array.isArray(data) || data.length < fixRows || !Array.isArray(data[0]) || data[0].length < fixColumns)
throw new Error('no data');
// we handle max 2 fixed rows
if (fixRows > 2)
throw new Error('max 2 fixed rows are allowed');
// fill empty cells in the first row with value set last in previous columns (for 2 fixed rows)
var tmp = '';
for (j=0;j<data[0].length;j++)
if (data[0][j] != '')
tmp = data[0][j];
else
data[0][j] = tmp;
// for 2 fixed rows calculate unique column number
if (fixRows == 2)
{
uniqueCols = 0;
tmp = {};
for (j=fixColumns;j<data[1].length;j++)
if (typeof tmp[ data[1][j] ] == 'undefined')
{
tmp[ data[1][j] ] = 1;
uniqueCols++;
}
}
// return first row: fix column titles + pivoted values column title + values column title(s)
row = [];
for (j=0;j<fixColumns;j++) row.push(fixRows == 2 ? data[0][j]||data[1][j] : data[0][j]); // for 2 fixed rows we try to find the title in row 1 and row 2
for (j=3;j<arguments.length;j++) row.push(arguments[j]);
ret.push(row);
// processing rows (skipping the fixed columns, then dedicating a new row for each pivoted value)
for (i=fixRows;i<data.length && data[i].length > 0;i++)
{
// skip totally empty or only whitespace containing rows
if (data[i].join('').replace(/\s+/g,'').length == 0 ) continue;
// unpivot the row
row = [];
for (j=0;j<fixColumns && j<data[i].length;j++)
row.push(data[i][j]);
for (j=fixColumns;j<data[i].length;j+=uniqueCols)
ret.push(
row.concat([data[0][j]]) // the first row title value
.concat(data[i].slice(j,j+uniqueCols)) // pivoted values
);
}
return ret;
}
@helmanfrow

This comment has been minimized.

Copy link

@helmanfrow helmanfrow commented Jul 21, 2020

This is an excellent script. I use it every day. Thank you!

@grimelda

This comment has been minimized.

Copy link

@grimelda grimelda commented Sep 9, 2020

Wow, this is exactly what I have needed all my life. Excellent script, thank you.

@daniel-klaus

This comment has been minimized.

Copy link

@daniel-klaus daniel-klaus commented Nov 24, 2020

Really useful. Thank you so much for sharing 🥇

@twhay

This comment has been minimized.

Copy link

@twhay twhay commented Jan 20, 2021

As much as I love using R or Python to do a quick unpivot, sometimes you just need to do it in Google Sheets. This is a great Google App Script function, thank you for sharing.

@bactisme

This comment has been minimized.

Copy link

@bactisme bactisme commented Feb 20, 2021

Hello,

With the two rows of headers setup, the functions was not exactly doing what I wanted.
Here is what I am trying to achieve :

Capture d’écran 2021-02-20 à 11 07 03

In order to do that I modified your version. Here is the forked gist :
https://gist.github.com/bactisme/fbb1fd1c19a9f78e5f15fc7e218a4904

Thanks again.

@helmanfrow

This comment has been minimized.

Copy link

@helmanfrow helmanfrow commented Feb 22, 2021

By the way, Google Sheets has an undocumented function called FLATTEN() which can be used to unpivot wide data much like this script can. I've used it and it works quite well. Instructions are floating around the web and are not too hard to find.

@SandroMarques1972

This comment has been minimized.

Copy link

@SandroMarques1972 SandroMarques1972 commented Sep 2, 2021

Thank you for sharing! It was exactly what I needed!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment