代码之家 › 专栏 › 技术社区 › esafwan

在Google工作表中,将行转换为按列分组的列

transpose google-sheets google-apps-script

esafwan · 技术社区 · 5 年前

根据列值更改Google sheet中的数据,以便更好地理解

由此:

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

为此:

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

我检查了转置等功能,它没有满足我的实际需要。

0 回复 | 直到 5 年前

Dmitry Kostyuk 5 年前

将下面代码段中的函数作为自定义函数运行:

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));

Google Sheets中的函数结果:

该函数首先构建,然后将其转换为一个新的2D数组。

Mike Steelson 5 年前

假设数据位于A列到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

Mike Steelson 5 年前

尝试

=newTab(A1,A2:C9)

具有自定义功能

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