How to unpivot a table using Google Apps Script?

Question

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']]

Can someone see the mistake?

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

What's the expected output for your data? *doesn't work properly* is a useless description of the problem. We know that it doesn't work. Isn't that why you're here? Instead describe the problem: What do you expect it to do? Unpivot. Yes but what's the exact output you're expecting and what does it produce instead? Does it throw a error? Pay attention to [mcve] — TheMaster, Mar 04 '20 at 16:58
@TheMaster I edited my question following your tips. Thanks for them! Let's see if you can find the answer. — Gustavo Rita, Mar 05 '20 at 08:22

score 2 · Accepted Answer · answered Mar 05 '20 at 08:50

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

Live snippet:

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

From your answer, I could use the hidden part. Thank you. – Tanaike Mar 06 '20 at 00:35 — Tanaike, Mar 06 '20 at 00:35
@Tanaike Glad I could help! – TheMaster Mar 06 '20 at 07:29 — TheMaster, Mar 06 '20 at 07:29

How to unpivot a table using Google Apps Script?

1 Answers1