CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstranspose
esafwan
esafwan

Reputation: 18029

Transpose rows to column grouped by a column in Googles Sheets

Change data in Google sheet for better understanding based on a column value

From this:

entity_id  |  data_key      |  data_value   
    1      |  name          |  some name
    1      |  author        |  john
    1      |  likes         |  12 
    2      |  name          |  another name
    2      |  author        |  sam 
    3      |  name          |  different name 
    3      |  author        |  mary
    3      |  likes         |  3

To this:

entity_id  |  name          |   author    |    likes 
    1      |  some name     |    john     |     12     
    2      |  another name  |    sam      |      
    3      |  different name|    mary     |     3

I checked features like transpose, it falls short of what I actually need.

Upvotes: 1

Views: 829

Answers (3)

Mike Steelson
Mike Steelson

Reputation: 15328

Try

=newTab(A1,A2:C9)

with custom function

function newTab(title,data){
  var headers=[]
  var items = new Map()
  var n = 0
  data.forEach(function(elem){
    items.set(elem[1],'')
    n= Math.max(n,elem[0])
  })
  items.forEach(function(value, key) {headers.push(key)})
  var result = Array.from({ length: n + 1 }, () => Array.from({ length: headers.length + 1 }, () => ''));
  result[0] = [title, ...headers]
  data.forEach(function(elem){
    result[elem[0]][headers.indexOf(elem[1])+1]=elem[2]
    result[elem[0]][0]=elem[0]
  })
  return(result)
}

https://docs.google.com/spreadsheets/d/12LOBTcialZ8wef9enII-hFsjrRnuOhx35_EXtg4Zreo/copy

Upvotes: 0

Mike Steelson
Mike Steelson

Reputation: 15328

Assuming that data is located columns A to C

={A1,transpose(unique(B2:B));arrayformula(ROW(A2:A)-1),arrayformula(iferror(vlookup((row(A2:A)-1)&"|"&transpose(unique(B2:B)),{arrayformula(A2:A&"|"&B2:B),arrayformula(C2:C)},2,0)))}

https://docs.google.com/spreadsheets/d/12LOBTcialZ8wef9enII-hFsjrRnuOhx35_EXtg4Zreo/copy

Upvotes: 0

Dmitry Kostyuk
Dmitry Kostyuk

Reputation: 1459

Run the function in the below snippet as a custom function:

const data = [
  ['entity_id', 'data_key', 'data_value'],
  [1, 'name', 'some name'],
  [1, 'author', 'john'],
  [1, 'likes', 12],
  [2, 'name', 'another name'],
  [2, 'author', 'sam'],
  [3, 'name', 'different name'],
  [3, 'author', 'mary'],
  [3, 'likes', 3],
];

/**
 *@customfunction
 */
const myTranspose = range => {
  range.shift();
  const headers = [];
  const undefToStr = value => (undefined === value ? '' : value);
  const obj = range.reduce((acc, row) => {
    if (!acc[row[0]]) acc[row[0]] = {};
    acc[row[0]][row[1]] = row[2];
    if (-1 === headers.indexOf(row[1])) headers.push(row[1]);
    return acc;
  }, {});
  return Object.entries(obj).reduce(
    (acc, entry) => {
      const row = [entry[0]];
      headers.forEach(header => row.push(undefToStr(entry[1][header])));
      acc.push(row);
      return acc;
    },
    [['entity_id', ...headers]]
  );
};

console.log(myTranspose(data));

The result of the function in Google Sheets: myTranspose function

The function first builds and then transposes it into a new 2D array.

Upvotes: 3

Related Questions