Tree Map Chart with Google Spreadsheets

Abstract

This article explains how to build a tree map (or treemapping) with Google Spreadsheets.

Objective

The objective is to visualize files and folders of a given file system and to build a tree map graph from these data. A lot of free tools can be found on the internet doing similar things, e.g. spacemonger, WinDirStat and many more. In our case, data of a mapped SharePoint online folder should be visualized. Some of the tools do not accept mapped SharePoint locations, therefore and also to try out Google's tree map this tool was chosen. The data (files) were "grabbed" with the help of a little VBA (can also be done with VBS) script running in Microsoft Excel.

Structure

The structure of a tree has typically 3 columns:

Id Caption Parent (Id)
0 root null
1 node 1 0
2 node 2 0
3 node 3 0
4 node 1.1 1
5 node 1.1.1 4
6 node 1.2 1
7 node 2.1 2

Google Charts / Google Sheets

A similar structure is needed when it comes to build a tree map with Google charts. In a Google spreadsheet, we have to prepare a table that looks like the following:

Item Parent Value
root 22
item 1 root 10
item 2 root 10
item 3 item 2 8
item 4 item 2 2
item 5 root 2

Visual representation:

Steps to build the chart

Please note: The first column has to be of data type string, numeric ids as shown in the generic tree structure will not work.






Get files and folder data

Excel Settings

It is recommended to use , (comma) as thousands- and . (dot) as decimal separator. If your system settings should be differnt, you can modify the settings within Microsoft Excel (cf. next screenshot or via Microsoft Windows Control Panel (Region)):

Source Code

Option Explicit On

Private i As Long

 

' source: https://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory/14246818#14246818

' modification by Dieter Neumann (dn@consulity.net)

Sub Demo()

 

    'On Error Resume Next

    i = 1

    ActiveSheet.Range("A2:Z20000").ClearContents()

    Dim fso As Object 'FileSystemObject

    Dim fldStart As Object 'Folder

    Dim fld As Object 'Folder

    Dim fl As Object 'File

    Dim Mask As String

 

    fso = CreateObject("scripting.FileSystemObject") ' late binding

    'Set fso = New FileSystemObject 'or use early binding (also replace Object types)

 

    fldStart = fso.GetFolder("X:\LocalTemp\_temp") ' <-- use your FileDialog code here

 

    Mask = "*.*"

 

    Range("A1").Offset(i) = fldStart.Path

    Range("A1").Offset(i, 1) = ""

    Range("A1").Offset(i, 2) = fldStart.Size / 1000

 

    i = i + 1

    ListFiles(fldStart, Mask)

    For Each fld In fldStart.SubFolders

        Range("A1").Offset(i) = fld

        Range("A1").Offset(i, 1) = fldStart

        Range("A1").Offset(i, 2) = fld.Size / 1000

        i = i + 1

        ListFiles(fld, Mask)

        ListFolders(fld, Mask)

    Next

End Sub

 

 

Sub ListFolders(fldStart As Object, Mask As String)

    Dim fld As Object 'Folder

    For Each fld In fldStart.SubFolders

        Range("A1").Offset(i) = fld.Path

        Range("A1").Offset(i, 1) = fldStart

        Range("A1").Offset(i, 2) = fld.Size / 1000

        i = i + 1

        ListFiles(fld, Mask)

        ListFolders(fld, Mask)

    Next

 

End Sub

 

Sub ListFiles(fld As Object, Mask As String)

    Dim fl As Object 'File

    For Each fl In fld.Files

        If fl.Name Like Mask Then

            Range("A1").Offset(i) = fl.Path & "\" & fl.Name

            Range("A1").Offset(i, 1) = fld.Path

            Range("A1").Offset(i, 2) = fl.Size / 1000

            i = i + 1

        End If

    Next

End Sub

 

Result

Here is the result for a sample folder.:

Please note: Tree maps are interactive charts. You can "drill down" with a single click and you can navigate back with the right mouse button (go one level higher) on a blue header.

Chart in own sheet

Please note: The redundant part of the path has been removed so that the root folder starts with "\" (backslash)


Dieter Neumann