如何使用Google Apps脚本取消透视表？ [英] How to unpivot a table using Google Apps Script?

问题描述

我在电子表格中有一个表格，想要使用Google Apps脚本取消透视表：原始表格的每个月行必须变成新表格中的多行。问题在于代码无法产生预期的结果。

I have a table in a spreadsheet which I want to unpivot using google apps script: each one of the month rows of the original table have to become multiple rows in the new table. The problem is the code doesn't produce the expected result.

需要创建以这种方式结尾的数组（表的每一行以一个不同的月份结尾）：

Insted of creating arrays that ends like this (each line of the table ends with one different month):

[[...,'April'],[...,'September'],[...,'December']]

它正在产生此结果（每行以原始表）：

It's producing this (each line ends with the last month value of that line in the original table):

[[...,'December'],[...,'December'],[...,'December']]

有人看到错误了吗？

function myFunction() {
  var ay_datos = [
    ['State', 'Month1', 'Month2', 'Month3', 'Number of months', 'Month'],
    ['California', 'April', 'September', 'December', 3, ''],
    ['Texas', 'January', 'March', '', 2, ''],
  ];
  var ay_new = [
    ['State', 'Month1', 'Month2', 'Month3', 'Number of months', 'Month'],
  ];

  for (i = 1; i < ay_datos.length; i++) {
    var num_months = ay_datos[i][4];
    var ay_linea = ay_datos[i];

    for (j = 0; j < num_months; j++) {
      ay_linea[5] = ay_linea[1 + j];
      ay_new.push(ay_linea);
    }
  }
}

推荐答案

您每次循环都推送相同的数组。对数组所做的任何修改将反映同一数组的所有引用。使用切片复制数组：

You're pushing the same array each time in a loop. Any modifications done to the array will reflect on all references of the same array. Use slice to copy a array:

  ay_linea[5] = ay_linea[1 + j];
  ay_new.push(ay_linea.slice(0));

实时代码段：

function myFunction() {
  const ay_datos = [
    ['State', 'Month1', 'Month2', 'Month3', 'Number of months', 'Month'],
    ['California', 'April', 'September', 'December', 3, ''],
    ['Texas', 'January', 'March', '', 2, ''],
  ];
  const ay_new = [
    ['State', 'Month1', 'Month2', 'Month3', 'Number of months', 'Month'],
  ];

  for (let i = 1; i < ay_datos.length; i++) {
    const num_months = ay_datos[i][4];
    const ay_linea = ay_datos[i];

    for (let j = 0; j < num_months; j++) {
      ay_linea[5] = ay_linea[1 + j];
      ay_new.push(ay_linea.slice(0));
    }
  }
  return ay_new;
}
console.log(myFunction());

这篇关于如何使用Google Apps脚本取消透视表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！