How to automatically format data to create a treemap in Google Sheets?

Question

I want to create a treemap from dynamic data in google spreadsheets. So far, I succeeded to have a table in a format that Excel can use, but I don't know how to transform this table in a table that Google sheet can use to create this treemap

Excel can use this data. Not Google sheet.

My data looks like this:

Categories  Item    Value
__________  ______  _____
category_1  item_1  5
category_1  item_2  20
category_1  item_3  1
category_2  item_4  0
category_2  item_5  5
category_2  item_6  18
category_3  item_7  16
category_4  item_8  7
category_4  item_9  16

I would like to find a way to transform this data into something like the table below, which is usable by Google sheet.

Item        Parent      Value
__________  __________  _____
Categories              88
category_1  Categories  26
item_1      category_1  5
item_2      category_1  20
item_3      category_1  1
category_2  Categories  23
item_4      category_2  0
item_5      category_2  5
item_6      category_2  18
category_3  Categories  16
item_7      category_3  16
category_4  Categories  23
item_8      category_4  7
item_9      category_4  16

I did not find a way to do that yet and was wondering if anyone had faced the same problem.

Answer 1

Probably you can use this simple script function as an example:

function makeTree() {
  var srcRange = SpreadsheetApp.getActiveSheet().getRange('A2:C10'),
      tree = {'.Categories': 0}, key;

  // Fill tree object with source data
  srcRange.getValues().forEach(function(rowValues) {
    // Add row value to the root
    tree['.Categories'] += rowValues[2];
    // Add it to "Category" level
    key = 'Categories.' + rowValues[0];
    if (tree[key] == undefined) {
      tree[key] = rowValues[2];
    } else {
      tree[key] += rowValues[2];
    }
    // Add it to "Item" level too
    key = rowValues[0] + '.' + rowValues[1];
    if (tree[key] == undefined) {
      tree[key] = rowValues[2];
    } else {
      tree[key] += rowValues[2];
    }
  });

  // Format tree rows for output
  var values = [];
  for (key in tree) {
    var subKeys = key.split('.');
    values.push([subKeys[1], subKeys[0], tree[key]]);
  }

  // Fill target data rows 
  var targetRange = srcRange.offset(0, 4, values.length);
  targetRange.setValues(values);
}

Here we collect all data in a single JS object, using composite string keys with a dot delimiter. Ready object is converted to the 2D-array before a target range filling. As a result we have both ranges on the same sheet: