Transforming data for d3 tree

Creating d3.js tree diagrams directly from Excel, shows how to take a simple list of Excel data and create d3.js diagram that looks like this,

I had a question on the Excel Liberation forum the other day about how to automate the creation of the input data – an extract of which looks like this

Pretty simple, but tedious to create manually if your data is not already organized that way. Using cJobject and cDataSet along with a bit of recursion  makes restructuring of most datasets very simple. I’ll use a color theory visualization to demonstrate a pattern for converting data structure. The output we want is this

but the input data we want looks like this

Whereas what we need to be able to feed the d3.js conversion is something like this

Here’s a VBA pattern that will transform the data, and then call up the d3.js conversion.

Loading ….

How does this work?
The secret sauce here is that the .add method of cJobject , usually used for creating or decoding json, has the very handy attribute of adding a child node if it is unknown or updating the value if the key is already known. Along with its recursion friendly structure, this very concise piece of code is all that is needed to turn the first data structure into the second


Set croot = job.add(InputBox("root name"))
    For Each dr In ds.rows
        Set ck = croot
        For Each dc In dr.columns
            If Not IsEmpty(dc.value) Then
                Set ck = ck.add(dc.toString)
            End If
        Next dc
    Next dr

For more stuff like this, see the excel liberation site. You can download all code mentioned here in cDataSet.xslm

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.


  1. Bruce-

    I believe this posting is in reply to a comment of mine, and I appreciate that. I have just gotten back to this project, after earlier hitting a wall with my JS object approach (the recursive child search was not returning an object reference).

    Working off a cDataSet.xlsm sheet, adding a 'inputToTree' sheet with data, and a blank 'ouputtree' sheet, I add a macro titled 'transformD3Tree'. Running transformD3Tree, I originally got a parameter mismatch error when it calls makeD3Tree. I saw that it takes two parameters (parameters and data) so I eliminated the third parameter 'title'. Since then I've tried running it and keep getting a compile error 'sub or function not defined'. Since then I have not been able to duplicate the parameter error, even when I add the title back in, which is strange.

    I'm sure this a more general structuring error, due to my lack of VBA experience, but was hoping you could point me in the correct direction.

    Apologies for the anonymous posting, but my employer blocks gmail/google groups, which I used to post my original query from home.



  2. Bruce- Thanks for the reply. I was working off an old workbook of yours. Just noticed that you have an updated workbook with the new macros. Downloaded that and it works! Then I extracted the JSON, and put it in a separate file, as per Bostock's schema:

    My next challenge is adding more atributes aside from children, as per this schema: On the excel doc I would add these as separate columns after the last child node column.

    If you could point me in the general direction of what variables and routines I should be looking at, I'll be glad to work on that. I'm not very good at VBA yet..

    Working from home today…

    Thanks again,


Comments are closed.