How to getRange in all cell appear when reshape data for wide to long form by monthly columns name GooglespreadSheet

Question

In R , data.table library dcast() can transform dataset from wide to long shape ,how can i do this in googlespreadsheet?

Sheet1

Name       Type YTD JAN FEB MAR
Product 1   A   8    1  3   4
Product 2   B   519  41 23  455
Product 3   C   32   2  25  5
   NA       D   3    NA 2   1

Sheet2 A B C D E F 1 Name Type YTD JAN FEB MAR 2 =filter(Sheet1!A2:F5,not(isblank(Sheet1!A2:A5)))

Show reshaped data in Sheet3 from A1

[

** C column for YTD is not necessarily needed .

Adjusted script by me not works : from Tanaike

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  var values = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues(); // Retrieve values
  var Result_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

  var result = [];
  for (var i = 1; i < values.length; i++) {
    for (var j = 1; j < values[i].length; j++) {
      result.push([values[0][i], values[j][0], values[j][i]]);
    }
  }

  Result_sheet.getRange().setValues(result); // Put result
}

I am too new to java script that cannot tell the reason.

Answer 1

@J. G. had the right idea, but there are a couple bugs in that code (e.g. i and j transposed) which resulted in the error @rane commented on.

This should do what the o.p. asked for without the need for filtering as in the Sheet2 intermediate step.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // identify source sheet
  var values = sheet.getDataRange().getValues(); // Retrieve values
  var Result_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  
  var result = [];
  result.push(["Name","Type","Date","YTD","QTY"]);
  
  for (var i = 1; i < values.length; i++) {
    for (var j = 3; j < values[i].length; j++) {
      if (values[i][j].length < 1) continue; // skip columns where 'qty' is blank, remove this line if you want empty value rows
      if (values[i][0].length < 1) continue; // skip rows where 'Name' is blank
      result.push([ values[i][0], values[i][1], values[0][j], values[i][2], values[i][j] ]);
    }
  }

  Result_sheet.getRange(1,1,result.length, result[0].length).setValues(result); // Populate results
}

which results in...

Name        Type    Date    YTD     QTY
Product 1   A       JAN     8       1
Product 1   A       FEB     8       3
Product 1   A       MAR     8       4
Product 2   B       JAN     519     41
Product 2   B       FEB     519     23
Product 2   B       MAR     519     455
Product 3   C       JAN     32      2
Product 3   C       FEB     32      25
Product 3   C       MAR     32      5

Answer 2

Ok, so from what I understand you want to take the first table, remove blank products, and decompress it.

The reason tnaike's script didn't work for you is that you have two leader columns (product and type) and not just one. This appears to adjust everything correctly.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  var values = sheet.getDataRange().getValues(); // Retrieve values
  var Result_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

  var result = [];
  result.push(["Name","Type","Month","QTY"]);
  for (var i = 1; i < values.length; i++) {

    for (var j = 1; j < values[i].length; j++) {
      if (values[j][0].length < 1) continue;
      if (values[0][i].length < 1) continue;
      result.push([ values[j][0],values[j][1], values[0][i], values[j][i]]);
    }
  }

  Result_sheet.getRange(1,1,result.length, result[0].length).setValues(result); // Put result
  Result_sheet.sort(1);
}

this results in:

Name/Type/Month/QTY
P1  A   Ja  1
P1  A   Fe  3
P1  A   Ma  4
P2  B   Ja  41
P2  B   Fe  23
P2  B   Ma  455
P3  C   Ja  2
P3  C   Fe  25
P3  C   Ma  5