Easy Way to Unpivot Google Sheets Data
I have been a strong advocate for Google Sheets, and over the years I have come to appreciate how fast and versatile the Google API is and how easy it is to get customization out of it.
Working a lot with messy data, I have had to find ways to easily clean up my sheets, and one of the tasks I face the most is unpivoting data.
What is Pivoted Data
Pivoting data is a technique that rotates data from a state of rows to a state of columns, possibly aggregating multiple source values. If you look at the Video Game Sales Data above you can see that Crimsonland generated $0.1 M in revenue from PS, $0.7M in PS2 sales etc. While this is easy to draw insights on using a table sometimes we need to unpivot our data so we can easily run reports in BI tools such as Tableau or PowerBi or create new reports in Sheets or Excel.
Unpivot Formula
No, there is no formula built into Google Sheets to unpivot data but with the power of Google Apps Script, it is easy to create and share one!
First, you need to open up your Script Editor under Tools. After that just copy and paste this code below and you’re ready to go:
/**
* UNPIVOT ARRAY
*
* @param array Select the Array of Data you want to Unpivot
* @param fixedCols # of fixed rows to keep
* @customfunction
*/
function UNPIVOT(array,fixedCols) {
var dataArray = array;
var resultsArray = [];
var colArr = [];
for(var i=1;i<dataArray.length;i++){
for(var j=fixedCols;j<dataArray[0].length;j++){
if(dataArray[i][j] != “”){
colArr.push(dataArray[i].slice(0, fixedCols));
resultsArray.push([dataArray[0][j],dataArray[i][j]]);
}
}
}
for(var i=0;i<colArr.length;i++){
for(var j=0;j<resultsArray[0].length;j++){
colArr[i].push(resultsArray[i][j])
}
}
return colArr;
}
After that just go back to your spreadsheet and use UNPIVOT(array,fixedCol) to unpivot any data. Just select the array and the number of fixed columns. For our game sales data we want to keep the first 2 columns so for the fixedCol parameter we have 2.
Well, Thanks again everybody and I hope you all found this useful!
