3

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

From this format :

          | JAN        | FEB        | MAR 
----------+------------+------------+-----------
Product 1 |          1 |          2 |          3
Product 2 |          4 |          5 |          6
Product 3 |          7 |          8 |          9

Convert it to this format:

Date|  Product  | Qty
-----------+-----------+----
JAN | Product 1 |   1
FEB | Product 2 |   4
MAR | Product 3 |   9
JAN | Product 2 |   4
FEB | Product 1 |   2
JAN | Product 3 |   7
MAR | Product 2 |   6
FEB | Product 3 |   8
MAR | Product 1 |   3

Do we have any solution to achieve this ?

Rubén
  • 34,714
  • 9
  • 70
  • 166
rane
  • 901
  • 4
  • 12
  • 24
  • 2
    Can you explain the logic for retrieving the output values from the input values? I cannot understand that why ``4`` is used 2 times and the order of output rows. And is the logic of your question different from [this thread](https://webapps.stackexchange.com/q/126088/176185)? If I misunderstood your question, I apologize. – Tanaike Apr 25 '19 at 06:36
  • Sorry tanaike , its just a typo .FEB Product 2 should be 5 . Just want to reshape the data format like R `dcast()` ot `melt()` functions – rane Apr 25 '19 at 09:31
  • 1
    Thank you for replying. I proposed a sample script as an answer. Could you please confirm it? If I misunderstood your question and the result was not what you want, I apologize. By the way, if you can do, can you update your question using the correct output you want? – Tanaike Apr 25 '19 at 12:29

1 Answers1

3

How about this sample script?

Sample script:

When you use this script, at first, please put the input values to the cells "A1:D4", and run myFunction().

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getRange("A1:D4").getValues(); // Retrieve values

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

  sheet.getRange("A6:C14").setValues(result); // Put result
}
  • In this sample script, it supposes the following situation.
    • Input values are put to the cells "A1:D4" of the active sheet.
    • Output values are put to the cells "A6:C14" of the active sheet.

Result:

enter image description here

Other pattern:

When result.push([values[0][i], values[j][0], values[j][i]]); is modified to temp.push([values[0][j], values[i][0], values[i][j]]);, the following result is obtained.

enter image description here

Note:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Tanaike thankyou so much , if i want to get all value in sheet , why script like `sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues()` get error ? (paste in specific sheet will be modified) – rane Apr 26 '19 at 03:50
  • 1
    @rane Thank you for replying. I cannot understand about your new situation. So I couldn't understand about the error. I apologize for my poor skill. So can you provide the detail information for replicating your issue? If your want to change the input values from your question, can you post it as new question? If the input and output values are the same with your this question, please provide the detail information (the error, input and output values, flow and script) for replicating your issue. I would like to confirm about your issue. – Tanaike Apr 26 '19 at 04:47
  • Thank you Tanaike ! posted as a new question :https://stackoverflow.com/questions/55863079/how-to-getrange-in-all-cell-appear-when-reshape-data-for-wide-to-long-form-by-mo Name :How to getRange in all cell appear when reshape data for wide to long form by monthly columns name GooglespreadSheet – rane Apr 26 '19 at 07:49
  • 1
    @rane Thank you for replying and your response. I saw it just now. I noticed that an answer has already been posted. I think that it will resolve your issue. – Tanaike Apr 26 '19 at 23:32
  • Tanaike , you are awesome – rane Apr 27 '19 at 04:36