Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
* 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];
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;
// 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]);
// 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++)
for (j=fixColumns;j<data[i].length;j+=uniqueCols)
row.concat([data[0][j]]) // the first row title value
.concat(data[i].slice(j,j+uniqueCols)) // pivoted values
return ret;
Copy link

helmanfrow commented Jul 21, 2020

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

Copy link

grimelda commented Sep 9, 2020

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

Copy link

daniel-klaus commented Nov 24, 2020

Really useful. Thank you so much for sharing 🥇

Copy link

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.

Copy link

bactisme commented Feb 20, 2021


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 :

Thanks again.

Copy link

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.

Copy link

SandroMarques1972 commented Sep 2, 2021

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

Copy link

chinmaykhandalkar commented Jun 20, 2022

I encountered "Too large data" error when I run this script, any ideas how I can get around that?

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